Querying Magento’s Customer EAV and Newsletter Subscriber Data

Magento’s EAV data structure makes storing and extending customer information infinitely flexible. This is great if you’re writing reports for Magento’s admin panel, much of the complexity is abstracted by the fantastic MVC system, and the heavy lifting is performed by the ORM.

Sometimes however, you just need to run a quick SQL query on the database. For example, retrieve all UK customers who have subscribed to the newsletter. This is where Magento can become a little tricky to work with.

The following query retrieves customers who are subscribed to a Magento store newsletter and have GB as their default shipping address country. It also formats the firstname and lastname fields as these’s aren’t cleaned up when they’re saved. Of course it can be optimised, but it’s fine for the odd ad-hoc report.

SELECT c.entity_id, c.email, CONCAT(UCASE(LEFT(cev.value, 1)), 
LCASE(SUBSTRING(cev.value, 2))) AS firstname, 
CONCAT(UCASE(LEFT(cev2.value, 1)), 
LCASE(SUBSTRING(cev2.value, 2))) AS lastname,
caev.value as shipping_country
FROM customer_entity AS c 
JOIN customer_entity_varchar AS cev ON(cev.entity_id = c.entity_id) 
JOIN customer_entity_varchar AS cev2 ON(cev2.entity_id = c.entity_id)
JOIN sales_flat_order AS o ON(o.customer_id = c.entity_id) 
JOIN customer_entity_int AS cev3 ON (cev3.entity_id = c.entity_id)
JOIN customer_address_entity_varchar AS caev ON (cev3.value = caev.entity_id)
JOIN newsletter_subscriber AS ns ON(ns.customer_id = c.entity_id)
WHERE cev.attribute_id IN 
(
	SELECT ea.attribute_id 
	FROM eav_attribute AS ea 
	WHERE ea.attribute_code = "firstname" 
	AND entity_type_id = 1
) 
AND cev2.attribute_id IN 
(
	SELECT ea.attribute_id 
	FROM eav_attribute AS ea 
	WHERE ea.attribute_code = "lastname" 
	AND entity_type_id = 1
)
AND cev3.attribute_id IN
(
	SELECT ea.attribute_id 
	FROM eav_attribute AS ea 
	WHERE ea.attribute_code = "default_shipping" 
	AND entity_type_id = 1
)
AND caev.attribute_id IN
(
	SELECT ea.attribute_id 
	FROM eav_attribute AS ea 
	WHERE ea.attribute_code = "country_id" 
	AND entity_type_id = 2
)
AND ns.subscriber_status = 1
AND caev.value = 'GB'
GROUP BY c.entity_id
Advertisements