CODEWITHSUNDEEP
phpmysqllaraveleloquent
Phil
Phil

Reputation: 2236

laravel how to reduce queries for count, eager load?

Say I have 3 models

Attendees belongs to Users

Bookings is a pivot relation

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>

Upvotes: 2

Views: 957

Answers (1)

oseintow
oseintow

Reputation: 7381

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);
 }

Upvotes: 1

Related Questions