How Get Count in Join Query in Laravel

0
votes

I want to convert the following SQL to Laravel.

SELECT COUNT('uc.*') AS count_down, bs.brand_name
FROM `user_activities` AS uc
    JOIN brands AS bs ON uc.brand_id = bs.id
GROUP BY uc.brand_id
ORDER BY count_down DESC
LIMIT 5

But when doing this:

$top_donwload_list = DB::table('user_activities')
    ->leftJoin('brands', 'brands.id', '=', 'user_activities.brand_id')
    ->selectRaw('brands.*, brands.brand_name, brands.id, count(user_activities.action_type) as user_activitiesCount')
    ->groupBy('user_activities.brand_id')
    ->get();

I get this error:

SQLSTATE[42000]: Syntax error or access violation: 1055 'colorworld.brands.id' isn't in GROUP BY (SQL: select brands.*, brands.brand_name, brands.id, count(user_activities.action_type) as user_activitiesCount from user_activities left join brands on brands.id = user_activities.brand_id group by user_activities.brand_id)

I tried to set 'strict' => true, in database.php but I get the same error in Laravel 5.7.

Update:- database table

Brand Table User activities

2
phpmysqllaravellaravel-5.7

2 Answers

4
votes

If I understood your question correctly, you are trying to find the count of user activities grouped by a brand's name. Also you want the top 5 records ordered by the ones with the most user activities.

So, the following should work: 

$top_donwload_list = DB::table('user_activities')
    ->selectRaw("brands.brand_name, COUNT('user_activities.*') as user_activitiesCount")
    ->join('brands', 'brands.id', '=', 'user_activities.brand_id')
    ->groupBy('brands.brand_name')
    ->orderBy('user_activitiesCount', 'desc')
    ->take(5)
    ->get();
0
votes

You can use Laravel Eloquent. I Assume that the relationship between brand and user_activities is one to many and Here, the "Brand" is the model of the brand entity. 

$count = Brand::leftJoin
                 ('user_activities', function($join_brands){ 
                    $join_brands-> on 
                        ('brands.id', '=','user_activity.brand_id');
                 )->orderBy('user_activities.count_down','desc ')                     
                  ->groupBy('user_activities.brand_id')->count();