0

I dont know why i am not getting what i want after executing my query AND i was not able to find an attribute.

Let me explain everything:

First off, i wanted to get all the products that had these attributes:

SKU, code_brand, code_business_unit, code_line, code_group, code_business and min_sale_qty

This is my query:

SELECT main.entity_id,main.sku,code_brand.value AS code_brand, code_business_unit.value AS code_business_unit, code_line.value AS code_line, code_group.value AS code_group, code_business.value AS code_business

FROM catalog_product_entity AS main 

LEFT JOIN catalog_product_entity_int AS code_brand
ON main.entity_id = code_brand.entity_id
LEFT JOIN catalog_product_entity_varchar AS code_business_unit
ON main.entity_id = code_business_unit.entity_id
LEFT JOIN catalog_product_entity_varchar AS code_line
ON main.entity_id = code_line.entity_id
LEFT JOIN catalog_product_entity_int AS code_group
ON main.entity_id = code_group.entity_id
LEFT JOIN catalog_product_entity_int AS code_business
ON main.entity_id = code_business.entity_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'
    )
)
AND code_business_unit.attribute_id = (
    SELECT attribute_id 
    FROM eav_attribute 
    WHERE attribute_code LIKE 'code_business_unit'
    AND `entity_type_id` = (
        SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'
    )
)
AND code_line.attribute_id = (
    SELECT attribute_id 
    FROM eav_attribute 
    WHERE attribute_code LIKE 'code_line'
    AND `entity_type_id` = (
        SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'
    )
)
AND code_group.attribute_id = (
    SELECT attribute_id 
    FROM eav_attribute 
    WHERE attribute_code LIKE 'code_group'
    AND `entity_type_id` = (
        SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'
    )
)
AND code_business.attribute_id = (
    SELECT attribute_id 
    FROM eav_attribute 
    WHERE attribute_code LIKE 'code_business'
    AND `entity_type_id` = (
        SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'
    )
)

It works but i am not getting the correct results, take a look at this picture:

image

Why, what is going on?

Also, i was not able to find this attribute: min_sale_qty

After executing this query i got no results:

select 
backend_type from eav_attribute
where attribute_code like "min_sale_qty"

I found this attribute in this table:

image_s

This is the database diagram:

image3

  • For the values of your attributes you have to join eav_attribute_option_value, your sql statement returns the option_id. Regarding the min_sale_qty: that is not a product attribute but one of the stock item. You have found the table you need to join. – HelgeB Feb 2 at 14:59
  • @HelgeB Thank you for replying! How would you change it? I dont understand that. Also, where can i get that table? – programming_amazing Feb 2 at 15:15
1

Here is the statement, I have mentioned in my comment (for simplicity I've used only one of your attributes, for the others please copy and adapt the added joins of eav_attribute_option and eav_attribute_option_value ). The tables are in the standard magento 1.9 schema and you find them also in your picture.

SELECT main.entity_id,main.sku,code_brand.value AS code_brand,option_value_brand.value,stock.min_sale_qty

FROM catalog_product_entity AS main 
join cataloginventory_stock_item stock on main.entity_id = stock.product_id
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'
    )
);
  • Thank you for replying!. Can you please explain me why did you use just ONE left condition? I mean, can you please "comment" your lines (below the select statement and above the where condition) and explain me why did you do that in that way? – programming_amazing Feb 2 at 16:30
  • If you check my code, i used different left-join conditions – programming_amazing Feb 2 at 16:33
  • The left join is copied from your code as well as the where condition, therefore I don't know if you need left join or not :-). For the varchar attributes you don't need to join additional tables. The eav_attribute_option and eav_attribute_option_value are needed only for select attibutes (frontend_input = select in eav_attribute and source_model = eav/entity_attribute_source_table) – HelgeB Feb 2 at 16:41
  • Take a look: in my case i used FROM - LEFT JOIN - LEFT JOIN- LEFT JOIN - LEFT JOIN - LEFT JOIN but you used FROM - JOIN -LEFT JOIN - JOIN - JOIN Whats the difference? – programming_amazing Feb 2 at 16:45
  • 1
    You can change all your left joins to joins because in the where clause you use an implicit inner join. Therefore you will get only products with values set for all attributes. I hope that helps you and you can adapt the statement by yourself. Otherwise please let me know, I can help you on monday – HelgeB Feb 2 at 17:05

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.