title: CakePHP: ContainableBehavior Query Structure/Joins
body: <h1>My Application's Data Model</h1>
  
  <ul>
  <li>Group hasMany User</li>
  <li>User belongsTo Group</li>
  <li>User hasMany Punch</li>
  <li>Punch belongsTo User</li>
  <li>Punch belongsTo PayPeriod</li>
  <li>PayPeriod hasMany Punch</li>
  </ul>
  
  <h1>Find call</h1>
  
  <pre class="lang-php prettyprint-override"><code>$groups = $this-&gt;Group-&gt;find('all', array( 'contain' =&gt; array(
      'User' =&gt; array(
          'Punch' =&gt; array(
              'conditions' =&gt; array(
                  'Punch.payperiod_id' =&gt; null
              )
          )
      )
  
  )));
  </code></pre>
  
  <h1>Query Debug</h1>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT `Group`.`id`, `Group`.`name` 
  FROM `pclock`.`groups` AS `Group` 
  WHERE 1 = 1 ORDER BY `name` ASC
  
  SELECT `User`.`id`, `User`.`name`, `User`.`group_id` 
  FROM `pclock`.`users` AS `User` 
  WHERE `User`.`group_id` IN (4, 3, 5, 2, 1)
  
  SELECT `Punch`.`id`, `Punch`.`user_id`, `Punch`.`time_in`, `Punch`.`time_out`, `Punch`.`payperiod_id` 
  FROM `pclock`.`punches` AS `Punch` 
  WHERE `Punch`.`payperiod_id` IS NULL AND `Punch`.`user_id` IN (1, 2, 3, 4, 5)
  </code></pre>
  
  <p>Once my application scales to hundreds of Users each with thousands of Punches, these queries can become very inefficient.  I would expect Containable to perform the following query:</p>
  
  <h2>Query I'd Prefer</h2>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT 
      Group.id, Group.name, 
      User.id, User.name, User.group_id, 
      Punch.id, Punch.user_id, Punch.time_in, Punch.time_out, Punch.payperiod_id 
  FROM groups AS Group
  LEFT JOIN users AS User 
      ON (Group.id = User.group_id)
  LEFT JOIN punches AS Punch 
      ON (User.id = Punch.user_id)
  WHERE Punch.payperiod_id IS NULL
  </code></pre>
  
  <p>Is there any way to optimize this?  The join attribute in the options array is seemingly ignored, and doing joins manually without Containable returns results that aren't hierarchical.</p>