Categories and their Subcategories using OR

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