How to coerce type of ActiveRecord attribute returned by :select phrase on joined table?

Question

Having trouble with AR 2.3.5, e.g.:

users = User.all( :select => "u.id, c.user_id", :from => "users u, connections c",
       :conditions => ... )

Returns, e.g.:

=> [#<User id: 1000>]
>> users.first.attributes
=> {"id"=>1000, "user_id"=>"1000"}

Note that AR returns the id of the model searched as numeric but the selected user_id of the joined model as a String, although both are int(11) in the database schema.

How could I better form this type of query to select columns of tables backing multiple models and retrieving their natural type rather than String ? Seems like AR is punting on this somewhere. How could I coerce the returned types at AR load time and not have to tack .to_i (etc.) onto every post-hoc access?

Answer 1

It's unfortunately not going to happen very easily. All of the data from the DB connection comes to rails as strings, the conversion of types happens in each of the dynamic attribute methods that rails creates at runtime. It knows which attributes to convert to which type by the table's column-type meta-data that it retrieves when the app starts. Each model only has column meta-data for it's own columns, that's why it's own columns end up with correct type. There is no easy way to auto-convert to the correct types.

You could on the other hand, create a simple conversion method that would take a Hash and automatically convert the attributes.

Something like this:

users = User.all(:select => "cl, comments.c2", ...)
users = convert_columns(users, 'c2' => :integer, 'other_column' => :date)

def convert_columns(records, columns = {})
  records.each do |rec|
    columns.each do |col, type|
      rec[col] = case type
         when :int then rec[col].to_i
         when :date then ........
         ....
      end
    end
  end
end

Answer 2

Why are you using :from => "users" inside a User.method ? The following will do an inner join (which is what you are doing anyways)

users = User.all(:include => :connections, :select => "users.id, connections.user_id", :conditions => {...})

This is going to be very heavy query for the database. Faster query would be with the outer join though.

This will also return the keys as INT not STRING

A much faster alternative was

Connection.all(:include => :user, :conditions => {...}).collect {|e| [e.user_id, e.id] }

This gives you an array of arrays with the ids. If you are going to select "id, user_id" columns only, then it may not necessarily be as AR object. An array can be faster.

I hope I am not missing some point here. Suggest me, if I am.