CODEWITHSUNDEEP
laraveleloquent
Hasan Hatem
Hasan Hatem

Reputation: 11

Combine duplicate rows depends on 2 columns values in laravel 8

I have this table 'day_meals':

id user_id meal_id meal_variable_id date
1 7 1 1 2022-07-13
2 7 5 1 2022-07-13
3 7 7 7 2022-07-13
4 7 7 7 2022-07-13
5 7 9 9 2022-07-13
6 7 6 1 2022-07-13

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)?

Upvotes: 0

Views: 543

Answers (1)

mrhn
mrhn

Reputation: 18916

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

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.

Upvotes: 1

Related Questions