left join and return only the latest record from right table using LARAVEL 6.5.0

Tags: , , , ,



Table 1
----------
NameID     Name
------------------
1           A
2           B
3           C
-----------------

Table 2
--------------------
ID   NameID   Order   
--------------------
1      1      Sugar
2      1      Salt 
3      2      Garlic 
4      2      Sugar
5      2      Onion
6      3      Oil
7      3      Black pepper 

I want to return only the latest and only one record per nameID from right table I dont know what code to use

This is the Result I want to return

RESULT
----------------------------
Name           Order
---------------------------
A              Salt  
B              Onion    
C              Black pepper

Controller.php

return DB::table('table1')
->leftjoin('table2','table1.nameID','=','table2.nameID')
-get();

Answer

try this

$data = Table1::select('Table1.Name', 'Table2.Order','Table2.ID')
->leftJoin('Table2', function($query) {
   $query->on('Table1.NameID','=','Table2.NameID')
        ->whereRaw('Table2.ID IN (select MAX(a2.ID) from Table2 as a2 join Table1 as u2 on u2.NameID = a2.NameID group by u2.NameID)');
})->get();

Edited :

$data = Table1::select('Table1.Name', 'Table2.Order','Table2.ID')


Source: stackoverflow