Custom SQL query without Corresponding Table

Question

I have a SQL query that isn't table-specific and I don't know how to handle it with Ruby On Rails.

Here my SQL query (you don't need to understand it):

SELECT type, actor_id, events.created_at, photo_id, photos.user_id FROM 
(SELECT 'comment' AS type, user_id AS actor_id, created_at, photo_id FROM comments
UNION
SELECT 'classification' AS type, user_id AS actor_id, created_at, photo_id FROM classifications) 
AS events
INNER JOIN photos ON photo_id = photos.id
WHERE user_id = #{@user.id}
ORDER BY created_at DESC
LIMIT 9

I tried to create a model and use a find_by_sql:

class RecentActivity ActiveRecord::Base
  attr_accessor :type, :actor_id, :created_at, :photo_id, :user_id
end

I get:

Mysql::Error: Table 'mysite_development.recent_activities' doesn't exist: SHOW FIELDS FROM `recent_activities`

How can I avoid this message? Is there any alternative solution?

Answer 1

You can grab a db connection directly from ActiveRecord::Base, but it's not as useful as extending AR::Base, because helpful methods like sanitize_sql are protected.

class ComplexQueries < ActiveRecord::Base
  def self.my_query
    # Notice how you can, and should, still sanitize params here. 
    self.connection.execute(sanitize_sql(["select * from foo limit ?", 10]))
  end
end

results = ComplexQueries.my_query
results.each_hash{|h| puts h.inspect}