CODEWITHSUNDEEP
phplaravel-4
apsdehal
apsdehal

Reputation: 845

How to execute this complex query through Laravel Eloquent?

I have this complex query in SQL:

SELECT distinct(albumId) as id,
band,albumName as name,
SUM(numberOfPlays) as numberOfPlays
FROM (
SELECT COUNT(*) as numberOfPlays,
tracks.album_id as albumId,
a.name as albumName,b.name as band
FROM logs l
LEFT JOIN tracks ON tracks.id=l.track_id
LEFT JOIN albums a ON tracks.album_id = a.id
LEFT JOIN bands b ON b.id = tracks.band_id
WHERE l.time>$days
GROUP by trackid
ORDER BY numberOfPlays DESC LIMIT 0,100) t
GROUP BY albumId ORDER BY numberOfPlays DESC");

So far I have successfully retreived the inner query using this:

Logs::select(DB::raw(
                    'COUNT(*) as numberOfPlays'
                ), 'track_id')
               ->where('time', '>', $days)
               ->orderBy('numberOfPlays', 'DESC')->take(100)
               ->with('track', 'track.album')
               ->groupBy(\DB::raw("track_id"))->orderBy('numberOfPlays', 'DESC');

How can I run further queries on the data returned by the above query?

Upvotes: 0

Views: 193

Answers (1)

Raftalks
Raftalks

Reputation: 2037

You can actually achieve similar result through the Eloquent ORM and utilising the methods available on the Collection result returned. Here is an example. Considering you have all the Model Classes. 

$logs = Log::with(['track.album', 'track.band'])->where('time', '>', $days)->take(100)get();




class Log extends Eloquent {

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


Class Track extends Eloquent {

   public function album() {
     return $this->belongsTo('Album');
   }

   public function band() {
     return $this->belongsTo('Band');
   }

}

Class Album extends Eloquent {

}


Class Band extends Eloquent {}

Now to produce a result the way you want, you can use the returned collection to transform its items to a presentable structure.

first we will group the logs by album

$logsByAlbum = $logs->groupBy('track.album.id');

then transform the grouped logs 

$logsByAlbum->transform(function($album_logs) {

    if(!empty($album_logs)) {

      return array(
        'id' => $album_logs[0]->album->id,
        'album_name' => $album_logs[0]->album->name,
        'numberOfPlays' => count($album_logs)
      );

    }

    return array();

});

After transformation, we need to filter the items with empty values
$final_result = $logsByAlbum->filter(function($item) { 
      return !empty($item);
});

return $final_result->toArray();

This is just a basic idea to help you use the Eloquent ORM and the power of the methods provided in the collection class.

Enjoy! :D

Upvotes: 1

Related Questions