1

I am trying without success to obtain all products from MAGENTO database. (SQL only)

After a few attempts i got this query (it is not working properly because it shows empty rows after executing it):

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'
    )
)

This is what i need: i want to obtain ALL products that have these attributes:

image

A guy suggested me to use this query:

    SELECT 
    `e`.`sku`, 
    IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name`,
    IF(at_description.value_id > 0, at_description.value, at_description_default.value) AS `description`

FROM 
   `catalog_product_entity` AS `e` 
    INNER JOIN 
         `catalog_product_entity_varchar` AS `at_name_default` 
               ON (`at_name_default`.`entity_id` = `e`.`entity_id`) AND 
                  (`at_name_default`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'name' AND et.entity_type_code = 'catalog_product')) AND 
                  `at_name_default`.`store_id` = 0 
    LEFT JOIN 
          `catalog_product_entity_varchar` AS `at_name` 
               ON (`at_name`.`entity_id` = `e`.`entity_id`) AND 
                  (`at_name`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'name' AND et.entity_type_code = 'catalog_product')) AND 
                  (`at_name`.`store_id` = 1) 
    INNER JOIN 
         `catalog_product_entity_text` AS `at_description_default` 
               ON (`at_description_default`.`entity_id` = `e`.`entity_id`) AND 
                  (`at_description_default`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'description' AND et.entity_type_code = 'catalog_product')) AND 
                  `at_description_default`.`store_id` = 0 
    LEFT JOIN 
          `catalog_product_entity_text` AS `at_description` 
               ON (`at_description`.`entity_id` = `e`.`entity_id`) AND 
                  (`at_description`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'description' AND et.entity_type_code = 'catalog_product')) AND 
                  (`at_description`.`store_id` = 1)

The problem is that i dont fully understand how does this work.. he talked about store view for an specific id and i dont know how to do that.

PS: I'm using MAGENTO 1.9

This is the database diagram:

Image2

0

Your Answer

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