2

Say I have 3 models

  • Users
  • Attendees
  • ScheduleProgramSegments

Attendees belongs to Users

  • User hasMany Attendees

Bookings is a pivot relation

  • Attendees belongsToMany ScheduledProgramSegments (pivot table = bookings)

I am getting the count of the pivot relation and I have about 600 too many queries because it keeps querying the count for each scheduled_program_segment_id...I do not know how to get them all in one query...eager load or otherwise.

Queries being run (shown by debugbar)

select * from `scheduled_program_segments` where `scheduled_program_segments`.`scheduled_program_id` = '477'410μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1392' and `bookings`.`registered` = '1'450μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1392' and `bookings`.`registered` = '1'390μs select * from `scheduled_program_segments` where `scheduled_program_segments`.`scheduled_program_id` = '478'420μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1393' and `bookings`.`registered` = '1'390μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1393' and `bookings`.`registered` = '1'390μs select * from `program_sessions` where `program_sessions`.`id` = '23' limit 1390μs select * from `scheduled_program_segments` where `scheduled_program_segments`.`scheduled_program_id` = '478'450μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1393' and `bookings`.`registered` = '1'390μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1393' and `bookings`.`registered` = '1'380μs select * from `scheduled_program_segments` where `scheduled_program_segments`.`scheduled_program_id` = '481'730μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1396' and `bookings`.`registered` = '1'400μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1396' and `bookings`.`registered` = '1'390μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1397' and `bookings`.`registered` = '1'390μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1398' and `bookings`.`registered` = '1'390μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1399' and `bookings`.`registered` = '1'390μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1400' and `bookings`.`registered` = '1'380μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1401' and `bookings`.`registered` = '1'380μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1402' and `bookings`.`registered` = '1'440μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1403' and `bookings`.`registered` = '1'380μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1404' and `bookings`.`registered` = '1'380μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1405' and `bookings`.`registered` = '1'380μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1406' and `bookings`.`registered` = '1'380μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1407' and `bookings`.`registered` = '1'380μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1408' and `bookings`.`registered` = '1'380μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1409' and `bookings`.`registered` = '1'380μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1410' and `bookings`.`registered` = '1'380μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1411' and `bookings`.`registered` = '1'380μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1412' and `bookings`.`registered` = '1'430μs select count(*) as aggregate from `attendees` inner join `bookings` on `attendees`.`id` = `bookings`.`attendee_id` where `bookings`.`scheduled_program_segment_id` = '1413' and `bookings`.`registered` = '1'

EDIT

Eager loading in controller

$programs = ScheduledProgram::with('scheduledProgramSegments.registeredAttendees','scheduledProgramSegments.attendees')->where('registration_start_date', '<=', $today) ->where('end_date', '>=', $today) ->get(); $user->load('attendees', 'attendees.scheduledProgramSegments')->get(); return View::make('user/registration/edit', compact('user','programs'));

ScheduledProgram Model

 public function scheduledProgramSegments() { return $this->hasMany('ScheduledProgramSegment'); } public function program() { return $this->belongsTo('Program'); } public function programSession() { return $this->belongsTo('ProgramSession'); } public function attendees() { return $this->belongsToMany('Attendee', 'prog_bookings')->withPivot('registered','paid')->withTimestamps(); //return $this->hasManyThrough('Attendee', 'ScheduledProgramSegment','id','id' ); } public function registeredAttendees() { //return $this->scheduledProgramSegments->attendees()->wherePivot('registered', 1); return $this->attendees()->wherePivot('registered', 1); } public function getRegisteredCountAttribute() { //return $this->registeredAttendees->count(); //Consider changing this to calculate the max registered of the child programs $largest = 0; $segments = $this->scheduledProgramSegments()->get(); //return $segments->first()->registered_count; foreach($segments as $segment){ $largest = ($segment->registered_count > $largest) ? $segment->registered_count : $largest; } return $largest; }

Attendee Model

public function user() { return $this->belongsTo('User'); } public function scheduledProgramSegments() { return $this->belongsToMany('ScheduledProgramSegment', 'bookings')->withPivot('paid','registered')->withTimestamps(); }

View

 <td class="cap">{{{$program->registered_count . '/' . $program->max_attendees}}}</td>
Improve this question
7
  • 1
    Show the code please. Commented Mar 30, 2017 at 17:07
  • 2
    You have an N+1 issue. You're not eager loading your bookings from your ScheduledProgramSegment somewhere. Commented Mar 30, 2017 at 17:13
  • @EricTucker yes, I eager load ` $user->load('attendees', 'attendees.scheduledProgramSegments')->get();` but I think it does not do the count Commented Mar 30, 2017 at 17:19
  • @AlexeyMezenin I have updated question with code :) Commented Mar 30, 2017 at 17:30
  • What version of Laravel are you using? Commented Mar 30, 2017 at 17:55

1 Answer 1

Reset to default
1

Change getRegisteredCountAttribute function to

 public function getRegisteredCountAttribute() { //return $this->registeredAttendees->count(); //Consider changing this to calculate the max registered of the child programs $largest = 0; $segments = $this->with('scheduledProgramSegments')->get(); // this is the line to change. this will prevent the N+1 query problem. //return $segments->first()->registered_count; foreach($segments as $segment){ $largest = ($segment->registered_count > $largest) ? $segment->registered_count : $largest; } return $largest;

}

and this too

 public function registeredAttendees() { //return $this->scheduledProgramSegments->attendees()->wherePivot('registered', 1); return $this->with('attendees')->wherePivot('registered', 1); }
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I had to remove the ->get() from $segments. Otherwise it worked! THANKS!
actually...it doesn't work. I thought it was working because the number of queries went down but that was because the query wasn't running at all. :(

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.