How to find only last row from second table, along

Question

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.

Answer 1

The right way to define the models would be:

Journal model

function volumes()
{
    return $this->belongsTo(Volume::class);
}

Volume model

function journals()
{
    return $this->hasMany(Journal::class);
}

Now $journal->volumes should return all the volumes that belong to that journal.

Answer 2

You can use a HasOne relationship:

class Journal extends Model
{
    public function latestVolume()
    {
        return $this->hasOne(Volume::class)->orderByDesc('id');
    }
}

$journals = Journal::with('latestVolume')->get();

Be aware that this will still fetch all volumes from the database. It then discards the "old" ones.

If you want to improve the performance by really only fetching the latest volume, you can use this package I've created: https://github.com/staudenmeir/eloquent-eager-limit

The package allows you to apply limit() to the relationship:

class Journal extends Model
{
    use \Staudenmeir\EloquentEagerLimit\HasEagerLimit;

    public function latestVolume()
    {
        return $this->hasOne(Volume::class)->orderByDesc('id')->limit(1);
    }
}