Categories and their Subcategories using OR

data & reporting
,
1

Sometimes you want to find information about topics that are in certain categories, plus all the topics in those categories’ subcategories too. You can add the category_id of each subcategory to your query, but this can produce a pretty long list depending on your category/subcategory setup. :slight_smile:

An alternative method is to add in the categories table and use OR:
(Make sure to amend the value to be the one of your parent category)

SELECT 
    t.category_id,
    t.id AS topic_id
FROM topics t
  JOIN categories c ON c.id = t.category_id
WHERE (c.id = 5 OR c.parent_category_id = 5)

You can also use this with a parameter:

-- [params]
-- int :category_id

SELECT 
    t.category_id,
    t.id AS topic_id
FROM topics t
  JOIN categories c ON c.id = t.category_id
WHERE (c.id = :category_id OR c.parent_category_id = :category_id)

And to amend it for multiple categories with subcategories you can swop the parameter for an int_list version and adapt your query to use IN like so:

-- [params]
-- int_list :category_id

SELECT 
    t.category_id,
    t.id AS topic_id
FROM topics t
  JOIN categories c ON c.id = t.category_id
WHERE (c.id IN (:category_id) OR c.parent_category_id IN (:category_id))

Or hard-code the parent categories into the query itself:

SELECT 
    t.category_id,
    t.id AS topic_id
FROM topics t
  JOIN categories c ON c.id = t.category_id
WHERE (c.id IN (4, 5) OR c.parent_category_id IN (4, 5))

These queries are mainly examples to demonstrate the principles. Hopefully you find them useful, but if you have any questions please ask them below. :slight_smile:

7 Likes