Ruby-on-rails 想在 Rails 中查找没有关联记录的记录

Want to find records with no associated records in Rails

提问by craic.com

Consider a simple association...

考虑一个简单的关联...

class Person
   has_many :friends
end

class Friend
   belongs_to :person
end

What is the cleanest way to get all persons that have NO friends in ARel and/or meta_where?

让所有在 ARel 和/或 meta_where 中没有朋友的人的最干净的方法是什么？

And then what about a has_many :through version

那么 has_many :through 版本呢？

class Person
   has_many :contacts
   has_many :friends, :through => :contacts, :uniq => true
end

class Friend
   has_many :contacts
   has_many :people, :through => :contacts, :uniq => true
end

class Contact
   belongs_to :friend
   belongs_to :person
end

I really don't want to use counter_cache - and I from what I've read it doesn't work with has_many :through

我真的不想使用 counter_cache - 从我读过的内容来看，它不适用于 has_many :through

I don't want to pull all the person.friends records and loop through them in Ruby - I want to have a query/scope that I can use with the meta_search gem

我不想提取所有 person.friends 记录并在 Ruby 中遍历它们 - 我想要一个可以与 meta_search gem 一起使用的查询/范围

I don't mind the performance cost of the queries

我不介意查询的性能成本

And the farther away from actual SQL the better...

离实际 SQL 越远越好......

采纳答案by Unixmonkey

This is still pretty close to SQL, but it should get everyone with no friends in the first case:

这仍然与 SQL 非常接近，但在第一种情况下，它应该让每个没有朋友的人都能得到：

Person.where('id NOT IN (SELECT DISTINCT(person_id) FROM friends)')

回答by smathy

Better:

更好的：

Person.includes(:friends).where( :friends => { :person_id => nil } )

For the hmt it's basically the same thing, you rely on the fact that a person with no friends will also have no contacts:

对于 hmt 来说，它基本上是一样的，你依赖于一个没有朋友的人也没有联系人的事实：

Person.includes(:contacts).where( :contacts => { :person_id => nil } )

Update

更新

Got a question about has_onein the comments, so just updating. The trick here is that includes()expects the name of the association but the whereexpects the name of the table. For a has_onethe association will generally be expressed in the singular, so that changes, but the where()part stays as it is. So if a Persononly has_one :contactthen your statement would be:

has_one在评论中有一个问题，所以只是更新。这里的技巧是includes()需要关联的名称，但where需要表的名称。对于 a has_one，关联通常用单数表示，因此会发生变化，但where()部分保持原样。因此，如果Person只有has_one :contact那么您的陈述将是：

Person.includes(:contact).where( :contacts => { :person_id => nil } )

Update 2

更新 2

Someone asked about the inverse, friends with no people. As I commented below, this actually made me realize that the last field (above: the :person_id) doesn't actually have to be related to the model you're returning, it just has to be a field in the join table. They're all going to be nilso it can be any of them. This leads to a simpler solution to the above:

有人问逆，朋友无人。正如我在下面评论的那样，这实际上让我意识到最后一个字段（上面： the :person_id）实际上不必与您返回的模型相关，它只需要是连接表中的一个字段。他们都将nil如此，所以它可以是其中的任何一个。这导致了上述问题的更简单的解决方案：

Person.includes(:contacts).where( :contacts => { :id => nil } )

And then switching this to return the friends with no people becomes even simpler, you change only the class at the front:

然后切换这个返回没有人的朋友就变得更简单了，你只改变前面的类：

Friend.includes(:contacts).where( :contacts => { :id => nil } )

Update 3 - Rails 5

更新 3 - Rails 5

Thanks to @Anson for the excellent Rails 5 solution (give him some +1s for his answer below), you can use left_outer_joinsto avoid loading the association:

感谢@Anson 提供了出色的 Rails 5 解决方案（在下面给他一些 +1 的答案），您可以使用它left_outer_joins来避免加载关联：

Person.left_outer_joins(:contacts).where( contacts: { id: nil } )

I've included it here so people will find it, but he deserves the +1s for this. Great addition!

我已经把它包括在这里，所以人们会找到它，但他应该为此得到 +1。很棒的补充！

Update 4 - Rails 6.1

更新 4 - Rails 6.1

Thanks to Tim Parkfor pointing out that in the upcoming 6.1 you can do this:

感谢Tim Park指出在即将发布的 6.1 中你可以这样做：

Person.where.missing(:contacts)

Thanks to the posthe linked to too.

也感谢他链接到的帖子。

回答by Anson

smathy has a good Rails 3 answer.

