问题描述
刚开始学习活动的记录,并想知道如何以最佳方式检索,其中涉及的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:参观,:通过=>:事件
的has_many:labtests,:通过=> :事件
结束
类事件<的ActiveRecord :: Base的
的has_many:访问
的has_many:labtests
belongs_to的:病人
结束
上课参观<的ActiveRecord :: Base的
belongs_to的:事件
结束
类LABTEST<的ActiveRecord :: Base的
belongs_to的:事件
结束
由于保隆指出,在:选择
选项不能与使用 :包括
选项。然而,:加入
选项即可。这是你想要的这里。事实上,它可以采取相同的参数:包括
或使用自己的SQL。下面是一些粗糙的,未经检验的code,可能需要一些小的摆弄。
Event.all(:选择=>中events.id,patients.lname,events.patient_id,events.event_type,MAX(events.event_date)为max_date:加入=>:患者,:组=>中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聚合查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!