blocks|key|750765|text|不幸的是，您可能正在寻找一个涉及SQL的解决方案，但是您可以将其设置在一个作用域中，然后直接使用该作用域：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|750766|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|code-block|syntax|javascript|750767|然后，为了获得它们，您可以执行Person.without_friends，也可以将其与其他Arel方法链接在一起：Person.without_friends.order("name").limit(10)|offset|length|style|CODE|750768|entityMap^0|0|0|F|M|1M|1A|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

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

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

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

blocks|key|750802|text|这仍然非常接近SQL，但在第一种情况下，它应该会让没有朋友的每个人：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|750803|Person.where('id+NOT+IN+(SELECT+DISTINCT(person_id)+FROM+friends)')|code-block|syntax|javascript|750804|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>Person.where('id NOT IN (SELECT DISTINCT(person_id) FROM friends)')
</code></pre>

blocks|key|966417|text|来自dmarkow和need的答案都满足了我的需求--谢谢！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|966418|我在我的真实应用程序中尝试了这两种方法，并获得了它们的时间--以下是两个作用域：|966419|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|code-block|syntax|javascript|966420|使用一个真实的应用程序运行此应用程序-包含约700个“人”记录的小表-平均运行5次|966421|query的方法(:without_friends_v1)+813ms+/查询|offset|length|style|CODE|966422|dmarkow方法(:without_friends_v2)+891ms+/查询(大约慢10%25+)|966423|但后来我突然想到，我不需要调用DISTINCT()...，我正在寻找没有Contacts的Person记录-所以他们只需要在联系人列表中NOT+IN+person_ids即可。所以我尝试了这个作用域：|966424|++scope+:without_friends_v3,+->+{+where("id+NOT+IN+(SELECT+person_id+FROM+contacts)")+}|966425|这得到了相同的结果，但平均每次调用425毫秒-几乎一半的时间...|966426|现在，您可能需要在其他类似的查询中使用DISTINCT+-但对于我的情况，这似乎很好用。|966427|谢谢你的帮忙|966428|entityMap^0|0|0|0|0|9|J|0|A|J|0|F|D|10|8|19|6|1W|6|23|A|0|0|0|J|8|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|16|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|17|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|18|8|@$M|19|N|1A|O|P]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1B|8|@$M|1C|N|1D|O|P]]|9|@]|A|$]]|$1|S|3|T|5|6|7|1E|8|@$M|1F|N|1G|O|P]|$M|1H|N|1I|O|P]|$M|1J|N|1K|O|P]|$M|1L|N|1M|O|P]|$M|1N|N|1O|O|P]]|9|@]|A|$]]|$1|U|3|V|5|F|7|1P|8|@]|9|@]|A|$G|H]]|$1|W|3|X|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1R|8|@$M|1S|N|1T|O|P]]|9|@]|A|$]]|$1|10|3|11|5|6|7|1U|8|@]|9|@]|A|$]]|$1|12|3|-4|5|6|7|1V|8|@]|9|@]|A|$]]]|13|$]]

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

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

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

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

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

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

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

<pre><code> scope :without_friends_v3, -&gt; { where("id NOT IN (SELECT person_id FROM contacts)") }
</code></pre>

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

Now you might need the <code>DISTINCT</code> in other similar queries - but for my case this seems to work fine.

Thanks for your help

