laravel 如何使用 Eloquent 对分组结果进行排序？

How to order grouped results using Eloquent?

提问by Andreas

I've been trying to figure this out for some time now and just can't seem to make it work. I have a table that looks similar to this.

一段时间以来，我一直试图弄清楚这一点，但似乎无法让它发挥作用。我有一张与此相似的表。

Table: Issues

表：问题

id  yearly_issue    year    stock   created_at      updated_at      magazine_id 
1   10              2000    1       [timestamp]     [timestamp]     3
2   12              1994    6       [timestamp]     [timestamp]     10
3   36              2007    10      [timestamp]     [timestamp]     102
4   6               2002    7       [timestamp]     [timestamp]     7
5   6               2002    2       [timestamp]     [timestamp]     5
6   12              2003    9       [timestamp]     [timestamp]     10
7   11              2003    12      [timestamp]     [timestamp]     10

My problem is that I need to sort it (easy!) but, I only want to get one of each magazine (column magazine_id).

我的问题是我需要对其进行排序（简单！）但是，我只想获取每本杂志中的一本（列 magazine_id）。

My Eloquent query as of now is:

到目前为止，我的雄辩查询是：

$issues = Issue::where('stock', ($all ? '>=' : '>'), 0)
  ->orderBy('year', 'desc')
  ->orderBy('yearly_issue', 'desc')
  ->take($perpage)
  ->get();

I thought adding the groupBy('magazine_id')would help, but it seems as though it only partially helps me. The results is not in the correct order. So, my question then is - is there any easy way around this?

我认为添加groupBy('magazine_id')会有所帮助，但似乎它只能部分帮助我。结果的顺序不正确。那么，我的问题是 - 有什么简单的方法可以解决这个问题吗？

I've been experimenting with various answers to similar questions but I completely fail to implement it.

我一直在尝试对类似问题的各种答案，但我完全无法实施。

Retrieving the last record in each group

检索每组中的最后一条记录

or

或者

How to get the latest record in each group using GROUP BY?

如何使用GROUP BY获取每个组中的最新记录？

More help would be much appreciated.

更多帮助将不胜感激。

EDIT: The closest I am currently is this:

编辑：我目前最接近的是这个：

SELECT i1.*, c.name AS image, m.title AS title
FROM issues i1
INNER JOIN covers c ON i1.id = c.issue_id
INNER JOIN magazines m ON i1.magazine_id = m.id
JOIN (SELECT magazine_id, MAX(year) year, MAX(yearly_issue) yearly_issue FROM issues GROUP BY magazine_id) i2
ON i1.magazine_id = i2.magazine_id
AND i1.year = i2.year
-- AND i1.yearly_issue = i2.yearly_issue
WHERE i1.stock ($all ? '>=' : '>') 0
ORDER BY i1.year DESC, i1.yearly_issue DESC
LIMIT $perpage

However, it is not giving me the desired result at all.

但是，它根本没有给我想要的结果。

回答by Rubens Mariuzzo

You need to add a MAXfunction in the SELECTclause for each column to be ordered in DESCending order. The inverse goes for columns ordered in ASCending order, you need to add MINfunction in the SELECTclause.

您需要MAX在SELECT子句中为要按DESC结束顺序排序的每一列添加一个函数。对于按ASC结束顺序排列的列，则相反，您需要MIN在SELECT子句中添加函数。

Your Eloquent query has to include a rawselect:

您的 Eloquent 查询必须包含一个原始选择：

$issues = DB::table('issues')
    ->select(DB::raw('id, max(year) as year, max(yearly_issue) as yearly_issue, stock, created_at, updated_at, magazine_id'))
    ->groupBy('magazine_id')
    ->orderBy('year', 'desc')
    ->orderBy('yearly_issue', 'desc')
    ->take(10)
    ->get();

The only drawbackis that you need to specify each column you want to retrieve. And do not use the *selector, it will override the aggregate function in the select clause.

唯一的缺点是您需要指定要检索的每一列。并且不要使用*选择器，它会覆盖选择子句中的聚合函数。

---

---

Update: Seems like adding the *selector beforethe aggregate functions in the SELECTclause works too. This means that you rewrite the raw select as:

更新：似乎在子句中的聚合函数之前添加*选择器也起作用。这意味着您将原始选择重写为：SELECT

->select(DB::raw('*, max(year) as year, max(yearly_issue) as yearly_issue'))

I think putting the *selector before makes the aggregate functions overrides their columns.

我认为将*选择器放在前面会使聚合函数覆盖它们的列。

回答by Willem Ellis

I was looking for a way to order results before grouping and this answer kept popping up. In my case, it was for a user's message inbox. I needed to display the latest message received by the user in each message thread they were part of. My table looks like this:

我一直在寻找一种在分组之前对结果进行排序的方法，这个答案不断出现。就我而言，它用于用户的消息收件箱。我需要在他们所属的每个消息线程中显示用户收到的最新消息。我的桌子看起来像这样：

--------------------------------------------------------------------
| id | sender_id | receiver_id | message | created_at | updated_at |
--------------------------------------------------------------------
|    |           |             |         |            |            |
--------------------------------------------------------------------

The accepted answer on this question, groups the results and then sorts them. I needed to sort the results by the created_atfield, descending, before grouping them so I'd get the latest message from each thread. Anyway, here was my final solution after a lot of digging:

关于这个问题的公认答案，对结果进行分组，然后对它们进行排序。我需要按字段对结果进行created_at降序排序，然后再对它们进行分组，这样我才能从每个线程中获取最新消息。无论如何，这是我经过大量挖掘后的最终解决方案：

$sub = $query->orderBy('created_at', 'desc');
return $query->select('*')
             ->from(DB::raw("({$sub->toSql()}) as sub"))
             ->groupBy('sender_id');

This constructs the following query:

这构造了以下查询：

select * from (
    select * from `messages`
    order by `created_at` desc
) as sub
group by `sender_id`

回答by Wade

In MySQL you can do something like this:

在 MySQL 中，您可以执行以下操作：

select * 
    from `UserTable` 
where userId=3
    group by `field` desc
    order by `dateActivityComplete` ;

In eloquent you would do something like this: ->groupBy('field','desc'), however, ordering the groupBy doesn't seem to be an option in eloquent the same as order by is.

在 eloquent 中，您会执行以下操作：->groupBy('field','desc')，但是，在 eloquent 中对 groupBy 进行排序似乎与 order by 不同。

Might be an instance where you have to use a raw query, or possibly the max function.

可能是您必须使用原始查询的实例，或者可能是 max 函数。

Wade

韦德