Find products (eav_attribute table) Magento

Question

I know that MAGENTO has an entity called catalog_product

When a new attribute is created there will be an entry in eav_attribute and if you check this entry there will be a column as entity_type_id

eav_attribute table also has a column known as attribute_code which is nothing but a value of Attribute Code entered while making a new attribute from admin.

I dont know which tables i need to "join" in order to obtain what i want. Query?

I am using MAGENTO 1.9 and MYSQL

Database diagram:

Thanks!

Answer 1

Do you really want / need to build an SQL query? That can get complicated really fast with EAV.

To filter all products by specific attributes in Magento you can use the collections.

For example to get all Products with a name containing 'XYZ':

/** @var Mage_Catalog_Model_Resource_Product_Collection $collection */
$collection = Mage::getResourceModel('catalog/product_collection');
$collection->addAttributeToFilter('name', array('like' => '%XYZ%'));
$products = $collection->getItems();

http://www.beckin.com/the-official-cheat-sheet-for-magento-developers/

You can also print the Select statement of a prepared collection like this:

echo $collection->getSelect()->__toString();

The same (mostly) in sql would look like:

SELECT main.entity_id,main.sku,name.value AS name
FROM catalog_product_entity AS main 
LEFT JOIN catalog_product_entity_varchar AS name
ON main.entity_id = name.entity_id
WHERE name.attribute_id = (
    SELECT attribute_id 
    FROM eav_attribute 
    WHERE attribute_code LIKE "name"
    AND `entity_type_id` = (
        SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'
    )
)
AND name.value LIKE "%XYZ%";

EDIT: Not sure what you are exactly trying but you really should ask the other side for a proper API Endpoint (for example a rest api) instead of going directly on the database.

I tried to add some Comments for further explaining the query. Although it's mostly SQL knowledge and getting out of scope (=magento) here.

-- first the columns that should be included in the result
-- prefixed with the table alias where they come from 
-- (main = catalog_product_entity and 
-- name = catalog_product_entity_varchar restricted to the name attribute)
-- the as name in the end just renames the name.value column to name in the result
SELECT main.entity_id,main.sku,name.value AS name
-- the main product table to select the products from
FROM catalog_product_entity AS main 
-- using a left join to include all entries from the main table and exclude
-- possible values in value table without a product relation 
-- (although that should never happen anyway)
LEFT JOIN catalog_product_entity_varchar AS name
ON main.entity_id = name.entity_id
-- restricting the rows from catalog_product_entity_varchar to the name attribute
WHERE name.attribute_id = (
    -- this complete subqery just gets the attribute id for the name attribute
    SELECT attribute_id 
    FROM eav_attribute 
    WHERE attribute_code LIKE "name"
    AND `entity_type_id` = (
        -- this subquery only gets the entity_type_id for catalog_product
        SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'
    )
)
-- finally filter the result to only show products where the name contains 'XYZ'
AND name.value LIKE "%XYZ%"

EDIT2: adding two more attributes, one of type text to make it more clear

SELECT main.entity_id,main.sku,name.value AS name, description.value AS description, image.value AS image
FROM catalog_product_entity AS main 

LEFT JOIN catalog_product_entity_varchar AS name
ON main.entity_id = name.entity_id

-- description is a text attribute so you need to join catalog_product_entity_text
-- see eav_attribute table fot the attribute type
LEFT JOIN catalog_product_entity_text AS description
ON main.entity_id = description.entity_id

-- adding the image which is of type varchar (like name)
LEFT JOIN catalog_product_entity_varchar AS image
ON main.entity_id = description.entity_id

WHERE name.attribute_id = (
    SELECT attribute_id 
    FROM eav_attribute 
    WHERE attribute_code LIKE "name"
    AND `entity_type_id` = (
        SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'
    )
)

-- filter catalog_product_entity_text values for description by the attribute id of description
AND description.attribute_id = (
    SELECT attribute_id 
    FROM eav_attribute 
    WHERE attribute_code LIKE "description"
    AND `entity_type_id` = (
        SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'
    )
)

-- filter catalog_product_entity_varchar values for image by the attribute id of image
AND image.attribute_id = (
    SELECT attribute_id 
    FROM eav_attribute 
    WHERE attribute_code LIKE "image"
    AND `entity_type_id` = (
        SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'
    )
)

AND name.value LIKE "%hose%";