I have three tables:
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?
Ok, the solution was to use a ´joins`:
filter :test, apply: ->(records, value, _options) {
records.joins(:subcategory).where('subcategories.category_id = ?', value[0])
}