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}

Answer 2

If you want to create classes (models) that are not backing to a database you need to not state that they are inheriting from ActiveRecord.

Do

class RecentActivity

Instead of

class RecentActivity < ActiveRecord::Base

That being said, it still might be helpful to get some more information on what you are trying to do: for example if you want to have something like "comments" or "pictures" on multiple different things like an article, or someones profile or blogpost all within the same application, I'd recommend checking out PolyMorphic Associations.