Problem with Laravel SQL Query

@rodrigo.pedra Still getting the same error

"message": "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`cat_desc))` as `cat_desc`, `SUM(kwh_used)` as `kwh_used`, `SUM(cost)` as `cost' at line 1 (SQL: select `MIN(IF(category`.`description IS NOT NULL, category`.`description, your_electricity_yesterday_category`.`cat_desc))` as `cat_desc`, `SUM(kwh_used)` as `kwh_used`, `SUM(cost)` as `cost`, `your_electricity_yesterday_category`.`category_id` from `your_electricity_yesterday_category` left join `category` as `category` on `your_electricity_yesterday_category`.`category_id` = `category`.`id` where `your_electricity_yesterday_category`.`category_id` = 11 and `your_electricity_yesterday_category`.`building_id` = 52 and `your_electricity_yesterday_category`.`category_id` not in (select `category_id` from `building_category_settings` where `building_id` = 52 and `hide_from_electricity_widget` = 1 group by `category_id` order by `kwh_used` desc))", 

@monstajamss ah ok, you should also use selectRaw, or wrap those expressions on DB::raw() so Laravel doesn't try to parse them as a table/column pair:

$other = $this->electricityConnections->select('category_id')
    ->from('building_category_settings')
    ->where('building_id', '=', 52)
    ->where('hide_from_electricity_widget', '=', 1)
    ->groupBy('category_id')
    ->orderBy('kwh_used', 'desc');

$electCategory = $this->electricityConnections
    ->selectRaw('MIN(IF(category.description IS NOT NULL, category.description, your_electricity_yesterday_category.cat_desc)) as cat_desc')
    ->selectRaw('SUM(kwh_used) as kwh_used')
    ->selectRaw('SUM(cost) as cost')
    ->addSelect('your_electricity_yesterday_category.category_id')
    ->leftJoin('category as category', 'your_electricity_yesterday_category.category_id', '=', 'category.id')
    ->where('your_electricity_yesterday_category.category_id', '=', 11)
    ->where('your_electricity_yesterday_category.building_id', '=', 52)
    ->whereNotIn('your_electricity_yesterday_category.category_id', $other)
    ->get();

dd($electCategory);

Reference: https://laravel.com/docs/9.x/queries#raw-expressions

@rodrigo.pedra Now i am getting another error entirely

    "message": "SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause (SQL: select MIN(IF(category.description IS NOT NULL, category.description, your_electricity_yesterday_category.cat_desc)) as cat_desc, SUM(kwh_used) as kwh_used, SUM(cost) as cost, `your_electricity_yesterday_category`.`category_id` from `your_electricity_yesterday_category` left join `category` as `category` on `your_electricity_yesterday_category`.`category_id` = `category`.`id` where `your_electricity_yesterday_category`.`category_id` = 11 and `your_electricity_yesterday_category`.`building_id` = 52 and `your_electricity_yesterday_category`.`category_id` not in (select `category_id` from `building_category_settings` where `building_id` = 52 and `hide_from_electricity_widget` = 1 group by `category_id` order by `kwh_used` desc))",

I actually ended up doing this from the link you pasted above

$other = $this->electricityConnections->select('category_id')
        ->from('building_category_settings')
        ->where('building_id', '=', 52)
        ->where('hide_from_electricity_widget', '=', 1)
        ->groupBy('category_id')
        ->orderBy('kwh_used', 'desc')
        ;
        $electCategory = $this->electricityConnections->select(\DB::raw('MIN(IF(category.description IS NOT NULL, category.description, your_electricity_yesterday_category.cat_desc)) as cat_desc'), (\DB::raw('SUM(kwh_used) as kwh_used')), (\DB::raw('SUM(cost) as cost')), 
        'your_electricity_yesterday_category.category_id')
        ->leftJoin('category as category','your_electricity_yesterday_category.category_id', '=', 'category.id')
        ->where('your_electricity_yesterday_category.category_id', '=', 11)
        ->where('your_electricity_yesterday_category.building_id', '=', 52)
        ->whereNotIn('your_electricity_yesterday_category.category_id', $other)
        ->get();
        dd($electCategory);

Any idea of the cause?

@monstajamss https://sinnbeck.dev/posts/laravel-groupby-error

@monstajamss read @sinnbeck 's blog post to understand how SQL GROUP BY works.

It is missing a ->groupBy(...) on the non-aggregated column:

    // ...
    ->groupBy('your_electricity_yesterday_category.category_id') // ADD THIS
    ->get();

@rodrigo.pedra and @sinnbeck Thanks Guys your solutions and explanation helped in figuring it out :)

Not sure who to give solved to 🤔

@monstajamss choose at your will, to me, and I am sure to @sinnbeck too, the most important thing is to help =)

Have a nice day

@monstajamss just leave it where it is I just gave a link to my blog ☺️