ntseles's avatar

Nested foreach loops with eloquent

Hi guys/gals! Bit of a noob question here :)

  1. I have a (list) or array of location names.
  2. I have an appointments table with a row named 'locations'.

What I need to do is: for-each location, query and count how many appointments are at a specific location. Here's my current non-working code

$locations = myLocationArray;
foreach ($locations as $location) 
{
$q= Appointment::where('appt_type', 'Install')->where('appt_location', $location)->get();
}
dd($q);

Of course I'm not getting what I'm expecting because It only return the last iteration.

Thanks in advance for any help!

0 likes
6 replies
tykus's avatar

Better to establish a proper relationship between Location and Appointment models; e.g. an Appointment belongsTo a Location - appt_location should instead be a foreign key ID of a Location, i.e. location_id

ntseles's avatar

I will definitely do that. Thank you for the input. Assuming my relationships are up to par, how would you structure the loop to count?

MohamedTammam's avatar

@ntseles What the relationship, the answer would be.

$locations = Location::whereIn('id', $locationsArray)->withCount('appointments')->get();
Jsanwo64's avatar
Jsanwo64
3 years ago
Best Answer
Level 11

@ntseles try

$locations = ['location1', 'location2', 'location3']; // Replace with your actual location array

$result = [];

foreach ($locations as $location) {
    $count = Appointment::where('appt_type', 'Install')
        ->where('appt_location', $location)
        ->count();

    $result[$location] = $count;
}

dd($result);
1 like
tykus's avatar

@ntseles this solution is awfully inefficient because you are executing as many queries as there are elements in $locations; the $result could have been achieved to a single query:

$result = Appointment::query()
    ->selectRaw('appt_location, COUNT(*) as count')
    ->where('appt_type', 'Install')
    ->whereIn('appt_location', $locations)
    ->groupBy('appt_location')
    ->pluck('count', 'appt_location');

Please or to participate in this conversation.