CODEWITHSUNDEEP
ruby-on-railsrubyrails-activerecord
user4584963
user4584963

Reputation: 2533

How to find a random item that has no rows in a join table?

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?

Upvotes: 1

Views: 69

Answers (3)

Dave Schweisguth
Dave Schweisguth

Reputation: 37627

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

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.

Upvotes: 1

Hieu Pham
Hieu Pham

Reputation: 6692

Solution #1

Item.pending.joins("LEFT OUTER JOIN ( SELECT id, item_id
                                      FROM flags
                                    ) AS temp
                                    ON temp.item_id = items.id")
            .where('temp.id = null')
            .order("RANDOM()")
            .first

So if we use LEFT OUTER JOIN, temp.id will equal null if that item is not flagged.

Solution #2

Item.pending.where("id NOT IN ( SELECT item_id FROM flags )")
            .order("RAMDOM()")
            .first

This is clear, we find an item which wasn't flagged.

Upvotes: 0

guitarman
guitarman

Reputation: 3310

I don't know, wether it's better to use a sub-select or not. I prefer to use ids and foreign keys where I can use it.

# initialize my instance variable to have it in the view always
@pending_item = nil

# get an array with all flagged item ids
flagged_item_ids = Flag.group(:item_id).pluck(:item_id)

# search for an unflagged item, if there are item ids in the array only, 
# because the statement could return a flagged item with an empty array in the condition  
if flagged_item_ids > 0
  @pending_item = Item.pending.where.not(id: flagged_item_ids).order("RANDOM()").first
end

@pending_item
# => an unflagged random item or nil

Keep in mind that the array flagged_item_ids could hold a huge amount of flagged item ids. This can consume a lot of memory.

Upvotes: 1

Related Questions