How can I get ActiveRecord model instances from a raw sql query?

Question

In Rails, I sometimes want to do a query with MySQL that is too complicated for ActiveRecord methods alone. So I will usually do the following to get back an array of hashes:

query = "SELECT * FROM ..."

result = ActiveRecord::Base.connection.exec_query.to_a

Often times I can get away with just this, but sometimes I still want an array of instances for my model so I can use them for CRUD.

In that case, I've tried this:

result.map{|u| User.new(u)}

This works fine until I try to do a create, save, or update. When that happens, I get an error telling me that a record with the same ID already exists.

Somehow, ActiveRecord already knows how to do this with the queries it is able to construct. How can I do the same thing and make it so that I can transform a complex raw query into a collection of model instances that will alter the table instead of trying to insert and failing?

Answer 1

Use find_by_sql e.g.

User.find_by_sql("SELECT * FROM user...")

the trouble with doing result.map{|u| User.new(u)} is that this creates new User objects with the attributes taken from the hashes returned by exec_query but these are treated as new, unsaved Active Record models so clash with the existing records if you then try to persist them.

That said, the Active Record query interface is pretty flexible so it could be worth posting some examples of the queries you need to see if these can be modelled without resorting to raw SQL.