Laravel eloquent selecting most recent row from joined table
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'))
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.
In your project model, make the notes
relationship function look something like:
public function notes() { return $this->hasMany('Note');}
Then you can get the most recent Note of the Project with primary key 1 by chaining orderBy
and first
:
Project::find(1)->notes()->orderBy('created_at', 'desc')->first();
Also, is your note table called 'Note' or 'Notes'? (Keeping with Laravel style, it should be named singular as I have it in this code)