ruby-on-rails - 没有对应表的自定义SQL查询

Question

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

这是我的SQL查询（您无需了解它）：

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 commentsUNIONSELECT 'classification' AS type, user_id AS actor_id, created_at, photo_id FROM classifications) AS eventsINNER JOIN photos ON photo_id = photos.idWHERE user_id = #{@user.id}ORDER BY created_at DESCLIMIT 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

我得到：

Mysql :: Error：表'mysite_development.recent_activities'不存在：从`recent_activities`中显示字段

如何避免此消息？有没有其他解决方案？

Answer 1

您可以直接从ActiveRecord :: Base获取数据库连接，但是它没有扩展AR :: Base有用，因为诸如sanitize_sql之类的有用方法受到了保护。

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}