php - 查询生成器 - 结合 orderBy 和 groupBy

Question

我有一个 Laravel 应用程序，我有一个查询应该按如下方式运行。

company   date      period
apple | 2017-08-23 | live
apple | 2017-09-04 | live
apple | 2014-03-04 | history
enron | 1987-09-09 | history
tesla | 2017-07-04 | live
tesla | 2017-06-03 | live

它需要按日期降序排列每个公司的条目，条件是 period='live'，从而返回每个公司的最后一个“实时”条目。在上面的例子中，这应该返回

company   date 
apple | 2017-09-04
tesla | 2017-07-04

我尝试使用查询生成器来完成此操作，例如:

return $query->where('period', '=', 'live')
        ->groupBy('company')
        ->orderBy('date','desc');

但是，这首先对结果进行分组，然后对它们进行排序，这会产生不可预测的结果。根据这个问题/答案，答案在于子查询:

ORDER BY date and time BEFORE GROUP BY name in mysql

但是，我无法将这种方法融入到我的问题中。您能否向我解释如何有效地解决这个问题，无论是使用子查询还是使用不同的方法，但不求助于原始查询？

Answer 1

您当前针对所需输出的策略是正确的，您不需要子查询。以下原始 MySQL 查询应该足够了:

SELECT company, MAX(date)
FROM companies
GROUP BY company

我们可以试试下面的 Laravel 代码:

return $query->where('period', '=', 'live')
    ->groupBy('company')
    ->orderBy('date','desc')
    ->get(['company', DB::raw('MAX(date) AS max_date')]);

与您在问题中所说的相反，如果您使用 ORDER BY 进行查询并且表中的基础数据没有更改，那么查询结果应该是完全可重现的。

编辑:

与您的问题相反，您的评论表明您想要每个公司的每个最大日期的整个匹配行。如果是这样，那么您确实需要以下原始 MySQL 查询:

SELECT c1.*
FROM companies c1
INNER JOIN
(
    SELECT company, MAX(date) AS max_date
    FROM companies
    GROUP BY company
) c2
    ON c1.company = c2.company AND
       c1.date = c2.max_date

我们可以尝试编写以下 Laravel 代码来处理这个问题:

DB::table('companies')
    ->select('*')
    ->join(DB::raw('(SELECT company, MAX(date) AS max_date
                     FROM companies GROUP BY company) c2'), function($join)
        {
            $join->on('companies.company', '=', 'c2.company');
            $join->on('companies.date', '=', 'c2.max_date');
        })
    ->orderBy('companies.date', 'DESC')
    ->get();