Categories and their Subcategories using OR

Community Building Data & reporting
,

AI-generated summary

JammyDodger explains how to retrieve topics from specific categories and their subcategories using SQL queries with OR. Instead of listing all subcategory IDs, the approach joins the categories table and checks if a category’s id or parent_category_id matches the target. This method supports single categories, parameters, multiple categories using IN, and hard-coded values. The examples demonstrate flexible querying for data reporting, particularly useful when working with hierarchical category structures.

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