title: Subquery in CakePhp with InnerJoin
body: <p>I am trying to convert this query:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT
      pdd.pedinte_id, pdd.data, pdd.situacao as Situacao, pdd.valor_total, pdd.qtd_etiquetas, 
      (
          SELECT count(pdi.envio_id)
          FROM pedinte_item pdi
          INNER JOIN envios env ON
              pdi.envio_id = env.envio_id
          WHERE
              pdi.pedinte_id = pdd.pedinte_id AND
              env.Situacao = 2
      ) AS TemErros
  FROM pedinte pdd
  left join user usr on
      usr.user_id = pdd.user_id
  WHERE pdd.user_id IS NOT NULL AND pdd.pedinte_id IS NOT NULL;
  </code></pre>
  <p>to CakePhp:</p>
  <pre class="lang-php prettyprint-override"><code>removed code, maybe very wrong.
  </code></pre>
  <p>Without success.</p>
  <p>I have 4 tables:</p>
  <ul>
  <li><code>pedinte</code> (<code>pdd</code>)</li>
  <li><code>pedinte_item</code> (<code>pdi</code>)</li>
  <li><code>envios</code> (<code>env</code>)</li>
  <li><code>user</code> (<code>usr</code>)</li>
  </ul>
  <p><code>pedinte</code> &gt; <code>pedinte_item</code> &gt; <code>envios</code> (<code>count</code>)</p>
  <p>Cant believe, harder to do the query builder than the mysql code.</p>