Home Rails 4 JOIN GROUP BY and SELECT
Reply: 0

Rails 4 JOIN GROUP BY and SELECT

user1202
1#
user1202 Published in June 21, 2018, 8:40 am

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?

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO