After executing my query i got several products (SKUs) but it is not displaying all the products that are stored in the database.

Take a look at this picture:


How do i know that? Well, I put a random SKU in the MAGENTO application, then i clicked on "search" and the product appeared.

I tried to find that SKU in MYSQL but it was not there:

Take a look:


This is my query:

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

FROM catalog_product_entity AS main 

LEFT JOIN catalog_product_entity_int AS code_brand
ON main.entity_id = code_brand.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'

What is happening here? Am i missing anything?

This is the MAGENTO database diagram:



How can i get all products with status = disabled or enabled?

I've been searching and i found this:

SELECT entity_id FROM `catalog_product_entity_int`
WHERE attribute_id = (
    SELECT attribute_id FROM `eav_attribute`
    WHERE `attribute_code` LIKE 'status'
) AND `catalog_product_entity_int`.value = 2

How can i modify my code in order to obtain that?

  • 1
    It looks like you have a limitation to 1000 rows in your SQL client set. Check the settings. Try select count(*) from ... insted of listing the fields that should give you the number of products your query would return without limit set. To change/delete the limit check the manual of your SQL client
    – HelgeB
    Commented Feb 3, 2019 at 8:07
  • @HelgeB Yeah, you are right!. Thank you, buddy :) Commented Feb 3, 2019 at 14:41
  • Dude, how can i know if a product is enabled or disabled? I mean, for example: i want to obtain all the products with "status = disabled". How can i do that? Take a look, i have edited the question. I tried to do that buy i failed :( Commented Feb 3, 2019 at 15:28
  • @HelgeB No, it is not working. I was not able to get ALL the products. Take a look: magento.stackexchange.com/questions/260305/… Commented Feb 4, 2019 at 2:06


Your Answer

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

Browse other questions tagged or ask your own question.