CODEWITHSUNDEEP
sqlruby-on-rails
blazeP
blazeP

Reputation: 91

Rails - what is the most efficient way to build query for multiple tags

I have this situation:

Two models: - Meal - Tag

With a many-to-many relationship, So product can have assigned many tags.

There is additional table for association (meal_id, tag_id) Then user choose some tags (for example 8).

I must write query in Rails (but can be only sql idea), which return:

  1. meals which has assigned at least one tag from chosen by user.
  2. meals which has assigned all chosen tags.

I don't know how to do it in most efficient way. Could you help me a little?

Meal.rb

class Meal < ActiveRecord::Base
   has_and_belongs_to_many :tags
end

Tag.rb

class Tag < ActiveRecord::Base
   has_and_belongs_to_many :meals
end

Upvotes: 1

Views: 229

Answers (2)

akbarbin
akbarbin

Reputation: 5105

Let's try to get Meal with dynamic tag_ids. You have to collect tag_ids then you join Meal by tag_ids

tag_ids = [1, 2, 3, 4]
Meal.joins(:tags).where('tags.id IN (?)', tags_ids).group("meals.id")
                    .having("COUNT(meals.id) >= ?", tag_ids.length)

I hope this help you.

Upvotes: 1

JuanM.
JuanM.

Reputation: 432

To return the queries you want try the following:

  1. "Meals which has assigned at least one tag":
    # we will store meal objects in this array
    #the ones that has at least one tag
    meals = []

    # go over all meals
    Meal.all.each do |meal|

      # if meal has tags store it
      if !meal.tags.blank?
        meals << meal
      end

    end

    # then I have all meals with at least one tag in my array called meals
    # and I can do whatever I want with it
    meals.each do |m|
      #do something with each meal
    end

    how_many_meals_with_one_tag_at_least = meals.count
  1. "Meals which has assigned all chosen tags". I think you mean: "Meals which has assigned all existent tags". 
    # again our array to store meals
    meals = []

    maximumTags = Tag.all.count   

    Meal.all.each do |meal|

      if meal.tags.count == maximumTags
        meals << meal
      end

    end

    meals.each do |m|
      #do something with your meal with all tags assigned
    end

I know this is approach is more ruby than sql, but it's easy to implement and gets you there.

Upvotes: 0

Related Questions