如何使用 Eloquent 对分组结果进行排序? [英] How to order grouped results using Eloquent?

问题描述

我已经尝试解决这个问题有一段时间了，但似乎无法让它发挥作用.我有一张与此类似的表.

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.

表格:问题

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

我的问题是我需要对它进行排序(简单！)但是，我只想获取每本杂志中的一本(列 magazine_id).

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

我目前的 Eloquent 查询是:

My Eloquent query as of now is:

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

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

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?

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

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

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

或

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

我们将不胜感激.

我目前最接近的是这个:

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.

推荐答案

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

You need to add a MAX function in the SELECT clause for each column to be ordered in DESCending order. The inverse goes for columns ordered in ASCending order, you need to add MIN function in the SELECT clause.

您的 Eloquent 查询必须包含一个 raw 选择:

Your Eloquent query has to include a raw select:

$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();

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

The only drawback is 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.

更新:似乎在 SELECT 子句中的聚合函数之前添加 * 选择器 也有效.这意味着您将原始选择重写为:

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

->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.

这篇关于如何使用 Eloquent 对分组结果进行排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！