Activerecord Find - Skipping Records or Getting Every Nth Record

ActiveRecord Find - Skipping Records or Getting Every Nth Record

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.

Return every nth row from database using ActiveRecord in rails

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.

ActiveRecord query for all of last day's data and every 100th record prior

You can use OR query:

Datum.where("created_at>?", 1.day.ago).or(Datum.where("id%100=0"))

Rails Active Record - Get an evenly distributed arbitrary number of records

If you're on postgresql, mssql or oracle, you can use the row_number() function but it looks like you'll need two nested queries, which might be more complicated than you want. See Return row of every n'th record for an example of how to construct it.

From there, what you're trying to do might look like:

MyModel.find_by_sql("
   select * from my_models where id in (
     SELECT t.id
     FROM
     (
         SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rownum
         FROM my_models
         WHERE [YOUR CONDITIONS HERE]
     ) AS t
     WHERE t.rownum % n = 0
     ORDER BY t.key
   )")

It's really hard to tell from here how expensive that query might be. If I might offer some advice, I would really suggest proving that you have a performance problem doing it the Rails way, before getting into trying an optimization like this.

You can avoid having to fetch and deserialize all the records you'd skip, by using the ids method of an AREL query object to get only the ids like so:

class MyModel < ActiveRecord::Base
  class < self
    def get_each_n_of_query(n)
      all_ids = get_query.ids
      ids = (0... all_ids.length).select{ |x| x%n == n-1 }.map { |y| all_ids[y] }
      where(id: ids)
    end

    def get_query()
      where(foo: 'bar', ...)
    end
  end
end

Credit to the answers at How do you select every nth item in an array? for how to divide the list of ids.

Return row of every n'th record

This is where ROW_NUMBER can help. It requires an order-by clause but this is okay because an order-by is present (and required to guarantee a particular order).

SELECT t.id, t.key
FROM
(
    SELECT id, key, ROW_NUMBER() OVER (ORDER BY key) AS rownum
    FROM datatable
) AS t
WHERE t.rownum % 30 = 0    -- or % 40 etc
ORDER BY t.key

ActiveRecord - get the last 20 records of a where

As per the SQL query in the update of your question, you can use from to pass a subquery and build something like that:

Instrument
  .from(Instrument.where('id < ?', 151_000).order(id: :desc).limit(10),
        :instruments)
  .order(:id)

Whats the best way to get the nth to last record?

What you're looking for is a combination of LIMIT, OFFSET, and ensuring that the query is using ORDER BY as a constraint. This is described on the PostgreSQL - Queries: Limit and Offset documentation page.

An example is:

irb> SomeModel.count
=> 35

irb> SomeModel.order(:id).reverse_order.limit(1).offset(4)
# SomeModel Load (0.7ms)  SELECT "some_models".* FROM "some_models" ORDER BY "some_models".id DESC LIMIT 1 OFFSET 4
=> #<ActiveRecord::Relation [#<SomeModel id: 31, name: "hello world", created_at: "2014-03-24 21:52:46", updated_at: "2014-03-24 21:52:46">]>

Which yields the same result (albeit as an AR::Relation) as:

irb> SomeModels.last(5).first
# SELECT "some_models".* FROM "some_models" ORDER BY "some_models"."id" DESC LIMIT 5
=> #<SomeModel id: 31, name: "hello world", created_at: "2014-03-24 21:52:46", updated_at: "2014-03-24 21:52:46">

The difference is that in the former query, PostgreSQL will treat the .reverse_order.limit.offset query in memory, and restrict the values appropriately before it returns the result. In the latter, PostgreSQL will return 5 results, and you restrict the values in Ruby by using #first.

ActiveRecord - Get the last n records and delete them in one command?

To do it in one SQL query use delete_all:

Model.order(created_at: :desc).limit(n).delete_all

But delete_all won't execute any model callbacks or validations

To run callbacks and validations use destroy_all:

Model.order(created_at: :desc).limit(n).destroy_all

Unfortunately destroy_all will execute n + 1 SQL queries: 1 query to retrieve records and n queries to delete each record.

Rails Active Record find(:all, :order = ) issue

I notice that in your first example, the simple :order => "date", record 7 is sorted before record 1. This order is also how you see the results in the multi-column sort, regardless of whether you sort by attending.

This would seem to make sense to me if the dates weren't exactly the same, and the date for 7 is before the date for 1. Instead of finding that the dates are exactly equal then proceeding to sort by attending, the query finds that the dates are not equal and simply sorts by that like all the other records.

I see from browsing around that SQLite doesn't have a native understanding of DATE or DATETIME data types and instead gives users the choice of floating point numbers or text that they must parse themselves. Is it possible that the literal representation of the dates in the database are not exactly equal? Most people seem to need to use date functions so that dates behave like you would expect. Perhaps there's a way to wrap your order by column with a date function that will give you something concrete to compare, like date(date) ASC, attending DESC. I'm not sure that syntax works, but it's an area to look at for solving your problem. Hope that helps.

---

Related Topics