ActiveRecord find_each and Postgres

Question

I get the following error:

PGError: ERROR: operator does not exist: character varying >= integer LINE 1: ...CT "games".* FROM "games" WHERE ("games"."uuid" >= 0) ORDE... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. : SELECT "games".* FROM "games" WHERE ("games"."uuid" >= 0) ORDER BY "games"."uuid" ASC LIMIT 1000 

when i try to do this:

 Game.find_each do |game| # ... end 

I have a primary string key (UUID) for my model:

 class Game < ActiveRecord::Base self.primary_key = 'uuid' before_create do |game| game.uuid = UUIDTools::UUID.timestamp_create().to_s if game.uuid.blank? end end 

I don’t know why ActiveRecord places a WHERE in this WHERE , but it is completely unnecessary and the reason is for a type error (since it is a string column, not an integer).

So how can I avoid this? Is this something I have to put in my model definition? Or should I avoid find_each and use a different method? This is a rake task that just goes through all the records and looks for additional information ...

Answer 1

It seems that find_each has an error with non-numeric primary keys:

https://groups.google.com/group/compositekeys/browse_frm/month/2011-06

Answer 2

This blog post has a fix for your error:

in lib / clean_find_in_batches.rb

 module CleanFindInBatches def self.included(base) base.class_eval do alias :old_find_in_batches :find_in_batches alias :find_in_batches :replacement_find_in_batches end end # Override due to implementation of regular find_in_batches # conflicting using UUIDs def replacement_find_in_batches(options = {}, &block) relation = self return old_find_in_batches(options, &block) if relation.primary_key.is_a?(Arel::Attributes::Integer) # Throw errors like the real thing if (finder_options = options.except(:batch_size)).present? raise "You can't specify an order, it forced to be #{batch_order}" if options[:order].present? raise "You can't specify a limit, it forced to be the batch_size" if options[:limit].present? raise 'You can\'t specify start, it\ forced to be 0 because the ID is a string' if options.delete(:start) relation = apply_finder_options(finder_options) end # Compute the batch size batch_size = options.delete(:batch_size) || 1000 offset = 0 # Get the relation and keep going over it until there nothing left relation = relation.except(:order).order(batch_order).limit(batch_size) while (results = relation.offset(offset).limit(batch_size).all).any? block.call results offset += batch_size end nil end end 

and in config / initializers / clean_find_in_batches.rb

 ActiveRecord::Batches.send(:include, CleanFindInBatches) 

Answer 3

I think that find_each without any parameters will result in find_by_id, where id> = 0. Although ActiveRecord uses the correct column, in your case it does not seem to know that the column is of type varchar, instead of this integer.

You can try using a different search method, or maybe try adding some conditions to find_each.

This may matter for the problem of using the string as the primary key: http://railsforum.com/viewtopic.php?id=11110

Greetings