List active products in a category in Magento with SQL

Question

I'm creating a report using the Magento databse and I just can't get it to cooperate with me.

I need to list the categories of a shop as well as how many Active Products there are in each category.

Ie: Bags (5) Shoes (12) Pencils (6)

etc.

what I found so far is that:

catalog_category_flat_store_9 lists the categories of the store along with a category id
the category id can be used in catalog_category_product to get Product id's
the Product id can be used in catalog_product_entity to get the sku (and to filter out the configured Products)
the sku can be used in catalog_product_flat_9 to get an entity id (which is retarded - they should have just included the categories and if it's active in there)
the entity id can be used in catalog_product_entity_int to get a list of integers
eav_attribute can be used to find that attribute id 96 (in my case) is the status attribute that shows wether or not something is Active

is this the right way to go about or is there a much simpler way of doing this?

So far, I've gotten as far as

SELECT entity_id FROM `catalog_product_flat_9` WHERE sku = (SELECT p.sku
FROM catalog_product_entity AS p
LEFT JOIN catalog_category_product AS cp ON p.entity_id = cp.product_id
WHERE p.type_id = 'simple'
AND cp.category_id = 89)

which isn't working, but at least it's a start

Enigmativity Enigmativity · Accepted Answer · 2015-11-23T00:10:31

This might help a bit.

Here's my query for retrieving the category names that are active, along with the count of all of the products within the category, such that the count is greater than zero.

SELECT t0.value, t0.c0
FROM (
  SELECT t2.value, (
    SELECT COUNT(*)
    FROM catalog_category_product AS t4
    WHERE (t4.category_id = t1.entity_id)) AS c0
  FROM catalog_category_entity AS t1
  INNER JOIN catalog_category_entity_varchar AS t2 ON (t1.entity_id = t2.entity_id)
  INNER JOIN catalog_category_entity_int AS t3 ON (t1.entity_id = t3.entity_id)
  WHERE (((((t2.attribute_id = 41)
      AND (t2.store_id = 0))
      AND (t3.attribute_id = 42))
      AND (t3.store_id = 0))
      AND (t3.value = 1))) AS t0
WHERE (t0.c0 > 0)

I haven't tried filtering products by what is in stock though.

List active products in a category in Magento with SQL

1 Answers