如何查找连接表中没有行的随机项目? [英] How to find a random item that has no rows in a join table?

问题描述

在我的 Rails 4 应用程序中，我有一个 Item 模型和一个 Flag 模型.项目 has_many 标志.标记 belong_to 项目.标志具有属性item_id、user_id 和reason.我需要一种方法来查找未标记的随机挂起项.我使用 enum 表示待处理状态.

In my Rails 4 app I have an Item model and a Flag model. Item has_many Flags. Flags belong_to Item. Flag has the attributes item_id, user_id, and reason. I need a way to find a random pending item that is not flagged. I am using enum for pending status.

我相信我可以找到标记为的随机待处理项目:

I believe I can find a random pending item that is flagged with:

@pending_item = Item.pending.joins(:flags).order("RANDOM()").first

但是我怎样才能找到一个没有标记的随机待处理项目?

but how can I find a random pending item that is not flagged?

推荐答案

Use where with not exists 得到 Item 没有 >Flags:

Use where with not exists to get Items without Flags:

@pending_item = Item.pending.
  where("not exists (select 1 from flags where flags.item_id = items.id)").
  order("RANDOM()").first

旁注:order("RANDOM()").first 如果有很多 Item 符合条件，则效率不高.对于大表，这可能会更有效:

Side note: order("RANDOM()").first is not efficient if there are many Items that meet the criteria. This will probably be more efficient for a large table:

unflagged_pending_items = Item.pending.
  where("not exists (select 1 from flags where flags.item_id = items.id)")
@pending_item = unflagged_pending_items.offset(rand unflagged_pending_items.count).first

或者，如果第一个查询不是太慢，并且您不需要每次都使用不同的随机项，您可以将结果缓存一段时间.

Alternatively, if the first query isn't too slow and you don't need a different random item every time, you could just cache the result for a while.

这篇关于如何查找连接表中没有行的随机项目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！