How to write a named scope to filter over the entire array passed in, and not just by matching a single element (using IN)

Question

I have two models: Project and Category, which have a many-to-many relationship between them. The project model is very simple:

class Project < ActiveRecord::Base
  has_and_belongs_to_many :categories

  scope :in_categories, lambda { |categories|
    joins(:categories).
    where("categories.id in (?)", categories.collect(&:to_i))
  }
end

Scope: in_categories accepts an array of category identifiers (as strings), so using this scope I can return every project that belongs to at least one of the categories passed to.

But what I'm actually trying to do is filter (the best name would be: has_categories). I just want to get projects that belong to all the categories that have been transferred. Therefore, if I go to ["1", "3", "4"], I want to get only projects that belong to all categories.

Answer 1

SQL , .

:

SELECT ...
FROM Projects p
JOIN Categories c1 ON c1.project_id = p.id
JOIN Categories c3 ON c3.project_id = p.id
JOIN Categories c4 ON c4.project_id = p.id
WHERE (c1.id, c3.id, c4.id) = (1, 3, 4);

. . :

WHERE c1.id = 1 AND c3.id = 3 AND c4.id = 4;

, , . , Categories.(project_id,id) , , , SQL EXPLAIN.

, , , . ..

Group-:

SELECT ...
FROM Projects p
JOIN Categories cc ON c.project_id = p.id
WHERE c.id IN (1, 3, 4)
GROUP BY p.id
HAVING COUNT(*) = 3;

MySQL (, ), GROUP BY , .

, SQL- API Rails ActiveRecord.

Answer 2

, ActiveRecord :

scope :has_categories, lambda { |categories|
  joins(:categories).
  where("categories.id in (?)", categories.collect(&:to_i)).
  group("projects.id HAVING COUNT(projects.id) = #{categories.count}")
}