Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Laravel ordering results of a left join

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.

like image 381
Alex Avatar asked Oct 17 '25 10:10

Alex


1 Answers

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();
like image 99
user1669496 Avatar answered Oct 19 '25 02:10

user1669496



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!