title: Yii2 - Query count per day to ActiveRecord
body: <p>I have the following table structure and using Yii2 <code>ActiveRecord</code> methods I'd like to extract the number of bookings (<code>OrderLine</code>) a supplier has for each day for the next week (0 entries also required). So some way of getting a row per day per supplier, with <code>num_bookings</code> or potentially <code>0</code> depending on the supplier.</p>
  
  <pre><code> /--------------------\                  /------------\
   | OrderLine          |------------------|Availability| 
   |--------------------| 0..n           1 |------------|
   |ID {PK}             |                  |ID {PK}     |
   |availabilityID {FK} |                  |start       |
   |line_status         |                  \------------/
   |supplierID   {FK}   |
   \--------------------/
            | 1
            |
            |
            | 1
       /----------\
       | Supplier |
       |----------|
       |ID {PK}   |
       \----------/
  </code></pre>
  
  <p>Querying the database directly, using DAO, with the following SQL gives me (almost) the desired result,</p>
  
  <pre><code>select count(ol.ID) as num_bookings, 
         day(from_unixtime(a.start)) as order_day,
         ol.supplierID
  from order_line ol left join 
  availability a on ol.availabilityID = a.ID
  where ol.line_status = "booked"
  and a.start &gt;= 1451952000   //magic number for midnight today
  and a.start &lt;= 1452556800   //magic number for seven days from now
  group by order_day, ol.supplierID;
  </code></pre>
  
  <p>something along the lines of</p>
  
  <pre><code>------------------------------------
  | num_bookings|order_day|supplierID|
  ------------------------------------
  | 1           | 5       | 3        |
  | 2           | 5       | 7        |
  | 1           | 6       | 7        |
  | 1           | 7       | 7        |
  ------------------------------------       
  </code></pre>
  
  <p>So there should be entries of <code>0</code> for the days the given <code>Supplier</code> has no bookings, like so</p>
  
  <pre><code>------------------------------------
  | num_bookings|order_day|supplierID|
  ------------------------------------
  | 1           | 5       | 3        |
  | 0           | 6       | 3        |
  | 0           | 7       | 3        |
  | 2           | 5       | 7        |
  | 1           | 6       | 7        |
  | 1           | 7       | 7        |
  ------------------------------------  
  [days 8+ omitted for brevity...]
  </code></pre>
  
  <p>I've got some php/Yii code which will [eventually] give me something similar but involves multiple queries and database connections as follows,</p>
  
  <pre><code>$suppliers = Supplier::find()-&gt;all(); // get all suppliers
  
  $start = strtotime('tomorrow');
  $end = strtotime('+7 days', $start);  // init times
  
  // create empty assoc array with key for each of next 7 days
  $booking_counts[date('D j', $start)] = 0;
  for ($i=1; $i&lt;7; ++$i) {
     $next = strtotime('+'.$i." days", $start);
     $booking_counts[date('D j', $next)] = 0;
  }
  
  foreach ($suppliers as $supplier) {
       $bookings = OrderLine::find()
                      -&gt;joinWith('availability')
                      -&gt;where(['order_line.supplierID' =&gt; $supplier-&gt;ID])
                      -&gt;andWhere(['&gt;=', 'availability.start', $start])
                      -&gt;andWhere(['&lt;=', 'availability.start', $end])
                      -&gt;andWhere(['order_line.line_status' =&gt; 'booked'])
                      -&gt;orderBy(['availability.start' =&gt; SORT_ASC])
                      -&gt;all();
  
      $booking_count = $booking_counts;
      foreach ($bookings as $booking) {
          $booking_count[date('D j', $booking-&gt;availability-&gt;start)] += 1;
      }
  
  }
  </code></pre>
  
  <p>This gives me an array for each supplier with the count stored under the appropriate day's index but that feels quite inefficient.</p>
  
  <p>Can I refactor this code to return the desired data with fewer database calls and less 'scaffold' code?</p>