0

Yesterday i tried to obtain all products from MAGENTO database. I did a query and i thought it was working BUT i was wrong.

I put a random SKU in the MAGENTO application, then i clicked on "search" and the product appeared but that SKU did not apprear in my query:

image

This is my query:

SELECT main.sku, code_brand.value AS code_brand,option_value_brand.value

FROM catalog_product_entity AS main 
LEFT JOIN catalog_product_entity_int AS code_brand ON main.entity_id = code_brand.entity_id
JOIN eav_attribute_option option_brand on option_brand.attribute_id = code_brand.attribute_id and option_brand.option_id = code_brand.value
JOIN eav_attribute_option_value option_value_brand on option_brand.option_id = option_value_brand.option_id

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

Whats going on here?

EDIT: What about store_id = 0 and store_id = 1?

Can please help me to obtain ALL the products?

What should i modify?

This is the MAGENTO database diagram:

image2

1

I'm still not sure what your requirements are, but I try to explain you what might be your problem: If you want't to get all the products - no matter if the attribute code_brand is set or not, you have to use join left consequently in your sql statement. Now I assume that either the SKU you have used to filter the result of your SQL client is from a product with no attribute selected for code_brand or your SQL client is still limiting the result set.

So here is the SQL statement to get all products and join the attribute value, if a value is set (otherwise you will get NULL in that column):

SELECT main.sku, code_brand.value AS code_brand,option_value_brand.value
FROM catalog_product_entity AS main 
LEFT JOIN eav_attribute attribute_brand on attribute_brand.attribute_code = 'code_brand' and attribute_brand.entity_type_id = 4
LEFT JOIN catalog_product_entity_int code_brand ON main.entity_id = code_brand.entity_id and code_brand.attribute_id = attribute_brand.attribute_id
LEFT JOIN eav_attribute_option option_brand on option_brand.attribute_id = code_brand.attribute_id and option_brand.option_id = code_brand.value
LEFT JOIN eav_attribute_option_value option_value_brand on option_brand.option_id = option_value_brand.option_id;

You can add where main.sku = '[what-ever-sku-you-want-to-filter]' to get the specific row for that sku.

I hope that helps.

Edit Assuming you want to join the varchar attribute code_business_unit to the above statement use this SQL (I've removed the columns for the option ids from the statement because you don't need them)

SELECT main.sku, option_value_brand.value AS code_brand, code_business_unit.value as code_business_unit
FROM catalog_product_entity AS main 
LEFT JOIN eav_attribute attribute_brand on attribute_brand.attribute_code = 'code_brand' and attribute_brand.entity_type_id = 4
LEFT JOIN eav_attribute attribute_business_unit on attribute_business_unit.attribute_code = 'code_business_unit' and attribute_brand.entity_type_id = 4
LEFT JOIN catalog_product_entity_int code_brand ON main.entity_id = code_brand.entity_id and code_brand.attribute_id = attribute_brand.attribute_id
LEFT JOIN eav_attribute_option option_brand on option_brand.attribute_id = code_brand.attribute_id and option_brand.option_id = code_brand.value
LEFT JOIN eav_attribute_option_value option_value_brand on option_brand.option_id = option_value_brand.option_id
LEFT JOIN catalog_product_entity_varchar code_business_unit ON main.entity_id = code_business_unit.entity_id and code_business_unit.attribute_id = attribute_business_unit.attribute_id;
  • Thank you for replying!. Why did you use attribute_brand.entity_type_id = 4 ? Should i use the where condition from my code? – programming_amazing Feb 4 at 11:50
  • the entity_type_id of catalog_product is 4 in magento. Since this value never changes it's simpler to query that way than using SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product' – HelgeB Feb 4 at 12:01
  • Just a question: How would you change your code in order to get catalog_product_entity_varchar AS code_business_unit? What about the "where" condition from my code? Should i use it in your code? – programming_amazing Feb 4 at 12:03
  • You don't need the where condition, I have transformed that into left joins in order to get all products including those without attribute code_brand set. For a varchar attribute you need only the value from the catalog_product_entity_varchar table. That means you can remove the last two joins from the statement in the answer and change the eav table corresponding from _int to _varchar, and of corse the attribute code :-) – HelgeB Feb 4 at 12:13
  • But if i delete the last two columns i will not be able to see the option value, am i right? – programming_amazing Feb 4 at 12:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.