Rails - batch sql query - Primary key not included in the custom select clause

Question

I have a legacy database with multiple existing tables, I want to query thousands of records in one legacy table that doesn't have primary key

   LegacyTable.where(condition, start_date, end_date).find_each do |record|
      yield record
    end

but it will throw the bellow error:

ORA-01741: illegal zero-length identifier

because it auto generates order by "LegacyTable"."" according to the api

NOTE: It's not possible to set the order. That is automatically set to ascending on the primary key (“id ASC”) to make the batch ordering work. This also means that this method only works with integer-based primary keys.

Then I set self.primary_key = 'TRANSACTION_ID' in the model, but TRANSACTION_ID is just an index in legacy table. Then it throws another exception:

RuntimeError (Primary key not included in the custom select clause):

How to do batch query in rails? If use Java, I can just add fetchSize parameter and don't need primary key in table.

Answer 1

ActiveRecord's find_in_batches (which find_each based on) is relying on primary key ordering to perform batches extraction in multiple queries (details). As @aliibrahim have already mentioned, you have to implement pagination on your own.

While I'm aware the question is about Oracle, for others coming from Google it worths mentioning, there is a great solution to the problem for those using PostgreSQL: postgresql_cursor gem.

PostgreSQL Cursor is an extension to the ActiveRecord PostgreSQLAdapter for very large result sets. It provides a cursor open/fetch/close interface to access data without loading all rows into memory, and instead loads the result rows in "chunks" (default of 1_000 rows), buffers them, and returns the rows one at a time.