smathy 有一个很好的 Rails 3 答案。

For Rails 5, you can use left_outer_joinsto avoid loading the association.

对于 Rails 5，您可以使用left_outer_joins避免加载关联。

Person.left_outer_joins(:contacts).where( contacts: { id: nil } )

Check out the api docs. It was introduced in pull request #12071.

查看api 文档。它是在 pull request #12071 中引入的。

回答by novemberkilo

Persons that have no friends

没有朋友的人

Person.includes(:friends).where("friends.person_id IS NULL")

Or that have at least one friend

或者至少有一个朋友

Person.includes(:friends).where("friends.person_id IS NOT NULL")

You can do this with Arel by setting up scopes on Friend

你可以通过在 Arel 上设置范围来做到这一点 Friend

class Friend
  belongs_to :person

  scope :to_somebody, ->{ where arel_table[:person_id].not_eq(nil) }
  scope :to_nobody,   ->{ where arel_table[:person_id].eq(nil) }
end

And then, Persons who have at least one friend:

然后，至少有一个朋友的人：

Person.includes(:friends).merge(Friend.to_somebody)

The friendless:

没朋友的：

Person.includes(:friends).merge(Friend.to_nobody)

回答by craic.com

Both the answers from dmarkow and Unixmonkey get me what I need - Thank You!

来自 dmarkow 和 Unixmonkey 的答案都能满足我的需求 - 谢谢！

I tried both out in my real app and got timings for them - Here are the two scopes:

我在我的真实应用程序中尝试了这两种方法并获得了它们的时间 - 这是两个范围：

class Person
  has_many :contacts
  has_many :friends, :through => :contacts, :uniq => true
  scope :without_friends_v1, -> { where("(select count(*) from contacts where person_id=people.id) = 0") }
  scope :without_friends_v2, -> { where("id NOT IN (SELECT DISTINCT(person_id) FROM contacts)") }
end

Ran this with a real app - small table with ~700 'Person' records - average of 5 runs

使用真正的应用程序运行此程序 - 包含约 700 条“人”记录的小表 - 平均运行 5 次

Unixmonkey's approach (:without_friends_v1) 813ms / query

Unixmonkey 的方法 ( :without_friends_v1) 813ms / query

dmarkow's approach (:without_friends_v2) 891ms / query (~ 10% slower)

dmarkow 的方法 ( :without_friends_v2) 891ms / query (~慢 10%)

But then it occurred to me that I don't need the call to DISTINCT()...I'm looking for Personrecords with NO Contacts- so they just need to be NOT INthe list of contact person_ids. So I tried this scope:

但是后来我想到我不需要打电话给DISTINCT()...我正在寻找PersonNO 的记录Contacts- 所以他们只需要成为NOT IN联系人列表person_ids。所以我尝试了这个范围：

  scope :without_friends_v3, -> { where("id NOT IN (SELECT person_id FROM contacts)") }

That gets the same result but with an average of 425 ms/call - nearly half the time...

这得到了相同的结果，但平均为 425 毫秒/呼叫 - 几乎一半的时间......

Now you might need the DISTINCTin other similar queries - but for my case this seems to work fine.

现在您可能需要DISTINCT其他类似的查询 - 但就我而言，这似乎工作正常。

Thanks for your help

谢谢你的帮助

回答by Dylan Markow

Unfortunately, you're probably looking at a solution involving SQL, but you could set it in a scope and then just use that scope:

不幸的是，您可能正在寻找一个涉及 SQL 的解决方案，但您可以将它设置在一个范围内，然后只使用该范围：

class Person
  has_many :contacts
  has_many :friends, :through => :contacts, :uniq => true
  scope :without_friends, where("(select count(*) from contacts where person_id=people.id) = 0")
end

Then to get them, you can just do Person.without_friends, and you can also chain this with other Arel methods: Person.without_friends.order("name").limit(10)

然后为了得到它们，你可以只做Person.without_friends，你也可以将它与其他 Arel 方法链接起来：Person.without_friends.order("name").limit(10)

回答by Dorian

Also, to filter out by one friend for instance:

此外，例如通过一位朋友过滤掉：

Friend.where.not(id: other_friend.friends.pluck(:id))

回答by David Aldridge

A NOT EXISTS correlated subquery ought to be fast, particularly as the row count and ratio of child to parent records increases.

NOT EXISTS 相关子查询应该很快，特别是当子记录与父记录的行数和比率增加时。

scope :without_friends, where("NOT EXISTS (SELECT null FROM contacts where contacts.person_id = people.id)")