blocks|key|750971|text|更新4-+Rails+6.1|type|unstyled|depth|inlineStyleRanges|entityRanges|data|750972|感谢Tim+Park指出，在即将到来的6.1中，您可以这样做：|offset|length|750973|Person.where.missing(:contacts)|code-block|syntax|javascript|750974|多亏了他链接到的the+post。|750975|更新3-+Rails+5|750976|感谢@Anson出色的Rails+5解决方案(下面给他一些%2B1的答案)，你可以使用left_outer_joins来避免加载关联：|style|CODE|750977|Person.left_outer_joins(:contacts).where(contacts:+{+id:+nil+})|750978|我在这里包含了它，这样人们就会找到它，但他应该为此得到%2B1。很棒的新增功能！|750979|更新2|750980|有人问到了相反的问题，没有人的朋友。正如我在下面评论的那样，这实际上让我意识到最后一个字段(上面：:person_id)实际上不必与您返回的模型相关，它只需要是连接表中的一个字段。它们都将是nil，所以可以是其中的任何一个。这为上述问题提供了更简单的解决方案：|750981|Person.includes(:contacts).where(contacts:+{+id:+nil+})|750982|然后切换到返回没有人的朋友变得更加简单，您只需更改前面的类：|750983|Friend.includes(:contacts).where(contacts:+{+id:+nil+})|750984|更新|750985|在评论中有一个关于has_one的问题，所以只需要更新。这里的诀窍是，includes()需要关联的名称，而where需要的是表的名称。对于has_one，关联通常以单数形式表示，因此会发生变化，但where()部分保持不变。因此，如果仅使用Person+has_one+:contact，则语句将为：|750986|Person.includes(:contact).where(contacts:+{+person_id:+nil+})|750987|原创|750988|更好的：|750989|Person.includes(:friends).where(friends:+{+person_id:+nil+})|750990|对于hmt来说，这基本上是一样的，你依赖于一个没有朋友的人也将没有联系人的事实：|750991|Person.includes(:contacts).where(contacts:+{+person_id:+nil+})|750992|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/users/5370668/tim-park|1|https://blog.saeloun.com/2020/01/21/rails-6-1-adds-query-method-missing-to-find-orphan-records^0|0|2|8|0|0|0|8|8|1|0|0|15|G|0|0|0|0|1D|A|2N|3|0|0|0|0|0|9|7|Z|A|1I|5|1Y|7|2R|7|3D|6|3K|G|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1X|8|@]|9|@$D|1Y|E|1Z|1|20]]|A|$]]|$1|F|3|G|5|H|7|21|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|22|8|@]|9|@$D|23|E|24|1|25]]|A|$]]|$1|M|3|N|5|6|7|26|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|27|8|@$D|28|E|29|Q|R]]|9|@]|A|$]]|$1|S|3|T|5|H|7|2A|8|@]|9|@]|A|$I|J]]|$1|U|3|V|5|6|7|2B|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|2C|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|2D|8|@$D|2E|E|2F|Q|R]|$D|2G|E|2H|Q|R]]|9|@]|A|$]]|$1|10|3|11|5|H|7|2I|8|@]|9|@]|A|$I|J]]|$1|12|3|13|5|6|7|2J|8|@]|9|@]|A|$]]|$1|14|3|15|5|H|7|2K|8|@]|9|@]|A|$I|J]]|$1|16|3|17|5|6|7|2L|8|@]|9|@]|A|$]]|$1|18|3|19|5|6|7|2M|8|@$D|2N|E|2O|Q|R]|$D|2P|E|2Q|Q|R]|$D|2R|E|2S|Q|R]|$D|2T|E|2U|Q|R]|$D|2V|E|2W|Q|R]|$D|2X|E|2Y|Q|R]|$D|2Z|E|30|Q|R]]|9|@]|A|$]]|$1|1A|3|1B|5|H|7|31|8|@]|9|@]|A|$I|J]]|$1|1C|3|1D|5|6|7|32|8|@]|9|@]|A|$]]|$1|1E|3|1F|5|6|7|33|8|@]|9|@]|A|$]]|$1|1G|3|1H|5|H|7|34|8|@]|9|@]|A|$I|J]]|$1|1I|3|1J|5|6|7|35|8|@]|9|@]|A|$]]|$1|1K|3|1L|5|H|7|36|8|@]|9|@]|A|$I|J]]|$1|1M|3|-4|5|6|7|37|8|@]|9|@]|A|$]]]|1N|$1O|$5|1P|1Q|1R|A|$1S|1T]]|1U|$5|1P|1Q|1R|A|$1S|1V]]]]

