How can I do something like find_in_batches_by_sql in Rails

find_in_batches does not allow pure SQL (so fa开发者_如何学Pythonr 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.

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

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

There is a more convienent way than re-inventing the in_batches feature, like the other answers suggested. Let's consider some "complicated" SQL that I would not enjoy translating into ActiveRecord or Arel calls (mostly because I'm too lazy to learn Arel's syntax):

WITH cte AS (
    SELECT foo, bar FROM foo_table
)
SELECT id FROM users INNER JOIN cte ON users.foo = cte.foo

All you need to do is only select the ids and use that query as a subquery of your ActiveRecord relation, like so:

sql_query = "..."
User.where("id IN (#{sql_query})").in_batches.update_all("comment = 'I did it!'")

And voilà, you've got your ActiveRecord relation and now you can have fun with it!