Rails 4 - Join Query matches all in array

Question

I have two models, Facility and Category:

class Facility < ActiveRecord::Base has_and_belongs_to_many :categories end class Category < ActiveRecord::Base has_and_belongs_to_many :facilities end

Say I have three possible Category records, with name: "Category A", "Category B", and "Category C".

I want to get all Facility records which are in both "Category A" and "Category B".

My latest query is:

Facility.joins(:categories).merge(Category.where(Category.arel_table[:name].matches_all(["Category A", "Category B"])))

which produces the following SQL:

SELECT "facilities".* FROM "facilities" INNER JOIN "categories_facilities" ON "categories_facilities"."facility_id" = "facilities"."id" INNER JOIN "categories" ON "categories"."id" = "categories_facilities"."category_id" WHERE ("categories"."name" ILIKE 'Category A' AND "categories"."name" ILIKE 'Category B')

This returns no results.

Using pure Ruby (e.g. Facility.all.select ...), I know there is at least one Facility in the database which belongs to both and only both "Category A" and "Category B".

How can I perform this query either in Rails or using arel?

CDub · Accepted Answer · 2016-12-07 19:05:06Z

I was able to solve this with switching from a has_and_belongs_to_many to has_many, through, and the following scope on Facility:

joins(:categories) .merge(Category.where(key: category_keys)) .group(CategoriesFacility.arel_table[:facility_id], arel_table[:id]) .having(CategoriesFacility.arel_table[:facility_id].count.gteq(category_keys.size)) .uniq

Ideally, I'd like to still use a HABTM table, but I prefer not to write raw SQL in my code.

Collectives™ on Stack Overflow

Rails 4 - Join Query matches all in array

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related