<h2>Update 4 - Rails 6.1</h2>
Thanks to <a href="https://stackoverflow.com/users/5370668/tim-park">Tim Park</a> for pointing out that in the upcoming 6.1 you can do this:
<pre class="lang-rb prettyprint-override"><code>Person.where.missing(:contacts)
</code></pre>
Thanks to <a href="https://blog.saeloun.com/2020/01/21/rails-6-1-adds-query-method-missing-to-find-orphan-records" rel="noreferrer">the post</a> he linked to too.
<h2>Update 3 - Rails 5</h2>
Thanks to @Anson for the excellent Rails 5 solution (give him some +1s for his answer below), you can use <code>left_outer_joins</code> to avoid loading the association:
<pre><code>Person.left_outer_joins(:contacts).where(contacts: { id: nil })
</code></pre>
I've included it here so people will find it, but he deserves the +1s for this. Great addition!
<h2>Update 2</h2>
Someone asked about the inverse, friends with no people. As I commented below, this actually made me realize that the last field (above: the <code>:person_id</code>) 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 <code>nil</code> so it can be any of them. This leads to a simpler solution to the above:
<pre class="lang-rb prettyprint-override"><code>Person.includes(:contacts).where(contacts: { id: nil })
</code></pre>
And then switching this to return the friends with no people becomes even simpler, you change only the class at the front:
<pre class="lang-rb prettyprint-override"><code>Friend.includes(:contacts).where(contacts: { id: nil })
</code></pre>
<h2>Update</h2>
Got a question about <code>has_one</code> in the comments, so just updating. The trick here is that <code>includes()</code> expects the name of the association but the <code>where</code> expects the name of the table. For a <code>has_one</code> the association will generally be expressed in the singular, so that changes, but the <code>where()</code> part stays as it is. So if a <code>Person</code> only <code>has_one :contact</code> then your statement would be:
<pre class="lang-rb prettyprint-override"><code>Person.includes(:contact).where(contacts: { person_id: nil })
</code></pre>
<h2>Original</h2>
Better:
<pre class="lang-rb prettyprint-override"><code>Person.includes(:friends).where(friends: { person_id: nil })
</code></pre>
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:
<pre class="lang-rb prettyprint-override"><code>Person.includes(:contacts).where(contacts: { person_id: nil })
</code></pre>

blocks|key|966526|text|NOT+EXISTS相关子查询应该很快，特别是在行数和子记录与父记录的比率增加的情况下。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|966527|scope+:without_friends,+where("NOT+EXISTS+(SELECT+null+FROM+contacts+where+contacts.person_id+=+people.id)")|code-block|syntax|javascript|966528|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>scope :without_friends, where("NOT EXISTS (SELECT null FROM contacts where contacts.person_id = people.id)")
</code></pre>

blocks|key|751060|text|没有朋友的人|type|unstyled|depth|inlineStyleRanges|entityRanges|data|751061|Person.includes(:friends).where("friends.person_id+IS+NULL")|code-block|syntax|javascript|751062|或者至少有一个朋友|751063|Person.includes(:friends).where("friends.person_id+IS+NOT+NULL")|751064|您可以通过在Friend上设置作用域来使用Arel完成此操作|offset|length|style|CODE|751065|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|751066|然后，至少有一个朋友的人：|751067|Person.includes(:friends).merge(Friend.to_somebody)|751068|没有朋友的人：|751069|Person.includes(:friends).merge(Friend.to_nobody)|751070|entityMap^0|0|0|0|0|6|6|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|14|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|15|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|16|8|@$M|17|N|18|O|P]]|9|@]|A|$]]|$1|Q|3|R|5|D|7|19|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|1A|8|@]|9|@]|A|$]]|$1|U|3|V|5|D|7|1B|8|@]|9|@]|A|$E|F]]|$1|W|3|X|5|6|7|1C|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|D|7|1D|8|@]|9|@]|A|$E|F]]|$1|10|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]]|11|$]]

