Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Laravel eloquent selecting most recent row from joined table

I have two tables, Project and Projectnote

There is a one to many relationship between project and projectnote.

I want to be able to list my projects and select the most recent projectnotes based on the created_at date.

Is this possible to do in Eloquent, I can't figure it out.

Thanks for any help!

Edit: 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();
like image 574
Alex Avatar asked Jun 12 '14 13:06

Alex


People also ask

How can I get last record in laravel?

Use Model::where('user_id', $user_id)->latest()->get()->first(); it will return only one record, if not find, it will return null . Hope this will help.

What is latest () in laravel?

latest() function in Laravel used to get latest records from database using default column created_at . latest() is the equivalent to orderBy('created_at', 'desc')

How can I get the latest record in database based on datetime Laravel?

You can use latest() : DB::table('tbl')->latest()->first(); // considers created_at field by default.


2 Answers

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

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'))
like image 185
Diederik Avatar answered Oct 21 '22 20:10

Diederik


I would suggest you try to order by id in that specific scenario; the below is my suggested solution for you based on your code:

$projects = Project::join('projectnotes', 'projectnotes.project_id', '=', 'projects.id')
    ->select(array('projects.*', 'projectnotes.note as note'))
    ->orderBy('projectnotes.id')
    ->latest('projectnotes.id')
    ->get()->unique();

The code above works on Laravel 5.6; your can try it out.

like image 27
Mundruku Avatar answered Oct 21 '22 22:10

Mundruku