Laravel ordering results of a left join

Question

I am trying to replicate the below SQL in Laravels Eloquent query builder.

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.

I need to get the order by 'created_at' desc into the left join but I don't know how to achieve this.

Any help would be much appreciated.

You need subquery. Check this http://stackoverflow.com/questions/4580479/mysql-left-join-group-by-and-order-by-not-working-as-required — Jarek Tkaczyk, Jun 13 '14 at 13:56
Yes, there is a subquery already in the Laravel code but I don't know how to add the order by into it. I get a parse error when I try $join->orderBy('creted_at', 'DESC') — Alex, Jun 13 '14 at 14:10
No, there is no subquery. You just join a table, while you need to join a sub select instead. Something like this http://stackoverflow.com/questions/18079281/laravel-fluent-query-builder-join-with-subquery — Jarek Tkaczyk, Jun 13 '14 at 15:13
this package can help you a look https://github.com/fico7489/laravel-eloquent-join — fico7489, Dec 17 '17 at 18:19

score 5 · Answer 1 · edited Feb 04 '15 at 12:18

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

This is completely different query that returns all the rows from joined table instead of just a single, latest one. The subquery is necessary for this. — Jarek Tkaczyk, Jun 13 '14 at 18:41

Laravel ordering results of a left join

1 Answers1