Level 73
Just a friendly advice, try to use English names for tables and columns. It not only follows the laravel naming convention, it also makes it much easier for others who only speaks English to help you.
Dec 22, 2021
6
Level 1
Stuck on eloquent related model query
I'm working on a registration system for a facility for disabled people. One of the things the team leaders are registering is the meals taken by the residents.
I have a gebruikers (= the residents) 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 gebruiker 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).
No problem so far.
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 experimenting with:
$gebruikers = Gebruiker::whereHas('maaltijdregistraties', function ($query) { $query->groupBy('datum') ->havingRaw('COUNT(*) > 1'); })->get();
But that does not seem to give the results I was hoping for.
Thanks for your input!
Level 1
@Tray2 Thanks for the advice!
Level 102
Has and whereHas takes a count as last parameters
Gebruiker::has('maaltijdregistraties', '>', 3)->get();
Use whereHas if you need to customize your query further
Level 1
@Sinnbeck I think your query will return all users that has more than 3 'maaltijdregistraties'.
But what I need is the number of days that a user took more than one meal (per day).
Level 102
@Bervetuna I would write that with pure sql. Once I get a working query I would convert it to a laravel query.
Level 1
I found a working solution for this with the following code. But maybe there is a more elegant approach?
//Controller: $gebruikers = Gebruiker::has('maaltijdregistraties')->orderBy('last','asc')->get(); //Blade: @foreach ($gebruikers as $gebruiker)
<div>
<div class="text-sm font-medium text-gray-900">
{{ $gebruiker->first }} {{ $gebruiker->last }}
</div>
</div>
</div>
</td>
<td class="px-6 py-4 whitespace-nowrap">
<div class="text-sm text-gray-900">{{$gebruiker->maaltijdregistraties->groupBy('datum')->count()}} verschillende dagen</div>
</td>
<td class="px-6 py-4 whitespace-nowrap">
@php
$dagenmetmeerdan1maaltijd = 0;
@endphp
@foreach ($gebruiker->maaltijdregistraties->groupBy('datum') as $maaltijdregistratiedag)
@php
if($maaltijdregistratiedag->count() > 1){
$dagenmetmeerdan1maaltijd++;
}
@endphp
@endforeach
<div class="text-sm text-gray-900">{{$dagenmetmeerdan1maaltijd}}</div>
</td>
<td class="px-6 py-4 whitespace-nowrap">
<div class="text-sm text-gray-900">{{$today}} - {{$dagenmetmeerdan1maaltijd}} = {{$today - $dagenmetmeerdan1maaltijd}}</div>
</td>
</tr>
@endforeach
Please or to participate in this conversation.