Laravel HasMany query latest entry and return related model

Question

Using Laravel 8

I have 3 models (Album, Track, Playcount). One Album has many Tracks, and one track has many Playcounts (every 24 hours a job will run and update the playcount).

Album Model

public function playcounts()
{
    return $this->hasManyThrough('App\Models\Playcount', 'App\Models\Track');
}

Track Model

public function playcounts()
{
    return $this->hasMany(Playcount::class, 'track_id');
}

Playcount Model

public function track()
{
    return $this->belongsTo(Track::class);
}

I would like to be able to select all albums where the LATEST entry in the playcounts table is older than 24 hours. Been stuck on this for days. What I'm trying right now is this:

Playcount Model

public function latest_playcount()
    {
        return $this->playcounts()->latest()->first();
    }

Controller

$track = Track::whereHas('playcounts', function($q) {
    $q->where('created_at', '<=', Carbon::now()->subHour(24));
        })
        ->with('playcounts')
        ->first();

$album_id = $track->album_id; //after this, select the album

is it possible to do this in one query?

Any help with this much appreciated.

EDIT: should anyone ever run into something similar, i solved it this way:

$album = DB::table('playcounts as p')
          ->select('p.*')
          ->leftJoin('playcounts as p1', function ($join) {
                $join->on('p.track_id', '=', 'p1.track_id')
                     ->whereRaw(DB::raw('p.created_at < p1.created_at'));
           })
          ->whereNull('p1.track_id')
          ->where('p.created_at', '<=', Carbon::now()->subHours(24))
                  ->join('tracks', 'tracks.id', 'p.track_id')
                    ->join('albums', 'albums.id', 'tracks.album_id')
                    ->addSelect('albums.*')
                    ->groupBy('albums.id')
                  ->take(1)->get();

Thank you everybody for helping!

Answer 1

As per my comment, have you tried this?

I've made assumptions that your tables are named: tracks, albums, playcounts

And that they all have the column names, I've listed.

use DB;

$albums = Playcount::where('created_at', '<=', Carbon::now()->subDay())
    ->join('tracks', 'tracks.id', 'playcounts.track_id')
    ->join('albums', 'albums.id', 'tracks.album_id')
    ->addSelect(DB::raw('albums.*, SUM(playcounts.value)'))
    ->groupBy('albums.id')
    ->get();

Of course, you can walk the other way, but this is simple. I'm not sure if you'll have dupes because of the 1 to many tracks -> albums relation, but the groupBy will solve that.