0

I've been trying without success to obtain all products with status = enabled from MAGENTO database and the price.

My query shows ALL the items (enabled and disabled ones) and i want to get only those with status = enabled.

This is my query:

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;

How should i modify my query in order to obtain what i want (all the items with status = enabled AND the price?)

I tried to use this condition but it did not work:

WHERE catalog_product_entity_int.value = 1
Improve this question

1 Answer 1

Reset to default
2

You should LEFT JOIN the status attribute. Add this code:

LEFT JOIN catalog_product_entity_int code_status 
          ON main.entity_id = code_status.entity_id 
             AND code_status.attribute_id = (SELECT attribute_id FROM eav_attribute where entity_type_id = 4 AND attribute_code  = 'status')

And this code for the price:

LEFT JOIN catalog_product_entity_decimal code_price 
              ON main.entity_id = code_price.entity_id 
                 AND code_price.attribute_id = (SELECT attribute_id FROM eav_attribute where entity_type_id = 4 AND attribute_code  = 'price')

So the whole code will be:

SELECT main.sku, 
       option_value_brand.value AS code_brand, 
       code_business_unit.value AS code_business_unit,
       code_status.value AS code_status,
       code_price.value AS code_price
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
        LEFT JOIN catalog_product_entity_int code_status 
              ON main.entity_id = code_status.entity_id 
                 AND code_status.attribute_id = (SELECT attribute_id FROM eav_attribute where entity_type_id = 4 AND attribute_code  = 'status')
        LEFT JOIN catalog_product_entity_decimal code_price 
              ON main.entity_id = code_price.entity_id 
                 AND code_price.attribute_id = (SELECT attribute_id FROM eav_attribute where entity_type_id = 4 AND attribute_code  = 'price')
WHERE code_status.value = 1;
Improve this answer
8
  • Thank you for replying! Could you please explain me why did you use a subquery?
    – programming_amazing
    Commented Mar 27, 2019 at 0:31
  • 1
    I did it mainly to avoid the code to be complicated to read. Now it is easier to read, maintain and add additional attributes to select. Maybe someone with better knowledge of MySQL can provide a better or faster alternative
    – karpa
    Commented Mar 27, 2019 at 0:37
  • 1
    @programming_amazing the status of a product is an eav attribute like other attributes you have in your query. Therefore you have to join the table of that attribute on the corresponding attribute id. That can be done either like you have done it with other attributes (join eav_attribute and join eav_attribute_int) or joining only eav_attribute_int and getting the attribute_id with the subquery. The result is the same and the above query should give you the results you need.
    – HelgeB
    Commented Mar 27, 2019 at 5:59
  • @HelgeB If i use this: option_value_code_status AS code_status it returns NULL but if i use this one: code_status.value AS code_status it returns 1. Why? Whats the difference?
    – programming_amazing
    Commented Mar 27, 2019 at 13:29
  • 1
    status is a simple eav attribute of type int, there is no option or option value table to be joined there. Therefore your last two joins of eav_attribute_option and eav_attribute_option_value can not work. Remove them and you will have the correct statement whitout subquery. You will still have to check that the value from the int table is 1.
    – HelgeB
    Commented Mar 27, 2019 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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