CODEWITHSUNDEEP
ruby-on-railsactiverecordruby-on-rails-4scopearel
Undistraction
Undistraction

Reputation: 43402

Ordering Records with Same Date Using Second Field

I have an application with many Galleries. Each gallery has a start_date DateTime field.

For legacy reasons, the time of all start_dates is midnight.

Thu, 10 Jul 2014 00:00:00 UTC +00:00

I need to order the galleries by date, so a user can shuffle back and forth across them using a 'older' and 'newer' link. Galleries are ordered based on start_date:

scope :start_date_ascending, -> { order(start_date: :asc) }
scope :start_date_descending, -> { order(start_date: :desc) }

My problem is that there is no clear older or newer gallery when there are multiple galleries with the same date. In such cases I cannot predict the order that Galleries with the same date are returned in, so moving across multiple galleries with the same date becomes random and error-prone.

I have scope set up to find newer and older galleries:

scope :newer_than, -> (gallery){ where.not(id:gallery).where('start_date >= :gallery_start_date', gallery_start_date:gallery.start_date) }
scope :older_than, -> (gallery){ where.not(id:gallery).where('start_date < :gallery_start_date', gallery_start_date:gallery.start_date) }

And I find the next and previous galleries like this:

def self.next_newer(gallery)
  Gallery.newer_than(gallery).start_date_ascending.limit(1).first
end

def self.next_older(gallery)
  Gallery.older_than(gallery).start_date_descending.limit(1).first
end

So it seems to me I need a secondary way to order the galleries that are the same date. It doesn't matter what order that is - it could simply be their ids.

How can I handle this situation so that galleries with the same date appear in the query in a predictable, fixed order so that next_newer and next_older move through them?

Upvotes: 1

Views: 74

Answers (2)

Benjamin Bouchet
Benjamin Bouchet

Reputation: 13181

Perhaps you can sort with a second criteria, name for example and if available, or even id 

scope :start_date_ascending, -> { order(start_date: :asc, name: :asc) }
scope :start_date_descending, -> { order(start_date: :desc, name: :asc) }

note on the start_date_descending scope it can be nice to keep name asc, so despite of the descending date order we keep a alphabetical order

And for the next and previous gallery, if you can store an array, you can get your ordered ids and iterate through them

ids = Gallery.start_date_ascending.pluck :id

Upvotes: 1

Undistraction
Undistraction

Reputation: 43402

Based on @BengaminSinclaire's suggestion:

  def self.next_newer(gallery)
    ascending_ids = Gallery.start_date_ascending.pluck :id
    current_index = ascending_ids.index(gallery.id)
    Gallery.find(ascending_ids[current_index+1]) if current_index < ascending_ids.length - 1
  end

  def self.next_older(gallery)
    ascending_ids = Gallery.start_date_ascending.pluck :id
    current_index = ascending_ids.index(gallery.id)
    Gallery.find(ascending_ids[current_index-1]) if current_index > 0
  end

Upvotes: 1

Related Questions