Home Rails 4 JOIN GROUP BY and SELECT
Reply: 1

Rails 4 JOIN GROUP BY and SELECT

stefano_cdn
1#
stefano_cdn Published in 2015-05-04 13:26:49Z

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?

Joe Kennedy
2#
Joe Kennedy Reply to 2015-05-04 15:09:11Z

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
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.250564 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO