A video can have multiple categories.
video.rb
has_many :video_categories
has_many :categories, through: :video_categories
category.rb
has_many :video_categories
has_many :videos, through: :video_categories
I have this simple form that allows the user to select the categories he wants to combine in order to find specific videos. For example, there's a "python" category and "advanced" category. If he selected these two categories, it should show videos that has both categories.
Video A - Categories [1,4,7] (this is the categories ids)
Video B - Categories [1,2,7,9]
Video C - Categories [7,9]
If the user select the categories [1,7], the output should be video A and B. The current scope that I have its returning ALL videos that has category 1, ALL videos that has category 7 and videos that has BOTH of them.
I want just the videos that has BOTH of them. How do I do that ?
Current scope:
video.rb
scope :for_categories, -> (category_ids) {
joins(:video_categories).where(
video_categories: { category_id: category_ids }
)
}
pages_controller.rb
def search
@results = Video.for_categories(params[:category_ids])
end
My form
<%= form_with url: "/search", method: :get do |form| %>
<%= form.collection_check_boxes(:category_ids, Category.all,
:id, :title, { prompt: 'None'}, { multiple: true} ) do |cat| %>
<label class="text-capitalize checkbox-inline mr-2">
<%= cat.check_box %>
<%= cat.label %>
</label>
<% end %>
<%= form.submit "Search" %>
<% end %>
CodePudding user response:
I was able to find the solution with the following code
video.rb
scope :by_categories, -> (category_ids) {
joins(:categories).where(categories: [category_ids] )
}
scope :by_all_categories, -> (category_ids) {
by_categories(category_ids).
group("videos.id").
having('count(videos.id) >= ?', category_ids.size)
}
pages_controller.rb
def search
@results = Video.by_all_categories(params[:category_ids].reject(&:empty?))
end
CodePudding user response:
You want apply a GROUP and use HAVING to set a condition on the group to ensure that the number of categories joined matches the number of ids:
class Video
def self.for_categories(*category_ids)
ids = category_ids.flatten # handles a single array as input
group(:id)
.joins(:categories)
.where(categories: { id: ids })
.having(
Category.arel_table[:id]
.count
.gteq(ids.length)
)
end
end
In terms of SQL this will give you something like:
SELECT videos.*
FROM videos
INNER JOIN video_categories ON video_categories.video_id = videos.id
INNER JOIN categories ON video_categories.category_id = categories.id
WHERE categories.id IN (1, 2, 3)
GROUP BY videos.id
HAVING COUNT(categories.id) >= 3