mercredi 13 mars 2019

How to find only last row from second table, along with its first table row

I have two tables. journals and volumes. Journal Table has unique rows, and volumes table have rows based on journal table id, name is journal_id(may be multiple).

journals table is:

id | journal_name

1  | journal1
2  | journal2

volumes table is:

id | journal_id | volume_name

1  |    1       |  volume1
2  |    1       |  volume2
3  |    1       |  volume3
4  |    2       |  volume4
5  |    2       |  volume5

Now I need join with row from journal table and only last rows of volumes based on journal_id.

Result should be:

id | journal_name | journal_id | volume_name

1  | journal1     |   1        |   volume3
2  | journal2     |   2        |   volume5

Not all the rows from volumes table. (Need only last rows from each group of journal_id).

required result from mysql query is:

SELECT J.journal_name,V.id,V.journal_id FROM journals AS J 
INNER JOIN (SELECT *
FROM volumes
WHERE id IN (
SELECT MAX(id)
FROM volumes
GROUP BY journal_id
)) AS V ON J.id = V.journal_id

Now my try in laravel is:

controller is:

public function index()
{
$volumes = volume::with('volumes')->orderBy('id','desc')->limit(1)->get();
    return view('welcome',compact('volumes'));
}

volume model is:

function volumes()
 {
      return $this->belongsTo(journal::class, 'journal_id');
 }

But it is giving only one row from entire volume table. I need last one row from each group of journal_id in volume table.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire