LARAVEL, ELOQUENT, SQL - groupBy 1

Question

When I run the below SQL manually I get my expected results. I am trying to write the same query in Laravel's Eloquent and I get an error:

SQLSTATE[42000]: Syntax error or access violation: 1056 Can't group on 'aggregate' (SQL: select count() as aggregate from daily_menu inner join meals on daily_menu.id = meals.daily_menu_id left join fruits on fruits.meal_id = meals.id where lodge_id = 1 group by 1)*

The original SQL Query that works:

SELECT
   d.id,
   SUM(IF(f.id IS NULL, 0, 1)) AS fruit_count
FROM daily_menu d
  JOIN meals m
    ON d.id = m.daily_menu_id
  LEFT JOIN fruits f
    ON m.id = f.meal_id 
GROUP BY 1

My Laravel Eloquent Implementation:

$query = $query
    ->select("daily_menu.id", DB::raw("SUM(IF(fruits.id IS NULL, 0, 1)) AS fruit_count"))
    ->join("meals", "daily_menu.id", "=", "meals.daily_menu_id")
    ->leftJoin("fruits", "fruits.meal_id", "=", "meals.id")
    ->groupBy(DB::raw("1"));

Wojo Wojo · Accepted Answer · 2020-03-12T18:11:49

$query = $query
        ->select("daily_menu.id", DB::raw("SUM(IF(fruits.id IS NULL, 0, 1)) AS fruit_count"))
        ->join("meals", "daily_menu.id", "=", "meals.daily_menu_id")
        ->leftJoin("fruits", "fruits.meal_id", "=", "meals.id")
        ->groupBy(DB::raw("daily_menu.id"));

I shouldn't have used 1 I should have used the proper column alias. Related: Can't fix this: "Cannot group by an aggregate"

LARAVEL, ELOQUENT, SQL - groupBy 1

1 Answers