ActiveRecord Find - skip entries or get every Nth entry

Question

I would like to make a query in which I select a bunch of data, but I would like to then reduce the resolution of this data by only selecting, say, every third record, or perhaps even every hundredth record or something else.

Is there an easy way to do this with ActiveRecord?

Answer 1

In Oracle, I would write the following:

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

this has the advantage that the filter occurs in the database, so not everything is retrieved.

In PostgreSQL, this is called ROW_NUMBER (which is actually an SQL standard). In MySQL, this is not supported.

In mysql, you can simulate rownum with SELECT @rownum: =@rownum +1 rownum, t.* FROM (SELECT @rownum:=0) r, mytable t; . So I think 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") 

must work.

Answer 2

If your model has an ascending line of type id without missing a single number, you can do something like this:

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

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

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