Laravel 雄辩地从连接表中选择最近的行 [英] Laravel eloquent selecting most recent row from joined table
问题描述
我有两个表,Project 和 Projectnote
I have two tables, Project and Projectnote
project 和 projectnote 之间存在一对多的关系.
There is a one to many relationship between project and projectnote.
我希望能够列出我的项目并根据 created_at 日期选择最新的项目笔记.
I want to be able to list my projects and select the most recent projectnotes based on the created_at date.
这是否可以在 Eloquent 中实现,我无法弄清楚.
Is this possible to do in Eloquent, I can't figure it out.
感谢您的帮助!
到目前为止,我有一个连接两个表的基本查询(如下).但是,这仅选择存在注释的项目,并且我得到多个行,其中每个项目有多个注释.
so far I have a basic query (below) that joins the two tables. However, this only selects projects where a note exists and I get multiple rows where there are several notes per project.
$projects = Project::join('projectnotes', 'projectnotes.project_id', '=', 'projects.id')
->select(array('projects.*', 'projectnotes.note as note'))
->get();
推荐答案
可以加入最新值的表格:
You can join the table on the latest value:
->join('t1', function($join) {
$join->on('t1.t2_id', '=', 't2.id')
->on('t1.id', '=', DB::raw("(select max(id) from t1 WHERE t1.t2_id = t2.id)"));
如果您还想在 t1 没有值的地方加入,请使用 leftJoin.在您的情况下,这看起来像这样:
If you also want to join where t1 does not have a value, use a leftJoin. In your case this would look like this:
$projects = Project::leftJoin('projectnotes', function($join) {
$join->on('projectnotes.project_id', '=', 'projects.id')
->on('projectnotes.id', '=', DB::raw("(SELECT max(id) from projectnotes WHERE projectnotes.project_id = projects.id)"));
})
->select(array('projects.*', 'projectnotes.note as note'))
这篇关于Laravel 雄辩地从连接表中选择最近的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!