Distinct Join Rails

Question

Video has many Events

I am trying to get all Videos that have Events scheduled in the future.

I have this which was already there:

named_scope :scheduled_in_future, :joins => :event, :conditions => ["event.scheduled_start > ? AND event.status = ?", Time.now.to_i, 'PENDING']

This works, but if the same Video has multiple events in the future it will give me duplicate Video records. Sure I can go through the array and weed out the duplicates, but there has to be a SQL way to do it.

I tried adding in a

:select => "DISTINCT(video.id)"

but it only returns the ID field instead of the whole record.

Answer 1

Try using :include instead of :joins and you should not see any more duplicate video results.

Also, you should be using a lambda in your named_scope, otherwise Time.now will be cached the first time you use it and you'll start getting incorrect results.

named_scope :scheduled_in_future, lambda {
  { 
    :include => :events,
    :conditions => ["event.scheduled_start > ? AND event.status = ?", Time.now.to_i, 'PENDING']
  }
}

Answer 2

Have you tried grouping by the video id?