Return every nth row from the database using ActiveRecord in rails

Question

Ruby 1.9.2 / rails 3.1 / deploy to hero → posgresql

Hey. When the number of lines related to the object moves by a certain amount, I want to discard every nth line. This is simply because the rows are used (partially) to display data for graphical display, therefore, as soon as the number of rows returned exceeds 20, it is useful to return every second, etc.

This question seemed to point in the right direction: ActiveRecord Find - skip records or get every Nth record

Running a mod on a line number makes sense, but is mostly used:

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

does not work, it returns an error:

 PGError: ERROR: window function call requires an OVER clause 

And any attempt to solve this, for example, basing the syntax of the OVER clause on the things that I see in the answer to this question:

PostgreSQL line numbering

ends with syntax errors and I cannot get the result.

Am I missing a more obvious way to efficiently return every nth task, or if I am on the right path, any pointers on the path? Obviously, the return of all data and their fixation in rails is subsequently possible, but terribly inefficient.

Thanks!

Answer 1

I think you are looking for a query like this:

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

This is hard to create with ActiveRecord, so you might need 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 will obviously want to change the ordering used and add any WHERE clauses or other modifications to suit your needs.

Answer 2

If I solved this, I would just write SQL itself, like the SQL you are attached to. You can do it with

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

or just enter the identification numbers and find by id

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