CODEWITHSUNDEEP
sqlruby-on-railsruby
David English
David English

Reputation: 63

How to complete a query in Rails with a deep condition?

I am attempting to query a database based on a deep condition. As an example, I would like to find all of the people whose primary pet has a specific breed name:

Person > Pet > Breed > Name

The models might look like this.

class Person
  has_many pets
  attr_accessor :primary_pet
end

class Pet
  belongs_to Person
  has_one Breed
end

class Breed
  # no belonging relationship
  attr_accessor :name
end

Now I could do:

Person.find_each do |person|
  puts person if primary_pet.breed.name == 'Norwich Terrier'
end

but I want to figure out the proper query syntax for doing this with a where method. The following does not work:

Person.where(primary_pet.breed.name: 'Norwich Terrier').find_each do |person|
  puts person
end

Upvotes: 2

Views: 213

Answers (1)

Michael Gaskill
Michael Gaskill

Reputation: 8042

I formalized the model relationships with minor edit, and ended up with this:

class Person < ActiveRecord::Base
  has_many :pets
  attr_accessor :primary_pet
end

class Pet < ActiveRecord::Base
  belongs_to :person
  belongs_to :breed
end

class Breed < ActiveRecord::Base
  # no belonging relationship
  attr_accessor :name
end

The best query that I came up with (that actually worked) was this:

Person.joins(pets: :breed).where(breeds: { name: 'Norwich Terrier' }).where("people.primary_pet = pets.id")

This says to join the relationship for pets and to breeds. This allows using the breeds structure to filter for 'Norwich Terrier'. And to finish it off, to additionally filter by primary_pet_id related to pets.id.

That should get you what you asked for in the question.

Here's how I tested it:

breed_names = ["Beagle", "Collie", "Norwich Terrier", "German Shepard", "Dachshund"]
people_names = ["Barbara", "Connie", "Paula", "Gerald", "Dave"]

# Add breeds
breed_names.each {|name| Breed.create!(name: name) }
# Add people
people_names.each {|name| Person.create!(name: name) }

# Add some pets
people_names.each do |name|
  person = Person.find_by(name: name)
  breed_names.sample(3).each do |breed|
    breed = Breed.find_by(name: breed_name)
    Pet.create!(breed_id: breed.id, person_id: person.id)
  end
end

# Assign the primary_pet for each person
Person.all.each {|person| pet = person.pets.first ; person.update!(primary_pet: pet) if pet }

# List all people and their pets by breed
Person.all.each do |person|
  puts "#{person.name}: #{person.pets.map {|pet| pet.breed.name}}"
end

# List all people and their primary pet breed
Person.all.each do |person|
  puts "#{person.name}: #{person.primary_pet ? person.primary_pet.breed.name : ''}"
end

# Run the query
Person.joins(pets: :breed).where(breeds: { name: 'Norwich Terrier' }).where("people.primary_pet = pets.id")

Upvotes: 1

Related Questions