Return every nth row from database using ActiveRecord in rails

Question

Ruby 1.9.2 / rails 3.1 / deploy onto heroku --> posgresql

Hi, Once a number of rows relating to an object goes over a certain amount, I wish to pull back every nth row instead. It's simply because the rows are used (in part) to display data for graphing, so once the number of rows returned goes above say 20, it's good to return every second one, and so forth.

This question seemed to point in the right direction: ActiveRecord Find - Skipping Records or Getting Every Nth Record

Doing a mod on row number makes sense, but using basically:

@widgetstats = self.widgetstats.find(:all,:conditions => 'MOD(ROW_NUMBER(),3) = 0 ')

doesn't work, it returns an error:

PGError: ERROR:  window function call requires an OVER clause

And any attempt to solve that with e.g. basing my OVER clause syntax on things I see in the answer on this question:

Row numbering in PostgreSQL

ends in syntax errors and I can't get a result.

Am I missing a more obvious way of efficiently returning every nth task or if I'm on the right track any pointers on the way to go? Obviously returning all the data and fixing it in rails afterwards is possible, but terribly inefficient.

Thank you!

Carl Zulauf · Accepted Answer · 2011-11-24 00:48:22Z

8

I think you are looking for a query like this one:

SELECT * FROM (SELECT widgetstats.*, row_number() OVER () AS rownum FROM widgetstats ORDER BY id) stats WHERE mod(rownum,3) = 0

This is difficult to build using ActiveRecord, so you might be forced to do something like:

@widgetstats = self.widgetstats.find_by_sql(
  %{
    SELECT * FROM
    (
      SELECT widgetstats.*, row_number() OVER () AS rownum FROM widgetstats ORDER BY id
    ) AS stats
    WHERE mod(rownum,3) = 0
  }
)

You'll obviously want to change the ordering used and add any WHERE clauses or other modifications to suit your needs.

edited Nov 24, 2011 at 0:48

answered Nov 24, 2011 at 0:38

Carl Zulauf

39.6k2 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Hinnegan · Accepted Answer · 2011-11-24 00:17:05Z

2

Were I to solve this, I would either just write the SQL myself, like the SQL that you linked to. You can do this with

my_model.connection.execute('...')

or just get the id numbers and find by id

ids = (1..30).step(2)
my_model.where(id => ids)

answered Nov 24, 2011 at 0:17

John Hinnegan

6,0022 gold badges51 silver badges65 bronze badges

Collectives™ on Stack Overflow

Return every nth row from database using ActiveRecord in rails

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related