ActiveRecord find_each combined with limit and order

by

The documentation says that find_each and find_in_batches don’t retain sort order and limit because:

  • Sorting ASC on the PK is used to make the batch ordering work.
  • Limit is used to control the batch sizes.

You could write your own version of this function like @rorra did. But you can get into trouble when mutating the objects. If for example you sort by created_at and save the object it might come up again in one of the next batches. Similarly you might skip objects because the order of results has changed when executing the query to get the next batch. Only use that solution with read only objects.

Now my primary concern was that I didn’t want to load 30000+ objects into memory at once. My concern was not the execution time of the query itself. Therefore I used a solution that executes the original query but only caches the ID’s. It then divides the array of ID’s into chunks and queries/creates the objects per chunk. This way you can safely mutate the objects because the sort order is kept in memory.

Here is a minimal example similar to what I did:

batch_size = 512
ids = Thing.order('created_at DESC').pluck(:id) # Replace .order(:created_at) with your own scope
ids.each_slice(batch_size) do |chunk|
    Thing.find(chunk, :order => "field(id, #{chunk.join(',')})").each do |thing|
      # Do things with thing
    end
end

The trade-offs to this solution are:

  • The complete query is executed to get the ID’s
  • An array of all the ID’s is kept in memory
  • Uses the MySQL specific FIELD() function

Hope this helps!

More Related Contents:

  1. ActiveRecord Arel OR condition
  2. Join multiple tables with active records
  3. PostgreSQL where all in array
  4. Rails 4 LIKE query – ActiveRecord adds quotes
  5. Find all records which have a count of an association greater than zero
  6. Rails find record with zero has_many records associated [duplicate]
  7. Rails raw SQL example
  8. How to use ANY instead of IN in a WHERE clause?
  9. PostgreSQL GROUP BY different from MySQL?
  10. MySQL not using indexes with WHERE IN clause?
  11. Best way to count rows by arbitrary time intervals
  12. Disable Rails SQL logging in console
  13. LEFT OUTER JOIN in Rails 4
  14. Rails 3 execute custom sql query without a model
  15. Perform this hours of operation query in PostgreSQL
  16. SQL where joined set must contain all values but may contain more
  17. Find records where join doesn’t exist
  18. Prepared Statement on Postgresql in Rails
  19. Postgresql query between date ranges
  20. Cannot Resolve Collation Conflict
  21. Relational Data Model for Double-Entry Accounting
  22. How to set a default row for a query that returns no rows?
  23. Do conditional INSERT with SQL?
  24. How to get Previous Value for Null Values
  25. Is ActiveRecord’s “order” method vulnerable to SQL injection?
  26. CONCAT_WS() for SQL Server
  27. SQL SET DEFAULT not working in MS Access
  28. Changing the CommandTimeout in SQL Management studio
  29. How do you read XML column in SQL Server 2008?
  30. Access substitute for EXCEPT clause

Leave a Comment