CODEWITHSUNDEEP
phplaravellaravel-5eloquent
cre8
cre8

Reputation: 13562

Eloquent get row with most relationships where date is relevant

I am working with Laravel and my current task is to get the Top 10 of songs played during last week.

$mostPlayed = Track::join('track_tracklist as pl', 'pl.track_id', '=', 'tracks.id')
            ->groupBy('tracks.id')
            ->orderBy('count','desc')
            ->take(10)
            ->get(['tracks.*', DB::raw('COUNT(pl.track_id) as count')]);

This query works fine, it returns me the 10 most played songs but without a date limitation. So I need to limit the connected tracklists with a given date (it should be compared with the date stored in tracklists) but I don't know how to add it.

Thank you :)

Update:

I came to a possible solution but it's not very efficient. The code above needs 0.009023904800415 seconds, the code below 0.96272301673889, needing 100% more time. Of course the code above is faster because it's not comparing the date but there should be a better way to solve the problem.

    $time = '2015-05-01';
    $elements = [];
    $maxArray = [
        'element' => null,
        'count' => 0
    ];
    for ($i = 0; $i < 10; $i++) {
        $elements[] = $maxArray;
    }
    foreach (Track::all() as $track) {
        $tracklists = $track->tracklists()->where('time', '>=', $time)->get();
        for ($i = 0; $i < count($elements); $i++) {
            if (count($tracklists) > $elements[$i]['count']) {
                array_splice($elements, $i, 0, [[
                    'element' => $track,
                    'count' => count($tracklists)
                ]]);
                array_pop($elements);
                break;
            }
        }
    }

SOLUTION jedrzej.kurylo was close to the solution, just forgetting one argument for the first join call:

        $time = '2015-07-02';
        $mostPlayed = Track::join('track_tracklist', 'track_tracklist.track_id', '=', 'tracks.id')
            ->join('tracklists', function ($join) use ($time) {
                $join->on('tracklists.id', '=', 'track_tracklist.tracklist_id')->where('tracklists.time', '>=', $time);
            })
            ->groupBy('tracks.id')
            ->orderBy('count', 'desc')
            ->take(10)
            ->get(['tracks.*', DB::raw('COUNT(track_tracklist.track_id) as count')]);

Will return the top 10 of tracks which are played between today and the given date needing 0.01 sec instead of 1 sec by doing with with a for each loop.

Upvotes: 0

Views: 293

Answers (1)

jedrzej.kurylo
jedrzej.kurylo

Reputation: 40909

If you want to count only track_tracklist records from given daterange you'll need to apply additional constraints on the JOIN:

$time = '2015-05-01';
$mostPlayed = Track::join('track_tracklist as pl','pl.track_id', '=', 'tracks.id')
->join('tracklists', function($join) use ($time) {
  $join->on('pl.tracklist_id', '=', 'tracklists.id');
  $join->where('tracklists.time', '>=', $time)
})
->groupBy('tracks.id')
->orderBy('count','desc')
->take(10)
->get(['tracks.*', DB::raw('COUNT(pl.track_id) as count')]);

Upvotes: 1

Related Questions