Search code examples
mysqlmagentoentity-attribute-value

mySQL refer to alias in WHERE clause (EAV Model)

I have the following mySQL query that basically searches for all products that contain 'rawganic' in the name.

It looks messy because Magento uses the Entity–attribute–value model which is pretty confusing, so I have lots of select queries to pull the data in (I'm sure there is probably a better way).

SELECT entity_id As eID, value as PName, 
   (SELECT value from catalog_product_entity_varchar WHERE attribute_id = 152 and entity_id = eID) as Size, 
   (SELECT sku from catalog_product_entity WHERE entity_id = eID) as SKU,
   (SELECT type_id from catalog_product_entity WHERE entity_id = eID) as Type,
   (SELECT value from catalog_product_entity_decimal WHERE attribute_id = 75 and entity_id = eID) as RRP,
   (SELECT value from catalog_product_entity_int WHERE attribute_id = 96 and entity_id = eID) as Status 
FROM catalog_product_entity_varchar 
WHERE (attribute_id = 71 and value like '%rawganic%')

The result is:

Result

This is great, except now I want to add another WHERE clause, where the Type must be 'simple' and the status is '1'.

The SELECT queries reference an alias 'eID' however I cannot use this in a WHERE clause. How can I add these extra WHERE clauses?

Solution

  • You can use HAVING:

    SELECT entity_id As eID, value as PName, 
   (SELECT value from catalog_product_entity_varchar WHERE attribute_id = 152 and entity_id = eID) as Size, 
   (SELECT sku from catalog_product_entity WHERE entity_id = eID) as SKU,
   (SELECT type_id from catalog_product_entity WHERE entity_id = eID) as Type,
   (SELECT value from catalog_product_entity_decimal WHERE attribute_id = 75 and entity_id = eID) as RRP,
   (SELECT value from catalog_product_entity_int WHERE attribute_id = 96 and entity_id = eID) as Status 
FROM catalog_product_entity_varchar 
WHERE (attribute_id = 71 and value like '%rawganic%')
HAVING Type = 'Simple' AND Status = 1