没有相应的表自定义的SQL查询 [英] Custom SQL query without Corresponding Table

问题描述

我有一个SQL查询是不表特定的，我不知道如何使用Ruby on Rails的处理。

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

下面我的SQL查询（你不需要了解它）：

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

我试图创建一个模型，并使用的find_by_sql：

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

我得到：

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?

推荐答案

您可以直接从ActiveRecord的::基地抢数据库连接，但它不是为延长AR :: Base的是有用的，因为有用的方法，如sanitize_sql的保护。

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}

这篇关于没有相应的表自定义的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！