Custom SQL query without Corresponding Table

activerecord, ruby-on-rails

Solution

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}

Problem

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?

Original source