How to get a most recent value group by year by using SQL

Question

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.

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.

How can I write it by using SQL?

Answer 1

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

Answer 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:

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:

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.

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:

- @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.

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.

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:

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.

Answer 3

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 }

Answer 4

Building upon Bharat's answer you can do this type of query in Rails using find_by_sql in this way:

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.

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.