title: Cake PHP3 subquery in Join statement
body: <p>I need to use <code>cake php</code> method to build this custom query but is that possible to make this sub query in cake stranded way ? </p>
  
  <pre><code>SELECT
                      m.chat_id AS chat_id,
                      m.message AS message,
                      m.created AS created,
                      g.title AS title,
                      u.id AS id,
                      u.name AS name,
                      g.id AS gig_id
                    FROM message m
                    NATURAL JOIN (
                      SELECT   chat_id, MAX(id) AS id
                      FROM     message
                      GROUP BY chat_id
                    ) t
                    RIGHT JOIN chat c ON c.id = t.chat_id
                    INNER JOIN application a ON a.chat_id = m.chat_id
                    INNER JOIN gig g ON g.id = a.gig_id
                    INNER JOIN user u ON u.id = a.user_id
                    WHERE (g.status = 1) 
                    ORDER BY m.created DESC
  </code></pre>
  
  <p>My code is something like below</p>
  
  <pre><code>$this-&gt;loadModel('Message');
          $dataField = $this-&gt;Message-&gt;find()
              -&gt;hydrate(false)
              -&gt;select(['Message.chat_id', 'Message.message', 'Message.created', 'u.id', 'u.name', 'g.id'])
              -&gt;join([
                  'a' =&gt; [
                      'table' =&gt; 'application',
                      'type' =&gt; 'INNER',
                      'conditions' =&gt; 'a.chat_id = chat_id',
                      ],
                  'g' =&gt; [
                      'table' =&gt; 'gig',
                      'type' =&gt; 'INNER',
                      'conditions' =&gt; 'g.id = a.gig_id',
                      ],
                  'u'  =&gt; [
                      'table' =&gt; 'user',
                      'type' =&gt; 'INNER',
                      'conditions' =&gt; 'u.id = a.user_id',
                  ],
  
              ])
              -&gt;where(['g.status' =&gt; 1])
              -&gt;order(['Message.created' =&gt; 'DESC']);
  </code></pre>
  
  <p>I need to know implement   </p>
  
  <pre><code>NATURAL JOIN (
                      SELECT   chat_id, MAX(id) AS id
                      FROM     message
                      GROUP BY chat_id
                    ) t
                    RIGHT JOIN chat c ON c.id = t.chat_id
  </code></pre>
  
  <p>In Cake way. Thanks.</p>