Rails 4 JOIN GROUP BY and SELECT

Question

I am trying to JOIN 2 tables in Rails 4, to perform a count and also keep a column of the joined table.

Models: User has_many :orders

What I want: the number of orders AND the date of the last order.

has_many :orders, -> { select("users.*, count(orders.id) as orders_count").group('users.id') }

I would like to select the created_at column in the ORDERS table like so

select("users.*, orders.created_at, count(orders.id) as orders_count").group('users.id')

But in that case I get an error

PG::GroupingError: ERROR:  column "orders.created_at" must appear in the GROUP BY clause or be used in an aggregate function

I need the FIRST created_at so I tried the SQL Aggregate Function "FIRST" on the created_at column

select("users.*, first(orders.created_at) as most_recent, count(orders.id) as orders_count").group('users.id')

But I would get

PG::UndefinedFunction: ERROR:  function first(character varying) does not exist

Any idea how can I achieve this JOIN, COUNT and SELECT on the joined table?

Answer 1

Would the following work for you?

User.joins(:orders)
    .select("users.*, max(orders.created_at) as most_recent, count(orders.id) as orders_count")
    .group('users.id')

Taking the max of order.created_at should give you the date of the most recent order. I don't think you want to have your select as part of has_many orders, since you're looking for a list of users, not a list of orders. If you'd a method that returns this active record query, assuming you'll use it more than once, you can add the following to your User model.

def self.with_order_info
  self.joins(:orders)
      .select("users.*, max(orders.created_at) as most_recent, count(orders.id) as orders_count")
      .group('users.id')
end

And then, you can call that method anywhere using:

@users = User.with_order_info

As a further note (to be 100% clear), you should keep your association to orders as:

has_many :orders