Today I was looking at adding a TopicQuery custom filter that included any topic in a category with a certain custom field enabled, or any descendants (max_depth of 3) of an “enabled” category, even if the descendants didn’t have the field enabled.
This is what I landed on, i.e. a recursive query with the category_custom_field query as the non-recursive term. This approach does work.
TopicQuery.add_custom_filter(:custom_enabled) do |result, query| if ActiveModel::Type::Boolean.new.cast(query.options[:custom_enabled]) result = result.where(" topics.category_id IN ( WITH RECURSIVE enabled_categories(id) AS ( SELECT category_id AS id FROM category_custom_fields WHERE name = 'custom_enabled' AND value::boolean IS TRUE UNION ALL SELECT categories.id FROM categories, enabled_categories WHERE categories.parent_category_id = enabled_categories.id ) SELECT id FROM enabled_categories ) ") end result end
I’m partly posting this as note to remember this approach, but also wondering if anyone has any suggested tweaks, or a better approach.