mysql - Magento 报告客户愿望 list

Question

我正在尝试对客户愿望 list 中的内容进行报告，我已完成此查询并需要更多帮助:

   SELECT b.email, c.value AS name, a.updated_at, d.added_at, d.product_id, e.name, SUM(g.qty_ordered) AS purchased
FROM `wishlist` AS a
INNER JOIN customer_entity AS b ON a.customer_id = b.entity_id
INNER JOIN customer_entity_varchar AS c ON a.customer_id = c.entity_id AND c.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'firstname' AND entity_type_id = b.entity_type_id)
INNER JOIN wishlist_item AS d ON a.wishlist_id = d.wishlist_id
INNER JOIN catalog_product_flat_1 AS e ON d.product_id = e.entity_id
LEFT JOIN sales_flat_order AS f ON f.customer_email = b.email
LEFT JOIN sales_flat_order_item AS g ON (f.entity_id = g.order_id AND g.sku LIKE CONCAT(e.sku,'%') AND g.product_type = 'simple')
GROUP BY b.email, c.value, a.updated_at, d.added_at, d.product_id, e.name

我试图从某些事情开始，所以我在 phpmyadmin 中执行此查询，但没有得到任何结果。有人可以至少向我指出如何在模板文件中获取此集合的一开始，或者至少看到第一个结果并开始了解它是如何工作的
我确实明白我应该制作一个管理模块并在 Mage_Adminhtml_Block_Widget_Grid

中过滤报告

编辑:这个查询会起作用，问题是catalog_product_flat_1在我现在工作的Magento中是空的，我已经在管理中启用了产品平面表，现在可以了。
与此模块一起可以轻松完成这项工作 https://github.com/kalenjordan/custom-reports为了其他人的兴趣
现在我有另一个问题，实际上这就是我需要做的:
如何使用精确查询过滤到指定产品？例如检查产品 SKU(或产品 ID 或名称等)并过滤愿望 list 中仅包含此产品的客户的结果。
现在它将显示每个独特客户在愿望 list 中拥有的所有产品(巨大的查询)

Answer 1

找到答案了:)最终查询包括定位特定产品(无扁平产品数据):

SELECT b.email, c.value AS firstname, a.updated_at, d.added_at, d.product_id, e.sku, SUM( g.qty_ordered ) AS purchased
FROM  `wishlist` AS a
INNER JOIN customer_entity AS b ON a.customer_id = b.entity_id
INNER JOIN customer_entity_varchar AS c ON a.customer_id = c.entity_id
AND c.attribute_id = ( 
SELECT attribute_id
FROM eav_attribute
WHERE attribute_code =  'firstname'
AND entity_type_id = b.entity_type_id ) 
INNER JOIN wishlist_item AS d ON a.wishlist_id = d.wishlist_id
INNER JOIN catalog_product_entity AS e ON d.product_id = e.entity_id
LEFT JOIN sales_flat_order AS f ON f.customer_email = b.email
LEFT JOIN sales_flat_order_item AS g ON ( f.entity_id = g.order_id
AND g.sku LIKE CONCAT( e.sku,  '%' ) 
AND g.product_type =  'simple' ) 
WHERE e.sku =  'api_75019'
GROUP BY b.email, c.value, a.updated_at, d.added_at, d.product_id, e.sku

这适用于启用平面数据:

SELECT b.email, c.value AS firstname, a.updated_at, d.added_at, d.product_id, e.name, SUM( g.qty_ordered ) AS purchased
FROM  `wishlist` AS a
INNER JOIN customer_entity AS b ON a.customer_id = b.entity_id
INNER JOIN customer_entity_varchar AS c ON a.customer_id = c.entity_id
AND c.attribute_id = ( 
SELECT attribute_id
FROM eav_attribute
WHERE attribute_code =  'firstname'
AND entity_type_id = b.entity_type_id ) 
INNER JOIN wishlist_item AS d ON a.wishlist_id = d.wishlist_id
INNER JOIN catalog_product_flat_1 AS e ON d.product_id = e.entity_id
LEFT JOIN sales_flat_order AS f ON f.customer_email = b.email
LEFT JOIN sales_flat_order_item AS g ON ( f.entity_id = g.order_id
AND g.sku LIKE CONCAT( e.sku,  '%' ) 
AND g.product_type =  'simple' ) 
WHERE e.sku =  'api_75019'
GROUP BY b.email, c.value, a.updated_at, d.added_at, d.product_id, e.name