Filtering topics by categories with a custom field, or their descendants

Dev
1

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.

3 Likes