CODEWITHSUNDEEP
ruby-on-railsactiverecord
collimarco
collimarco

Reputation: 35389

Custom SQL query without Corresponding Table

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?

Upvotes: 17

Views: 11967

Answers (3)

sschmeck
sschmeck

Reputation: 7685

You can also grab the ActiveRecord connection and call select_all:

ActiveRecord::Base.connection.select_all('select * from foo').each do |e|
  puts e.inspect
end

Upvotes: 7

Mike Buckbee
Mike Buckbee

Reputation: 6983

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.

Upvotes: 0

jdl
jdl

Reputation: 17790

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}

Upvotes: 38

Related Questions