title: Multi level ordering in cakePHP
body: <p>Why don't cakePHP include all my associations into one SQL query? The only way I have been able to do this is using "joins", but I hoped belongsTo and Containable behaviour was enough.</p>
  
  <p>Here is an example:
  Post->belongsTo->Category->belongsTo->category_type
  (All models are setup correctly and work.</p>
  
  <p>listing posts with pagination in index, I try this:</p>
  
  <pre><code>public function index() {   
      $this-&gt;paginate = array( 'contain' =&gt; array('Category' =&gt; array('CategoryType')));
      $this-&gt;set('posts', $this-&gt;paginate());
  }
  </code></pre>
  
  <p>This fetches the array correctly, but it does it in many SQLs like this:</p>
  
  <pre><code>SELECT `Post`.`id`, `Post`.`name`, `Post`.`content`, `Post`.`category_id`, `Category`.`id`, `Category`.`name`, `Category`.`category_type_id` FROM `unit_app`.`post` AS `Post` LEFT JOIN `unit_app`.`categories` AS `Category` ON (`Post`.`category_id` = `Category`.`id`) WHERE 1 = 1 LIMIT 20
  SELECT `CategoryType`.`id`, `CategoryType`.`name` FROM `unit_app`.`category_types` AS `CategoryType` WHERE `CategoryType`.`id` = 1
  SELECT `CategoryType`.`id`, `CategoryType`.`name` FROM `unit_app`.`category_types` AS `CategoryType` WHERE `CategoryType`.`id` = 2
  SELECT `CategoryType`.`id`, `CategoryType`.`name` FROM `unit_app`.`category_types` AS `CategoryType` WHERE `CategoryType`.`id` = 2
  </code></pre>
  
  <p>This makes it difficult to order this query on CategoryType.name ASC.
  Any suggestions?</p>
  
  <p>If joins are only option, do I have to unbind the models before querying?
  Will pagination work fine with joins?</p>
  
  <p>Note! this is just a small part of all models, the resulting post->index need to fetch many other models through similar associations also.</p>
  
  <p>(tested on cake 2.2.0 and v2.4.0-dev, php v5.4.11)</p>
  
  <p>UPDATE! ---------</p>
  
  <p>I just wanted to show my findings. I have now solved this without joins, but I had to re-bind in the model to get it working.</p>
  
  <p>This is basically what I did to get it to work (also with paginations and sorts):
  In Post model:
  Added a bind function:</p>
  
  <pre><code>  $this-&gt;unbindModel(array(
      'belongsTo' =&gt; array('Category')
    ));
  
    $this-&gt;bindModel(array(
      'hasOne' =&gt; array(
        'Category' =&gt; array(
          'foreignKey' =&gt; false,
          'conditions' =&gt; array('Category.id = Post.category_id')
      ),
      'CategoryType' =&gt; array(
        'foreignKey' =&gt; false,
        'conditions' =&gt; array('CategoryType.id = Category.category_type_id')
      ))));
  </code></pre>
  
  <p>Then I added this to my index in Post controller:</p>
  
  <pre><code>  $this-&gt;Post-&gt;bindCategory();
    $this-&gt;paginate = array('contain' =&gt; array('Category' ,'CategoryType'));
    $this-&gt;set('posts', $this-&gt;paginate());
  </code></pre>
  
  <p>I include table headers also just for documentation:</p>
  
  <pre><code>&lt;th&gt;&lt;?php echo $this-&gt;Paginator-&gt;sort('CategoryType.name', 'Type'); ?&gt;&lt;/th&gt;
  &lt;th&gt;&lt;?php echo $this-&gt;Paginator-&gt;sort('Category.name', 'Category'); ?&gt;&lt;/th&gt;
  </code></pre>
  
  <p>I hope this post can help others as well :)</p>
  
  <p>I am also going to test this Behaviour to see if I can omit all the bind-functions as well: <a href="https://github.com/siran/linkable/" rel="nofollow">https://github.com/siran/linkable/</a></p>
  
  <p>There are lots of plugins to cake, but cake should have a "certification" of the plugins. It is quite difficult to find the fully working and tested ones on github :)</p>
  
  <p>I also miss a site like railscasts.com just for cake :D</p>
  
  <p>/MartOn</p>