I have a Company model that has_many Statement.
我有一个公司模型，它有很多说法。
class Company < ActiveRecord::Base
has_many :statements
end
I want to get statements that have most latest date field grouped by fiscal_year_end field.
我想要得到按fiscal_year_end字段分组的最新日期字段的语句。
I implemented the function like this:
我实现了这样的函数:
c = Company.first
c.statements.to_a.group_by{|s| s.fiscal_year_end }.map{|k,v| v.max_by(&:date) }
It works ok, but if possible I want to use ActiveRecord query(SQL), so that I don't need to load unnecessary instance to memory.
它可以正常工作，但是如果可能的话，我希望使用ActiveRecord查询(SQL)，这样就不需要将不必要的实例加载到内存中。
How can I write it by using SQL?
如何使用SQL编写它?
4 个解决方案
#1
2
select t.username, t.date, t.value
from MyTable t
inner join (
select username, max(date) as MaxDate
from MyTable
group by username
) tm on t.username = tm.username and t.date = tm.MaxDate
#2
2
For these kinds of things, I find it helpful to get the raw SQL working first, and then translate it into ActiveRecord afterwards. It sounds like a textbook case of GROUP BY:
对于这类事情，我发现先让原始SQL工作，然后再将其转换为ActiveRecord是很有帮助的。这听起来像教科书上的集体案例:
SELECT fiscal_year_end, MAX(date) AS max_date
FROM statements
WHERE company_id = 1
GROUP BY fiscal_year_end
Now you can express that in ActiveRecord like so:
现在你可以在ActiveRecord中这样表达:
c = Company.first
c.statements.
group(:fiscal_year_end).
order(nil). # might not be necessary, depending on your association and Rails version
select("fiscal_year_end, MAX(date) AS max_date")
The reason for order(nil) is to prevent ActiveRecord from adding ORDER BY id to the query. Rails 4+ does this automatically. Since you aren't grouping by id, it will cause the error you're seeing. You could also order(:fiscal_year_end) if that is what you want.
order(nil)的原因是防止ActiveRecord通过id向查询添加order。Rails 4+自动执行此操作。由于不是按id进行分组，因此会导致所看到的错误。如果您需要的话，您也可以订购(:fiscal_year_end)。
That will give you a bunch of Statement objects. They will be read-only, and every attribute will be nil except for fiscal_year_end and the magically-present new field max_date. These instances don't represent specific statements, but statement "groups" from your query. So you can do something like this:
这会给你一堆语句对象。它们将是只读的，每个属性都将为nil，除了财政年度结束和magicpresent的新字段max_date。这些实例并不表示特定的语句，而是来自查询的语句“组”。你可以这样做:
- @statements_by_fiscal_year_end.each do |s|
%tr
%td= s.fiscal_year_end
%td= s.max_date
Note there is no n+1 query problem here, because you fetched everything you need in one query.
注意，这里没有n+1查询问题，因为您在一个查询中获取了所需的所有内容。
If you decide that you need more than just the max date, e.g. you want the whole statement with the latest date, then you should look at your options for the greatest n per group problem. For raw SQL I like LATERAL JOIN, but the easiest approach to use with ActiveRecord is DISTINCT ON.
如果你决定你需要的不仅仅是最大的日期，例如你想要完整的陈述和最新的日期，那么你应该看看你的选项，每个组最大的n。对于原始SQL，我喜欢横向连接，但是使用ActiveRecord最简单的方法是ON。
Oh one more tip: For debugging weird errors, I find it helpful to confirm what SQL ActiveRecord is trying to use. You can use to_sql to get that:
哦，还有一个提示:对于调试奇怪的错误，我发现确认SQL ActiveRecord正在尝试使用的是有用的。您可以使用to_sql来获得:
c = Company.first
puts c.statements.
group(:fiscal_year_end).
select("fiscal_year_end, MAX(date) AS max_date").
to_sql
In that example, I'm leaving off order(nil) so you can see that ActiveRecord is adding an ORDER BY clause you don't want.
在这个例子中，我省略了order(nil)你可以看到ActiveRecord添加了一个你不想要的order BY子句。
#3
0
for example you want to get all statements by start of the months you should use this
例如，您希望在开始的几个月前得到所有的语句，您应该使用它。
@companey = Company.first
@statements = @companey.statements.find(:all, :order => 'due_at, id', :limit => 50)
then group them as you want
然后按自己的意愿分组
@monthly_statements = @statements.group_by { |statement| t.due_at.beginning_of_month }
#4
0
Building upon Bharat's answer you can do this type of query in Rails using find_by_sql in this way:
根据Bharat的回答，您可以使用find_by_sql在Rails中执行这种类型的查询:
Statement.find_by_sql ["Select t.* from statements t INNER JOIN (
SELECT fiscal_year_end, max(date) as MaxDate GROUP BY fiscal_year_end
) tm on t.fiscal_year_end = tm.fiscal_year_end AND
t.created_at = tm.MaxDate WHERE t.company_id = ?", company.id]
Note the last where part to make sure the statements belong to a specific company instance, and that this is called from the class. I haven't tested this with the array form, but I believe you can turn this into a scope and use it like this:
请注意最后一个部分，以确保语句属于某个特定的公司实例，并且这是从类中调用的。我还没有对数组形式进行测试，但是我相信您可以将其转换为一个范围，并像这样使用它:
# In Statement model
scope :latest_from_fiscal_year, lambda |enterprise_id| {
find_by_sql[..., enterprise_id] # Query above
}
# Wherever you need these statements for a particular company
company = Company.find(params[:id])
latest_statements = Statement.latest_from_fiscal_year(company.id)
Note that if you somehow need all the latest statements for all companies then this most likely leave you with a N+1 queries problem. But that is a beast for another day.
注意，如果您需要所有公司的所有最新语句，那么这很可能会给您带来N+1查询问题。但那是另一天的野兽。
Note: If anyone else has a way to have this query work on the association without using the last where part (company.statements.latest_from_year and such) let me know and I'll edit this, in my case in rails 3 it just pulled em from the whole table without filtering.
注意:如果其他人有办法让这个查询在关联上工作，而不使用最后的where部分(company.语句)。latest_from_year等)告诉我，我将编辑它，在我的rails 3中，它只是从整个表中提取em而不进行过滤。
#1
2
select t.username, t.date, t.value
from MyTable t
inner join (
select username, max(date) as MaxDate
from MyTable
group by username
) tm on t.username = tm.username and t.date = tm.MaxDate
#2
2
For these kinds of things, I find it helpful to get the raw SQL working first, and then translate it into ActiveRecord afterwards. It sounds like a textbook case of GROUP BY:
对于这类事情，我发现先让原始SQL工作，然后再将其转换为ActiveRecord是很有帮助的。这听起来像教科书上的集体案例:
SELECT fiscal_year_end, MAX(date) AS max_date
FROM statements
WHERE company_id = 1
GROUP BY fiscal_year_end
Now you can express that in ActiveRecord like so:
现在你可以在ActiveRecord中这样表达:
c = Company.first
c.statements.
group(:fiscal_year_end).
order(nil). # might not be necessary, depending on your association and Rails version
select("fiscal_year_end, MAX(date) AS max_date")
The reason for order(nil) is to prevent ActiveRecord from adding ORDER BY id to the query. Rails 4+ does this automatically. Since you aren't grouping by id, it will cause the error you're seeing. You could also order(:fiscal_year_end) if that is what you want.
order(nil)的原因是防止ActiveRecord通过id向查询添加order。Rails 4+自动执行此操作。由于不是按id进行分组，因此会导致所看到的错误。如果您需要的话，您也可以订购(:fiscal_year_end)。
That will give you a bunch of Statement objects. They will be read-only, and every attribute will be nil except for fiscal_year_end and the magically-present new field max_date. These instances don't represent specific statements, but statement "groups" from your query. So you can do something like this:
这会给你一堆语句对象。它们将是只读的，每个属性都将为nil，除了财政年度结束和magicpresent的新字段max_date。这些实例并不表示特定的语句，而是来自查询的语句“组”。你可以这样做:
- @statements_by_fiscal_year_end.each do |s|
%tr
%td= s.fiscal_year_end
%td= s.max_date
Note there is no n+1 query problem here, because you fetched everything you need in one query.
注意，这里没有n+1查询问题，因为您在一个查询中获取了所需的所有内容。
If you decide that you need more than just the max date, e.g. you want the whole statement with the latest date, then you should look at your options for the greatest n per group problem. For raw SQL I like LATERAL JOIN, but the easiest approach to use with ActiveRecord is DISTINCT ON.
如果你决定你需要的不仅仅是最大的日期，例如你想要完整的陈述和最新的日期，那么你应该看看你的选项，每个组最大的n。对于原始SQL，我喜欢横向连接，但是使用ActiveRecord最简单的方法是ON。
Oh one more tip: For debugging weird errors, I find it helpful to confirm what SQL ActiveRecord is trying to use. You can use to_sql to get that:
哦，还有一个提示:对于调试奇怪的错误，我发现确认SQL ActiveRecord正在尝试使用的是有用的。您可以使用to_sql来获得:
c = Company.first
puts c.statements.
group(:fiscal_year_end).
select("fiscal_year_end, MAX(date) AS max_date").
to_sql
In that example, I'm leaving off order(nil) so you can see that ActiveRecord is adding an ORDER BY clause you don't want.
在这个例子中，我省略了order(nil)你可以看到ActiveRecord添加了一个你不想要的order BY子句。
#3
0
for example you want to get all statements by start of the months you should use this
例如，您希望在开始的几个月前得到所有的语句，您应该使用它。
@companey = Company.first
@statements = @companey.statements.find(:all, :order => 'due_at, id', :limit => 50)
then group them as you want
然后按自己的意愿分组
@monthly_statements = @statements.group_by { |statement| t.due_at.beginning_of_month }
#4
0
Building upon Bharat's answer you can do this type of query in Rails using find_by_sql in this way:
根据Bharat的回答，您可以使用find_by_sql在Rails中执行这种类型的查询:
Statement.find_by_sql ["Select t.* from statements t INNER JOIN (
SELECT fiscal_year_end, max(date) as MaxDate GROUP BY fiscal_year_end
) tm on t.fiscal_year_end = tm.fiscal_year_end AND
t.created_at = tm.MaxDate WHERE t.company_id = ?", company.id]
Note the last where part to make sure the statements belong to a specific company instance, and that this is called from the class. I haven't tested this with the array form, but I believe you can turn this into a scope and use it like this:
请注意最后一个部分，以确保语句属于某个特定的公司实例，并且这是从类中调用的。我还没有对数组形式进行测试，但是我相信您可以将其转换为一个范围，并像这样使用它:
# In Statement model
scope :latest_from_fiscal_year, lambda |enterprise_id| {
find_by_sql[..., enterprise_id] # Query above
}
# Wherever you need these statements for a particular company
company = Company.find(params[:id])
latest_statements = Statement.latest_from_fiscal_year(company.id)
Note that if you somehow need all the latest statements for all companies then this most likely leave you with a N+1 queries problem. But that is a beast for another day.
注意，如果您需要所有公司的所有最新语句，那么这很可能会给您带来N+1查询问题。但那是另一天的野兽。
Note: If anyone else has a way to have this query work on the association without using the last where part (company.statements.latest_from_year and such) let me know and I'll edit this, in my case in rails 3 it just pulled em from the whole table without filtering.
注意:如果其他人有办法让这个查询在关联上工作，而不使用最后的where部分(company.语句)。latest_from_year等)告诉我，我将编辑它，在我的rails 3中，它只是从整个表中提取em而不进行过滤。