能否Rails的活动记录处理SQL聚合查询？ [英] Can Rails' Active Record handle SQL aggregate queries?

问题描述

刚开始学习活动的记录，并想知道如何以最佳方式检索，其中涉及的SQL汇总查询多个表中的数据。

在下面的例子中（从医学的应用程序）我正在寻找不同类型的为每位患者最近发生的事件（如上次访问，最后LABTEST等）。你可以从SQL查询中看到下面我正在寻找从分组查询最大（日）值。我使出的find_by_sql要做到这一点 - 不过，我倒要看看如何做到这一点，而无需使用的find_by_sql

IOW - 在这里使用纯ActiveRecord的方法，你怎么会得到所需要的数据。下面是表和Class DEFS我与测试：

由SQL查询来获取最新的条目为每种类型 - 请注意最大（EVENT_DATE）在这里

  STRSQL =选择p.lname，e.patient_id，e.event_type，MAX（e.event_date）作为EVENT_DATE
     从事件ê
         内部联接患者P的e.patient_id = p.id
         按p.lname，e.patient_id，e.event_type
 

下面的示例SQL查询结果：

LNAME，patient_id，EVENT_TYPE，最新
亨特，3，LABTEST，2003-05-01 00:00:00
亨特，3，访问，2003-03-01 00:00:00
'Seifer'，2'，LABTEST'，'2002-05-01 0点00分○○秒'
Seifer，2，访问，2002-03-01 00:00:00

表的关系是：
表--->患者 - >活动
                                - >访问
                                - > labtests
                                - > ...等
病人
      t.string：LNAME
      t.date：DOB

事件
      t.column：patient_id，整数
      t.column：EVENT_DATE，时间：DATETIME
      t.column：EVENT_TYPE，：字符串

访问
      t.column：EVENT_ID，整数
      t.column：visittype，：字符串

labtests
      t.column：EVENT_ID，整数
      t.column：testtype，：字符串
      t.column：testvalue，：字符串

类

 类患者LT;的ActiveRecord :: Base的
  的has_many：事件
  的has_many：参观，：通过=＆GT;：事件
  的has_many：labtests，：通过=＆GT; ：事件
结束

类事件＆LT;的ActiveRecord :: Base的
  的has_many：访问
  的has_many：labtests
  belongs_to的：病人
结束

上课参观＆LT;的ActiveRecord :: Base的
  belongs_to的：事件
结束

类LABTEST＆LT;的ActiveRecord :: Base的
    belongs_to的：事件
结束
 

解决方案

由于保隆指出，在：选择选项不能与使用 ：包括选项。然而，：加入选项即可。这是你想要的这里。事实上，它可以采取相同的参数：包括或使用自己的SQL。下面是一些粗糙的，未经检验的code，可能需要一些小的摆弄。

  Event.all（：选择=＆gt;中events.id，patients.lname，events.patient_id，events.event_type，MAX（events.event_date）为max_date：加入=＆GT;：患者，：组=＆gt;中patients.lname，events.patient_id，events.event_type）
 

请注意我修改的东西咯。我改名为 EVENT_DATE 别名 max_date 所以没有混乱哪个属性你指的是。在中使用的属性：选择查询是在型号返回。例如，在此您可以调用 event.max_date 。我还添加了事件 ID 列，因为有时你可以得到你怎么没有 ID 属性（取决于一些讨厌的错误使用返回的型号）。

与的主要区别：包括和：加入是相关车型的前执行的立即加载。换句话说，它会自动获取每个事件的患者相关联的对象。这就要求选择语句的控制，因为它需要来选择，同时病人​​属性。随着：加入病人对象没有实例化

Just started learning active record and am wondering how to best retrieve data from multiple tables where an SQL aggregate query is involved.

In the following example (from a medical app) I'm looking for the most recent events of various types for each patient (e.g. last visit, last labtest etc). As you can see from the sql query below I'm looking for the max(date) value from a grouped query. I resorted to find_by_sql to do this - however I'd like to see how to do this without using find_by_sql.

IOW - how would you get the required data here using a pure ActiveRecord approach. Below are the Table and Class defs I'm testing with:

Find by Sql to retrieve most recent entries for each type - note the 'max(event_date)' here

strsql = "select  p.lname, e.patient_id, e.event_type, max(e.event_date) as event_date 
     from events e   
         inner join patients p on e.patient_id = p.id
         group by p.lname, e.patient_id, e.event_type"

Here's the sample sql query result:

lname, patient_id, event_type, latest
'Hunt', 3, 'Labtest', '2003-05-01 00:00:00'
'Hunt', 3, 'Visit', '2003-03-01 00:00:00'
'Seifer', 2, 'Labtest', '2002-05-01 00:00:00'
'Seifer', 2, 'Visit', '2002-03-01 00:00:00'

Table Relationships are:
Tables ---> Patients --> Events
                               --> visits
                               --> labtests
                               --> ... other
patients
      t.string :lname
      t.date :dob

events
      t.column :patient_id, :integer
      t.column :event_date, :datetime
      t.column :event_type, :string

visits 
      t.column :event_id, :integer
      t.column :visittype, :string

labtests
      t.column :event_id, :integer
      t.column :testtype, :string
      t.column :testvalue, :string

Classes

class Patient < ActiveRecord::Base
  has_many :events
  has_many :visits, :through =>:events
  has_many :labtests, :through => :events
end

class Event < ActiveRecord::Base
  has_many :visits
  has_many :labtests
  belongs_to :patient
end

class Visit < ActiveRecord::Base
  belongs_to :event
end

class Labtest < ActiveRecord::Base
    belongs_to :event
end

解决方案

As Pallan pointed out, the :select option cannot be used with the :include option. However the :joins option can. And this is what you want here. In fact, it can take the same arguments as :include or use your own SQL. Here's some rough, untested code, may need some minor fiddling.

Event.all(:select => "events.id, patients.lname, events.patient_id, events.event_type, max(events.event_date) as max_date", :joins => :patient, :group => "patients.lname, events.patient_id, events.event_type")

Note I modified things slightly. I renamed the event_date alias to max_date so there's no confusion over which attribute you are referring to. The attributes used in your :select query are available in the models returned. For example, in this you can call event.max_date. I also added the event id column because you can sometimes get some nasty errors without an id attribute (depending on how you use the returned models).

The primary difference between :include and :joins is that the former performs eager loading of the associated models. In other words, it will automatically fetch the associated patient object for each event. This requires control of the select statement because it needs to select the patient attributes at the same time. With :joins the patient objects are not instantiated.

这篇关于能否Rails的活动记录处理SQL聚合查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！