左连接的 Laravel 排序结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24206872/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel ordering results of a left join
提问by Alex
I am trying to replicate the below SQL in Laravels Eloquent query builder.
我正在尝试在 Laravel Eloquent 查询构建器中复制以下 SQL。
select a.name, b.note from projects a
left join (select note, project_id from projectnotes order by created_at desc) as b on (b.project_id = a.id)
where projectphase_id = 10 group by a.id;
So far I have:
到目前为止，我有：
$projects = Project::leftjoin('projectnotes', function($join)
{
$join->on('projectnotes.project_id', '=', 'projects.id');
})
->where('projectphase_id', '=', '10')
->select(array('projects.*', 'projectnotes.note as note'))
->groupBy('projects.id')
->get()
which works for everything except getting the most recent projectnotes, it just returns the first one entered into the projectnotes table for each project.
除了获取最新的 projectnotes 之外，它适用于所有事情，它只返回输入到每个项目的 projectnotes 表中的第一个。
I need to get the order by 'created_at' desc into the left join but I don't know how to achieve this.
我需要通过 'created_at' desc 将订单放入左连接中，但我不知道如何实现。
Any help would be much appreciated.
任何帮助将非常感激。
回答by user1669496
Your subquery is unnecessary and is just making the entire thing inefficient. To be sure though, make sure this query returns the same results...
您的子查询是不必要的，只会使整个事情效率低下。不过，可以肯定的是，请确保此查询返回相同的结果...
SELECT
projects.name,
notes.note
FROM
projects
LEFT JOIN
projectnotes notes on projects.id = notes.project_id
WHERE
projects.projectphase_id = 10
ORDER BY
notes.created_at desc
If it does, that query translated to the query builder looks like this...
如果是这样，该查询转换为查询构建器看起来像这样......
$projects = DB::table('projects')
->select('projects.name', 'notes.note')
->join('projectnotes as notes', 'projects.id', '=', 'notes.project_id', 'left')
->where('projects.projectphase_id', '=', '10')
->orderBy('notes.created_at', 'desc')
->get();
