CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresqlactiverecord
tsyan
tsyan

Reputation: 41

Rails query with multiple conditions on the same field

I have two models, Recipes and Skills. In this scenario, a skill is a cooking technique, like baking, frying, etc. So each recipe has a certain set of associated skills.

I want to search all the recipes like this:

  1. Find all recipes that use any given set of skills (e.g. Baking OR Frying OR both)
    EDIT: This should NOT return recipes that require a skill that wasn't in the search query - e.g. If I search for skills [1, 2] I don't want a recipe that uses skills [1, 2, 4] or any other superset.

  2. If you add a new skill to the search, return just the additional recipes (e.g. if you add Boiling to the previous query of Baking or Frying, how many NEW recipes can you now cook?)

I currently have this working in Rails using plain old Ruby methods: 

class Recipe < ActiveRecord::Base
  has_many :practices
  has_many :skills, through: :practices

  def self.find_recipes_that_require_any_of_these_skills(*known_skill_ids)
    self.select do |recipe|
      recipe.skill_ids.all? do |skill_id|
        known_skill_ids.include?(skill_id)
      end
    end
  end

  # calls the above method twice, once with the new skill and once without
  # and subtracts one result from the other
  def self.find_newly_unlocked_recipes(*prior_skill_ids, new_skill_id)
    self.find_recipes_that_require_any_of_these_skills(*(prior_skill_ids + [new_skill_id])) - self.find_recipes_that_require_any_of_these_skills(*prior_skill_ids)
  end
end

In Rails console: Recipe.find_recipes_that_require_any_of_these_skills(1,4)
returns all the Recipe objects for skill 1, skill 4, or skills 1 & 4.

But this is inefficient because it generates a SQL query for every single recipe in my database.

How can I write these queries the ActiveRecord/SQL way?

Upvotes: 2

Views: 3310

Answers (5)

chumakoff
chumakoff

Reputation: 7034

Two queries to DB:

def self.find_recipes_that_require_any_of_these_skills(*known_skill_ids)
  Recipe.joins(:skills)
        .merge(Skill.where(id: known_skill_ids))
        .where("recipes.id NOT IN (?)", Recipe.joins(:skills).merge(Skill.where("skills.id NOT IN (?)", known_skill_ids)).uniq.pluck(:id)).uniq
end

Upvotes: 0

chumakoff
chumakoff

Reputation: 7034

It seems to be that your find_recipes_that_require_any_of_these_skills method isn't correct. It returns recipes that have all of the known_skill, not any.

So, the ActiveRecord/SQL way:

Known skills:

class Skill < ActiveRecord::Base

    #known skills
    scope :known_skills, -> { where(id: known_skill_ids) }

    #not known skills
    scope :not_known_skills, -> { where("skills.id NOT IN (?)", known_skill_ids) }

Recipes that have any of known skills:

Recipe.joins(:skills).merge(Skill.known_skills)

Newly_unlocked_recipes:

Recipe.joins(:skills).merge(Skill.not_known_skills).where("skills.id = ?", new_skill_id)

Upvotes: 0

Ruby Racer
Ruby Racer

Reputation: 5740

The following method uses just three sql queries in total to create a collection of recipies

array_of_skills=["cooking","frying",...]
skills=Skill.where('name in (?)',array_of_skills).map(&:id)

Up to here you already have, so you might need just this:

practices=Practice.where('skill_id in (?)',skills).map(&:recipe_id)
recipes=Recipe.where('id in (?)', practices)

Maybe there is a better way, but I don't think there would be something with much less sql

Upvotes: 0

Robert Krzyzanowski
Robert Krzyzanowski

Reputation: 9344

def self.find_recipes_that_require_any_of_these_skills(*known_skill_ids)
  self.includes(:skills).where(skills: { id: known_skill_ids })
end

Upvotes: 2

mralexlau
mralexlau

Reputation: 1993

Since you're using has_many :skills, through: :practices - your Practices table should have both recipe_id and skills_id columns.

previous_recipe_ids = Recipe.joins(:practices).where('practices.skill_id in (?)', prior_skills_ids).map(&:id) Recipe.joins(:practices).where('practices.skill_id = (?) and recipes.recipe_id not in (?)', new_skill_id, previous_recipe_ids)

Upvotes: 0

Related Questions