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.
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.