How can I do something like find_in_batches_by_sql in Rails

Question

find_in_batches does not allow pure SQL (so far as I can see).
find_by_sql does not have batch support (so far as I can see).

So how can I do something like find_in_batches_by_sql?

The SQL is nasty programatically generated stuff and it points to 3rd party databases and the result sets can have hundred-thousands to millions of records returned.

Are there other cursor tricks with ActiveRecord I should look into?

Thanks.

Answer 1

Updated to use the correct order for LIMIT and OFFSET

You could always break the SQL into parts and do something like

Model.select("*").where("WHERE CLAUSE HERE").joins("JOIN CLAUSES HERE").find_in_batches {...}

Or if you needed really fun SQL stuff you could just use an offset and limit and loop until you exhausted the results. Here's the basic idea:

offset = 0
limit = 1000

while(results)
  results = Model.find_by_sql("<your SQL here> LIMIT #{limit} OFFSET #{offset}")
  offset += limit  
  # Do stuff here
end

Answer 2

Note offset should after limit

offset = 0
limit = 1000

while(results)
  results = Model.find_by_sql("<your SQL here>  LIMIT #{limit} OFFSET #{offset}")
  offset += limit  
  # Do stuff here
end