title: CakePHP Pagination with hasMany through Relationship
body: <p>I have a model relationship like this:</p>
  
  <pre><code>Person belongsTo Counselor, PersonType, Program, Term
  Person hasMany PersonStage, Text, Mail
  PersonStage belongsTo Person, Stage
  Stage hasMany PersonStage
  </code></pre>
  
  <p>Essentially, I have created a many-to-many relationship between stage and person, but the PeronStage table <code>(people_stages)</code> has some extra information, most importantly a <code>created</code> field. </p>
  
  <p>What I am attempting to do is find and paginate results, often based on the Person's <em>current</em> PersonStage, which would always be the most recent (one with <code>MAX(people_stages.created)</code>.)</p>
  
  <p>I have no idea how to accomplish this in Cake. I know in SQL the join query would be as follows, but I have no idea how to replicate this in Cake. </p>
  
  <pre><code>SELECT   *
  FROM     people AS p LEFT JOIN (people_stages AS s NATURAL JOIN (
             SELECT   person_id, MAX(created) created
             FROM     people_stages
             GROUP BY person_id
           ) t) ON s.person_id = p.id
  ORDER BY p.last_name
  </code></pre>
  
  <p>I have tried various joins, all to no avail. Here is my code without these joins:</p>
  
  <pre><code>$this-&gt;Paginator-&gt;settings = array(
      'recursive' =&gt; -1,
      'contain' =&gt; array('Counselor','Term','Program','PersonType'), 
      'fields' =&gt; array('Person.*', 'Counselor.id', 'Counselor.first_name', 'Counselor.last_name', 'Term.*', 'Program.*', 'PersonType.*'),
      'order' =&gt; array('Person.counselor_read' =&gt; 'asc', 'PersonStage.create' =&gt; 'desc'),
      'limit' =&gt; 25
  );
  $people = $this-&gt;Paginator-&gt;paginate('Person');
  </code></pre>