Laravel query builder count

1
votes

I'm using Laravels query builder to retrieve a list of items with some filter options - I need to do a count inside of this query:

        $f = DB::table('Likes')
            ->join('Freestyle', 'Likes.FreestyleID', '=', 'Freestyle.id')
            ->join('Beat', 'Freestyle.BeatId', '=', 'Beat.id')
            ->join('Track', 'Beat.TrackId', '=', 'Track.id')
            ->join('Genre', 'Track.GenreId', '=', 'Genre.id')
            ->select('Likes.freestyleID as likeFreestyleID', 'Freestyle.*', 'Beat.TrackId as UseMeForTrack',
    'Genre.id as GenreID')
            ->where('Freestyle.Active', '1')
            ->where('Freestyle.created_at', '>', "$dateScope")
            ->whereNull('Freestyle.deleted_at')
            ->whereIn('GenreID', $request->genre)
        ->first();

To count the amount of times the 'FreestyleID' appears in the likes table.

is this possible? The data returned is perfect I just need the amount of likes a freestyle has, where the FreestyleID in the likes table is null.

2
phpmysqllaravelquery-builder

2 Answers

0
votes

Something like this : 

$f = DB::table('Likes')
                ->join('Freestyle', 'Likes.FreestyleID', '=', 'Freestyle.id')
                ->join('Beat', 'Freestyle.BeatId', '=', 'Beat.id')
                ->join('Track', 'Beat.TrackId', '=', 'Track.id')
                ->join('Genre', 'Track.GenreId', '=', 'Genre.id')
                ->select('Likes.freestyleID as likeFreestyleID','count(Likes.FreestyleID)', 'Freestyle.*', 'Beat.TrackId as UseMeForTrack',
        'Genre.id as GenreID')
                ->where('Freestyle.Active', '1')
                ->where('Freestyle.created_at', '>', "$dateScope")
                ->whereNull('Freestyle.deleted_at')
                ->whereIn('GenreID', $request->genre)
            ->first();
0
votes

I think you should be able to use a raw expression like this:

$f = DB::table('Likes')
  ->join('Freestyle', 'Likes.FreestyleID', '=', 'Freestyle.id')
  ->join('Beat', 'Freestyle.BeatId', '=', 'Beat.id')
  ->join('Track', 'Beat.TrackId', '=', 'Track.id')
  ->join('Genre', 'Track.GenreId', '=', 'Genre.id')
  ->select(DB::raw('COUNT(likes.FreestyleID) as num_likes'), 'Likes.freestyleID as likeFreestyleID', 'Freestyle.*', 'Beat.TrackId as UseMeForTrack',
'Genre.id as GenreID')
  ->where('Freestyle.Active', '1')
  ->where('Freestyle.created_at', '>', "$dateScope")
  ->whereNull('Freestyle.deleted_at')
  ->whereIn('GenreID', $request->genre)
  ->groupBy('Freestyle.id')
  ->first();