I have this table 'day_meals':

I am trying to get all records in the same date and combine duplicate row who have the same meal_id and meal_variable_id

so here we have 6 rows i want to get 5 rows and the rows (3, 4) combined with count = 2

i tried this code:

$meals_day = DayMeal::where('date', date('Y-m-d'))->get();
$data = collect($meals_day)->groupBy(['meal_id', 'meal_variable_id']);

and this one:

$meals_day = DayMeal::where('date', date('Y-m-d'))
    ->get()
    ->groupBy(['meal_id', 'meal_variable_id']);

but this code return 5 rows without the the quantity:

Illuminate\Support\Collection {#1409 ▼
  #items: array:5 [▼
    1 => Illuminate\Support\Collection {#1445 ▼
      #items: array:1 [▶]
      #escapeWhenCastingToString: false
    }
    5 => Illuminate\Support\Collection {#1447 ▼
      #items: array:1 [▶]
      #escapeWhenCastingToString: false
    }
    7 => Illuminate\Support\Collection {#1449 ▼
      #items: array:1 [▶]
      #escapeWhenCastingToString: false
    }
    9 => Illuminate\Support\Collection {#1451 ▼
      #items: array:1 [▶]
      #escapeWhenCastingToString: false
    }
    6 => Illuminate\Support\Collection {#1453 ▼
      #items: array:1 [▶]
      #escapeWhenCastingToString: false
    }
  ]
  #escapeWhenCastingToString: false
}

how can I get 5 rows and with quantity for duplicated rows depending on 2 columns (meal_id and meal_variable_id)?

CodePudding user response：

You are using collection group by, i would instead utilise SQL, which is a little less convenient but more powerfull.

DayMeal::where('date', now()->format('Y-m-d'))
    ->groupBy(['meal_id', 'meal_variable_id', 'date'])
    ->select('meal_id', 'meal_variable_id', 'date', DB::raw('count(id)'))
    ->get();

Notice, not using get() or first() to execute the query, before the last step, which is different from your approach. Depending if you run your SQL in strict mode, you can have trouble selecting columns which are not aggregate or in the group by. Which can make your models not contain columns, not in the select statement.