Laravel Eloquent wherehas with condition-CodePudding

I have a gebruikers (meaning users) table. Gebruikers are grouped in a team (many to many relationship). Gebruikers can have a breakfast, diner and supper. They eat together with their team colleagues.

Meals are registered in a mealregistration table. So there is also a many to many relationship between the gebruikers table and the mealregistration table. A gebruiker can have many mealregistrations, a mealregistration has many gebruikers.

Not every team member takes every meal. Some gebruikers only have breakfast, or dinner, or supper, or any combination of meals.

This is my code for saving a mealregistration

$userid = Auth::user()->id;
    
    $nieuwemaaltijd = ModelsMaaltijdregistratie::create([
        'user_id' => $userid,
        'datum' => $this->datum,
        'afdeling_id' => $this->selectedAfdeling,
        
        'type' => $this->type,
        'prijs' => appsetting($this->datum,$this->type)
       
    ]);
    $nieuwemaaltijd->gebruikers()->sync($this->selectedGebruikers);

the user_id is the id of the teamleader making the registration, afdeling_id stands for the team, type is breakfast, diner or supper and price is the money charged for the meal (I made a function in a helper class for that).

I'm asked to provide a list of users that take more than one meal a day and how many days (in a certain period) they take more than one meal.

So for example if gebruiker x takes breakfast and diner on Monday, only supper on Thursday and all three meals on Wednesday, he should be in the list with count 2 (since he took more than one meal on 2 days).

I'm getting close with:

$gebruikers = Gebruiker::whereHas('maaltijdregistraties', function ($query) {
        $query->groupBy('datum')
        ->havingRaw('COUNT(*) > 1'); })->get();

It gives me a list with all the gebruikers that have at least one day on which the took more than one meal.

But how can I display the number of days that they took more than one meal?

Thanks!

CodePudding user response：

You may take a look at counting related models on the Laravel documentation.

$gebruikers = Gebruiker::withCount(['maaltijdregistraties' => function (Builder $query) {
    $query->groupBy('datum')->havingRaw('COUNT(*) > 1');
}])->get();

echo $gebruikers[0]->maaltijdregistraties_count;

CodePudding user response：

I succeeded in my goal by changing my code to the following:

 $gebruikers = Gebruiker::whereHas('maaltijdregistraties', function ($query) {
            $query->groupBy('datum')
            ->havingRaw('COUNT(*) > 1')
        
   ; })
   
->with(['maaltijdregistraties' => function ($query) {
    $query->groupBy('datum')
    ->havingRaw('COUNT(*) > 1')
    
    ;
}])
    ->get();

The blade code is

<ul>
            @foreach ($gebruikers as $gebruiker)
                <li>{{$gebruiker->first}} {{$gebruiker->last}} {{$gebruiker->maaltijdregistraties->groupBy('datum')->count()}}</li>
                
            @endforeach
           </ul>