ruby-on-railsactiverecordjsonapi-resources

Filter by a field of a join table using JSONAPI Resources


I have three tables:

  1. Observation
  2. Subcategory
  3. Category

with the following structure:

class Observation < ApplicationRecord
  translates :name
  belongs_to :subcategory
end

class Subcategory < ApplicationRecord
  belongs_to :category
  has_many :observations
end

class Category < ApplicationRecord
  has_many :subcategories
end

I want to be able to filter with JSONAPI::Resource the observations based on a category_id

class ObservationResource < JSONAPI::Resource
  attributes :name, :subcategory_id
  filter :subcategory_id, :test

  filter :test, apply: ->(records, value, _options) {
    records.where('subcategories.category_id = ?', value[0])
  }
end

If I try to do the request:

/observations?include=subcategory,subcategory.category&filter[subcategory.category_id]=1

I get:

subcategory.category_id is not allowed

If I try to do the request:

/observations?include=subcategory,subcategory.category&filter[test]=1

I get an exception coming from Postgres when trying to execute:

SQL (6.4ms) SELECT DISTINCT "observations"."id", "observations"."id" AS alias_0 FROM "observations" LEFT OUTER JOIN "observation_translations" ON "observation_translations"."observation_id" = "observations"."id" WHERE (subcategories.category_id = '1') ORDER BY "observations"."id" ASC LIMIT $1 OFFSET $2 [["LIMIT", 1000], ["OFFSET", 0]]

Because there's no join on the table subcategories

If I add a default scope to include the subcategories all the queries work well but the last one fails, when trying to count the records:

SELECT COUNT(*) FROM "observations" WHERE (subcategories.category_id = '1')

How can I do this properly?


Solution

  • Ok, the solution was to use a ´joins`:

    filter :test, apply: ->(records, value, _options) {
      records.joins(:subcategory).where('subcategories.category_id = ?', value[0])
    }