Rails 4 JOIN GROUP BY和SELECT [英] Rails 4 JOIN GROUP BY and SELECT

问题描述

我试图在Rails 4中联接2个表，以进行计数并保留联接表的一列.

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

型号:用户has_many:orders

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') }

我想像这样在ORDERS表中选择created_at列

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

我需要FIRST created_at，所以我在created_at列上尝试了SQL聚合函数"FIRST"

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')

但我会得到

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

有什么主意如何在联接表上实现此JOIN，COUNT和SELECT?

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

推荐答案

以下内容适合您吗?

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

采用order.created_at的max可以为您提供最新订单的日期.我认为您不希望将select作为has_many订单的一部分，因为您要查找的是用户列表，而不是订单列表.如果要使用返回此活动记录查询的方法，并假设您将多次使用它，则可以将以下内容添加到您的User模型中.

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

作为进一步的说明(100％清楚)，您应将与订单的关联保持为:

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

has_many :orders

这篇关于Rails 4 JOIN GROUP BY和SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！