Persons that have no friends

<pre><code>Person.includes(:friends).where("friends.person_id IS NULL")
</code></pre>

Or that have at least one friend

<pre><code>Person.includes(:friends).where("friends.person_id IS NOT NULL")
</code></pre>

You can do this with Arel by setting up scopes on <code>Friend</code> 

<pre><code>class Friend
 belongs_to :person

 scope :to_somebody, -&gt;{ where arel_table[:person_id].not_eq(nil) }
 scope :to_nobody, -&gt;{ where arel_table[:person_id].eq(nil) }
end
</code></pre>

And then, Persons who have at least one friend:

<pre><code>Person.includes(:friends).merge(Friend.to_somebody)
</code></pre>

The friendless:

<pre><code>Person.includes(:friends).merge(Friend.to_nobody)
</code></pre>

blocks|key|751074|text|另外，例如，通过一个朋友过滤掉：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|751075|Friend.where.not(id:+other_friend.friends.pluck(:id))|code-block|syntax|javascript|751076|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Also, to filter out by one friend for instance:

<pre><code>Friend.where.not(id: other_friend.friends.pluck(:id))
</code></pre>

blocks|key|751109|text|以下是使用子查询的选项：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|751110|#+Scenario+#1+-+person+<->+friend
people+=+Person.where.not(id:+Friend.select(:person_id))

#+Scenario+#2+-+person+<->+contact+<->+friend
people+=+Person.where.not(id:+Contact.select(:person_id))|code-block|syntax|javascript|751111|上述表达式应生成以下SQL：|751112|--+Scenario+#1+-+person+<->+friend
SELECT+people.*
FROM+people+
WHERE+people.id+NOT+IN+(
++SELECT+friends.person_id
++FROM+friends
)

--+Scenario+#2+-+person+<->+contact+<->+friend
SELECT+people.*
FROM+people+
WHERE+people.id+NOT+IN+(
++SELECT+contacts.person_id
++FROM+contacts
)|751113|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Here is an option using a subquery:
<pre class="lang-rb prettyprint-override"><code># Scenario #1 - person &lt;-&gt; friend
people = Person.where.not(id: Friend.select(:person_id))

# Scenario #2 - person &lt;-&gt; contact &lt;-&gt; friend
people = Person.where.not(id: Contact.select(:person_id))
</code></pre>
The above expressions should generate the following SQL:
<pre class="lang-sql prettyprint-override"><code>-- Scenario #1 - person &lt;-&gt; friend
SELECT people.*
FROM people 
WHERE people.id NOT IN (
 SELECT friends.person_id
 FROM friends
)

-- Scenario #2 - person &lt;-&gt; contact &lt;-&gt; friend
SELECT people.*
FROM people 
WHERE people.id NOT IN (
 SELECT contacts.person_id
 FROM contacts
)
</code></pre>

Consider a simple association...

<pre><code>class Person
 has_many :friends
end

class Friend
 belongs_to :person
end
</code></pre>

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

And then what about a has_many :through version

<pre><code>class Person
 has_many :contacts
 has_many :friends, :through =&gt; :contacts, :uniq =&gt; true
end

class Friend
 has_many :contacts
 has_many :people, :through =&gt; :contacts, :uniq =&gt; true
end

class Contact
 belongs_to :friend
 belongs_to :person
end
</code></pre>

I really don't want to use counter_cache - and I from what I've read it doesn't work with 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

I don't mind the performance cost of the queries

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

Want to find records with no associated records in Rails

Ruby语言

考虑一个简单的关联..。class Person   has_many :friendsendclass Friend   belongs_to :personend要让所有在ARel和/或meta_where上没有朋友的人都加入进来，最干净的方法是什么？那么has_many :直通版本呢？class Person  ...

问我想在Rails中查找没有关联记录的记录
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问我想在Rails中查找没有关联记录的记录EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问我想在Rails中查找没有关联记录的记录
EN