Magento 1 – SQL Query to Obtain All Products Info from a Specific Category

categorymagento-1product-collectionsql

I have on the tree of categories one category named Trademarks and within this one, a category named Pharmatron (and many other). I am trying to get all products from this category (Pharmatron). The category Trademarks is just another way of providing access to the products in the frontend menu, in case the visitor just wants to check products from a specific brand.

The ID of Trademarks is 7 and the ID of Pharmatron is 411.

I am kind of guessing, but not quite coming clear..
select *

FROM cp8_catalog_product_entity_varchar as cepvar
LEFT JOIN cp8_catalog_category_entity_varchar as ccevar
ON cepvar.entity_id = ccevar.entity_id
LEFT JOIN eav_attribute AS ea 
ON cepvar.entity_type_id = ea.entity_type_id 
AND ea.backend_type = 'varchar'
AND cepvar.value = 'Pharmatron'

I am getting zero rows with that query. However, if I remove the WHERE clause I get basically a whole list of any product.

I have learned a bit about the EAV system and I understand why it has to have those JOINS, yet not grasped fully the interrelation between categories and products tables so as to solve the query.

Best Answer

Magento-way:

echo Mage::getModel('catalog/category')
    ->load(1) // your category ID here
    ->getProductCollection()
    ->getSelect()
    ->__toString();

Result from above:

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`
FROM `prefix_catalog_product_entity` AS `e`
INNER JOIN `prefix_catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.category_id = '1' AND cat_index.is_parent=1

Related Solutions

Magento – List of products and category with thesql

Magento saves product & category associations in the catalog_category_product table.

and Sku is saved at product main table catalog_product_entity.Now using query you can fulfill your requirement

Mysql query like

SELECT cat.product_id, GROUP_CONCAT( cat.category_id ) , product.sku
FROM catalog_category_product AS cat, catalog_product_entity AS product
WHERE cat.product_id = product.entity_id
GROUP BY cat.product_id

Magento – SQL Query to get actual name of custom attribute value

Try following query.

$query = "SELECT ce.sku,           
            ea.attribute_code,
            CASE ea.backend_type
               WHEN 'varchar' THEN ce_varchar.value
               WHEN 'int' THEN ce_int.value
               WHEN 'text' THEN ce_text.value
               WHEN 'decimal' THEN ce_decimal.value
               WHEN 'datetime' THEN ce_datetime.value
               ELSE ea.backend_type
            END AS value
        FROM (select sku,entity_type_id,entity_id from catalog_product_entity ) AS ce
        LEFT JOIN eav_attribute AS ea
            ON ce.entity_type_id = ea.entity_type_id
        LEFT JOIN catalog_product_entity_varchar AS ce_varchar
            ON ce.entity_id = ce_varchar.entity_id
            AND ea.attribute_id = ce_varchar.attribute_id
            AND ea.backend_type = 'varchar'
        LEFT JOIN catalog_product_entity_int AS ce_int
            ON ce.entity_id = ce_int.entity_id
            AND ea.attribute_id = ce_int.attribute_id
            AND ea.backend_type = 'int'
        LEFT JOIN catalog_product_entity_text AS ce_text
            ON ce.entity_id = ce_text.entity_id
            AND ea.attribute_id = ce_text.attribute_id
            AND ea.backend_type = 'text'
        LEFT JOIN catalog_product_entity_decimal AS ce_decimal
            ON ce.entity_id = ce_decimal.entity_id
            AND ea.attribute_id = ce_decimal.attribute_id
            AND ea.backend_type = 'decimal'
        LEFT JOIN catalog_product_entity_datetime AS ce_datetime
            ON ce.entity_id = ce_datetime.entity_id
            AND ea.attribute_id = ce_datetime.attribute_id
            AND ea.backend_type = 'datetime'";

Related Topic