ActiveRecord Find - Skipping Records or Getting Every Nth Record

Question

I'd like to do a query where I select a bunch of data, but I'd like to be able to then decrease the resolution of that data by only selecting, say, every third record, or maybe even every hundredth record, or whatever.

Is there any straightforward way to do this with ActiveRecord?

Answer 1

If your Model has an ascending row like id without missing any number you can do something like this:

Model.all(:condition => "models.id%3=0")

If not you can first fetch all rows from the database and then you can do this:

models = Model.all
third_models = models.reject{ |model| models.index(model) % 3 != 0 }

Answer 2

In Oracle i would write that as follows:

YourModel.find(:conditions => 'MOD(ROWNUM,3) = 0') 

this has the advantage that the filter happens at the database, so not everything is retrieved.

In PostgreSQL this is called ROW_NUMBER (actually that is the SQL-standard). In MySQL this is not supported.

In mysql you can mimic rownum using SELECT @rownum:=@rownum+1 rownum, t.* FROM (SELECT @rownum:=0) r, mytable t;. So i guess something like

Bar.find_by_sql("select * from (SELECT @rownum:=@rownum+1 rownum, t.* FROM (SELECT @rownum:=0) r, mytable t) where mod(rownum,3) = 0") 

should work.