Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>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:</p> <ul> <li>Obtain the 4 notes that come "before" <code>primary_note</code></li> <li>Obtain the 4 notes "after" <code>primary_note</code></li> <li>Joining the two sets together</li> </ul> <p>Something similar to this (warning: untested code)</p> <pre><code>class Note &lt; 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 &gt;= ?', date]).order("date DESC, created_at DESC").limit(limit) end def self.notes_after(date, limit=4) self.where(['date &lt;= ?', date]).order("created_at ASC, date ASC").limit(limit) end # Instance method def notes_around(limit=4) (Note.notes_before(self.date, limit) &lt;&lt; self) + Note.notes_after(self.date, limit) end end </code></pre> <p>Usage:</p> <pre><code>n = Note.new(:date =&gt; 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 =&gt; 'blue') # chainable </code></pre> <p>But I could not think of a "chainable class method". Maybe I don't know enough SQL.</p> <blockquote> <p>Ideally, the 9 other notes will be allocated as 5 before the primary note and 4 after (or 4 before &amp; 5 after), based on the ordering in the first line above</p> </blockquote> <p>I don't understand how you determine that you need "4" or "5" on this context. So I'm assuming that you know that.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow can I get records 'around' a certain record in Ruby on Rails?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USkikito
    1. USkikito
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow can I get records 'around' a certain record in Ruby on Rails?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COYou could do it in one SQL query using a UNION but then you'd have to bypass some of the ActiveRecord niceties.
      singulars
      1. PO
      1. USmu is too short
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload