How can I get records 'around' a certain record in Ruby on Rails?

I have a model called note. I currently get notes like this:

current_user.notes.order('date DESC, created_at DESC').limit(10).reverse!

I now need to do the following: Take a note_id parameter, and return 10 notes: the one from the parameter and 9 other ones 'around' the first.

Ideally, the 9 other notes will be allocated as 5 before the primary note and 4 after (or 4 before & 5 after), based on the ordering in the first line above.

Example:

note note note note 开发者_JAVA百科primary_note note note note note note

Sometimes this will not be possible. For example if the primary_note is the users second note, it should return this:

note primary_note note note note note note note note note

Or if it's the latest note, and the user only has 3 notes total, it should return:

note note primary_note

How can I do this? I'm using ruby 1.9.2 & Rails 3.0.1

I don't think there is a way of obtaining those notes with a single query - you will need at least two. From my (somewhat limited) understanding of SQL, the only way you have to do that is:

• Obtain the 4 notes that come "before" primary_note
• Obtain the 4 notes "after" primary_note
• Joining the two sets together

Something similar to this (warning: untested code)

class Note < ActiveRecord::Base
  # on rails 3 I prefer class methods to named scopes.
  # they are equally "chainable" and have simpler syntax
  def self.notes_before(date, limit=4)
    self.where(['date >= ?', date]).order("date DESC, created_at DESC").limit(limit)
  end

  def self.notes_after(date, limit=4)
    self.where(['date <= ?', date]).order("created_at ASC, date ASC").limit(limit)
  end

  # Instance method
  def notes_around(limit=4)
    (Note.notes_before(self.date, limit) << self) + Note.notes_after(self.date, limit)
  end
end

Usage:

n = Note.new(:date => Date.yesterday)
n.notes_around # returns the 9 notes around n, plus n
n.notes_around(40) # returns 81 notes

# in addition:
Note.notes_before(Date.today, 40) # last 40 notes
Note.notes_after(Date.today, 10).where(:color => 'blue') # chainable

But I could not think of a "chainable class method". Maybe I don't know enough SQL.

Ideally, the 9 other notes will be allocated as 5 before the primary note and 4 after (or 4 before & 5 after), based on the ordering in the first line above

I don't understand how you determine that you need "4" or "5" on this context. So I'm assuming that you know that.

You can use a range condition in your query

class Note
  scope :around_note_id, lambda { |id| where(:id => range_for_search(id) }

  def self.range_for_search(id)
    # your logic here, i.e.
    # (id-1)..(id+1)
  end
end

> Note.around_note_id(3)