Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 707
collimarco Avatar asked Aug 16 '09 18:08

collimarco


People also ask

How do I create a custom SQL query?

After connecting to your data, double-click the New Custom SQL option on the Data Source page. Type or paste the query into the text box. The query must be a single SELECT* statement.

How do you fetch records that are present in one table but not in another table?

How to Select All Records from One Table That Do Not Exist in Another Table in SQL? We can get the records in one table that doesn't exist in another table by using NOT IN or NOT EXISTS with the subqueries including the other table in the subqueries.

What is a raw query in SQL?

Raw SQL, sometimes also called native SQL, is the most basic, most low-level form of database interaction. You tell the database what to do in the language of the database. Most developers should know basics of SQL. This means how to CREATE tables and views, how to SELECT and JOIN data, how to UPDATE and DELETE data.


1 Answers

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}
like image 161
jdl Avatar answered Oct 18 '22 03:10

jdl