title: laravel how to reduce queries for count, eager load?
body: <p>Say I have 3 models </p>
  
  <ul>
  <li><code>Users</code>  </li>
  <li><code>Attendees</code>  </li>
  <li><code>ScheduleProgramSegments</code></li>
  </ul>
  
  <p><code>Attendees</code> belongs to <code>Users</code> </p>
  
  <ul>
  <li><code>User</code> hasMany <code>Attendees</code></li>
  </ul>
  
  <p><code>Bookings</code> is a pivot relation  </p>
  
  <ul>
  <li><code>Attendees</code> belongsToMany <code>ScheduledProgramSegments</code>  (pivot table = <code>bookings</code>)</li>
  </ul>
  
  <p>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 <code>scheduled_program_segment_id</code>...I do not know how to get them all in one query...eager load or otherwise.</p>
  
  <p><strong>Queries being run (shown by debugbar)</strong></p>
  
  <pre><code>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'
  </code></pre>
  
  <p><strong>EDIT</strong></p>
  
  <p><strong>Eager loading in controller</strong></p>
  
  <pre><code>$programs = ScheduledProgram::with('scheduledProgramSegments.registeredAttendees','scheduledProgramSegments.attendees')-&gt;where('registration_start_date', '&lt;=', $today)
                              -&gt;where('end_date',                '&gt;=',  $today)
                              -&gt;get();
  
  $user-&gt;load('attendees', 'attendees.scheduledProgramSegments')-&gt;get();
  
  
  
  return View::make('user/registration/edit', compact('user','programs'));
  </code></pre>
  
  <p><strong>ScheduledProgram Model</strong></p>
  
  <pre><code>     public function scheduledProgramSegments()
    {
      return $this-&gt;hasMany('ScheduledProgramSegment');
    }
    public function program()
    {
      return $this-&gt;belongsTo('Program');
    }
    public function programSession()
    {
      return $this-&gt;belongsTo('ProgramSession');
    }
    public function attendees()
    {
      return $this-&gt;belongsToMany('Attendee', 'prog_bookings')-&gt;withPivot('registered','paid')-&gt;withTimestamps();
      //return $this-&gt;hasManyThrough('Attendee', 'ScheduledProgramSegment','id','id' );
    }
  
    public function registeredAttendees()
    {
        //return $this-&gt;scheduledProgramSegments-&gt;attendees()-&gt;wherePivot('registered', 1);
        return $this-&gt;attendees()-&gt;wherePivot('registered', 1);
    }
    public function getRegisteredCountAttribute()
    {
      //return $this-&gt;registeredAttendees-&gt;count();
      //Consider changing this to calculate the max registered of the child programs
      $largest = 0;
      $segments = $this-&gt;scheduledProgramSegments()-&gt;get();
  //return $segments-&gt;first()-&gt;registered_count;
      foreach($segments as $segment){
        $largest = ($segment-&gt;registered_count &gt; $largest) ? $segment-&gt;registered_count : $largest;
      }
      return $largest;
    }
  </code></pre>
  
  <p><strong>Attendee Model</strong></p>
  
  <pre><code>public function user()
  {
      return $this-&gt;belongsTo('User');
  }
  
  public function scheduledProgramSegments()
  {
      return $this-&gt;belongsToMany('ScheduledProgramSegment', 'bookings')-&gt;withPivot('paid','registered')-&gt;withTimestamps();
  }
  </code></pre>
  
  <p><strong>View</strong></p>
  
  <pre><code>  &lt;td class="cap"&gt;{{{$program-&gt;registered_count . '/' . $program-&gt;max_attendees}}}&lt;/td&gt;
  </code></pre>