doushuangdui5419 2010-11-11 12:38
浏览 130
已采纳

从magento表结构获取客户信息

I want to get rows for all customer details from magento tables.

Can anyone give me the query?

Tables are:

  • customer_entity
  • eav_attribute
  • customer_entity_varchar
  • 写回答

11条回答 默认 最新

  • doulan8846 2010-11-11 13:27
    关注

    It's possible to extract some data from Magento MySQL DB, but before executing a queries below notice following:

    1) Magento is built to be configurable, so that you must not fetch data from MySQL, but you must use Magento Customer module (models, resource models) and Magento configuration data to retrieve customer data. Any straight query is not guaranteed to be compatible with different Magento versions and even installations of same version.

    2) Magento EAV structure does not allow you to fetch all customer data in a nice form within one query, because table names are matched to attributes dynamically.

    3) You cannot extract ALL customer data just by one query or even multiple queries. Because many informational objects are created within models and only model has the logic to gather all data within one customer object. I talk about shipping/billing addresses, store credit, reward points (for EE version) and so on.

    However just to fetch all customers' varchar attributes you can use following code (not guaranteed to work due to mentioned above in 1):

    SELECT ce.*, ea.attribute_code, cev.value
      FROM customer_entity AS ce 
      LEFT JOIN eav_attribute AS ea ON ce.entity_type_id = ea.entity_type_id AND ea.backend_type = 'varchar'
      LEFT JOIN customer_entity_varchar AS cev ON ce.entity_id = cev.entity_id AND ea.attribute_id = cev.attribute_id
    

    Also it's possible to use such a query to extract all attributes for a customer

    SELECT ce.*, ea.attribute_code, 
        CASE ea.backend_type 
           WHEN 'varchar' THEN ce_varchar.value
           WHEN 'int' THEN ce_int.value
           WHEN 'text' THEN ce_text.value
           WHEN 'decimal' THEN ce_decimal.value
           WHEN 'datetime' THEN ce_datetime.value
           ELSE NULL
        END AS value
      FROM customer_entity AS ce 
      LEFT JOIN eav_attribute AS ea ON ce.entity_type_id = ea.entity_type_id
      LEFT JOIN customer_entity_varchar AS ce_varchar ON ce.entity_id = ce_varchar.entity_id AND ea.attribute_id = ce_varchar.attribute_id AND ea.backend_type = 'varchar'
      LEFT JOIN customer_entity_int AS ce_int ON ce.entity_id = ce_int.entity_id AND ea.attribute_id = ce_int.attribute_id AND ea.backend_type = 'int'
      LEFT JOIN customer_entity_text AS ce_text ON ce.entity_id = ce_text.entity_id AND ea.attribute_id = ce_text.attribute_id AND ea.backend_type = 'text'
      LEFT JOIN customer_entity_decimal AS ce_decimal ON ce.entity_id = ce_decimal.entity_id AND ea.attribute_id = ce_decimal.attribute_id AND ea.backend_type = 'decimal'
      LEFT JOIN customer_entity_datetime AS ce_datetime ON ce.entity_id = ce_datetime.entity_id AND ea.attribute_id = ce_datetime.attribute_id AND ea.backend_type = 'datetime'
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(10条)

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题