CODEWITHSUNDEEP
laravelsql-order-byleft-joineloquent
Alex
Alex

Reputation: 183

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.

Upvotes: 4

Views: 21827

Answers (1)

user1669496
user1669496

Reputation: 33078

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

Upvotes: 5

Related Questions