Home > Blockchain >  Rails active record query to retrive all records that contains ALL IDS in the array of IDS
Rails active record query to retrive all records that contains ALL IDS in the array of IDS

Time:10-13

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
  • Related