sql - 如何在 Rails 中完成深度条件查询？

Question

我正在尝试根据深度条件查询数据库。例如，我想找到所有其主要宠物具有特定品种名称的人:

人物 > 宠物 > 品种 > 名字

模型可能看起来像这样。

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

现在我可以做:

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

但我想找出使用​​ where 方法执行此操作的正确查询语法。以下不起作用:

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

Answer 1

我通过少量编辑将模型关系形式化，最后得到:

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

我想到的(实际有效的)最好的查询是:

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

这表示加入宠物 和品种 的关系。这允许使用 breeds 结构来过滤“Norwich Terrier”。最后，通过与 pets.id 相关的 primary_pet_id 进行额外过滤。

这应该能得到您在问题中要求的内容。

我是这样测试的:

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")