title: Sub-queries in fields and where in CakePHP 2.3
body: <p>How can I dot that in CakePHP?
  I try everything I know, but doesn't work:</p>
  
  <pre><code>        select
              card_type
              ,sum (case when used = 'Y' then (select amount from auth a1 where a1.origid = a.pnref and trxtype = 'D') else amount end) as total
          from    auth a
          where
              add_date between '$this-&gt;date1' and '$this-&gt;date2'
              and     (trxtype = 'S' or trxtype = 'F')
              and     user_num = $this-&gt;user_num
              and     pnref not in (select    origid
                                      from        auth a
                                      where       add_date between '$this-&gt;date1' and '$this-&gt;date2'
                                      and trxtype = 'V')
          group by card_type
          order by 1
  </code></pre>
  
  <ul>
  <li>Sub-query in fields</li>
  <li>Sub-query in where</li>
  </ul>
  
  <hr>
  
  <p><strong>[I try this]</strong>:<br /></p>
  
  <pre><code>    $conditionsSubQuery['"Auth2"."add_date BETWEEN ? AND ?"'] = array($inicial_date, $final_date);
      $conditionsSubQuery['"Auth2"."trxtype"'] = 'V';
  
      $db = $this-&gt;User-&gt;getDataSource();
      $subQuery = $db-&gt;buildStatement(
          array(
              'fields'     =&gt; array('"Auth2"."origid"'),
              'table'      =&gt; 'auth',
              'alias'      =&gt; 'Auth2',
              'limit'      =&gt; null,
              'offset'     =&gt; null,
              'joins'      =&gt; array(),
              'conditions' =&gt; $conditionsSubQuery,
              'order'      =&gt; null,
              'group'      =&gt; null
              ),
          $this-&gt;Auth
      );
  
      $subQuery = ' "Auth"."pnref" NOT IN (' . $subQuery . ') ';
      $subQueryExpression = $db-&gt;expression($subQuery);
  
      $conditions[] = $subQueryExpression;
      $conditions[] = array(
          'Auth.date BETWEEN ? and ?' =&gt; array($inicial_date, $final_date),
          'OR' =&gt; array(
              'Auth.trxtype' =&gt; 'S',
              'Auth.trxtype' =&gt; 'F'
          ),
          'Auth.user_num' =&gt; $user_num
      );
  
      $fields = array(
          'Auth.card_type',
          "sum (case when Auth.used = 'Y' then (select Auth2.amount from auth Auth2 where Auth2.origid = Auth.pnref and Auth.trxtype = 'D') else Auth.amount end) as Auth.total"
      );
  
      $group = array('Auth.card_type');
      $order = array(1);
  
      return $this-&gt;Auth-&gt;find('all', compact('fields', 'conditions', 'group', 'order'));
  </code></pre>
  
  <hr>
  
  <p><strong>[ERROR]</strong><br /></p>
  
  <pre><code>Database Error
  Error: SQLSTATE[42601]: Syntax error: 7 ERRO: erro de sintaxe em ou próximo a "." LINE 1: ... Auth.trxtype = 'D') else Auth.amount end) as Auth.total, "U... ^
  
  SQL Query: SELECT "Auth"."card_type" AS "Auth__card_type", sum (case when Auth.used = 'Y' then (select Auth2.amount from auth Auth2 where Auth2.origid = Auth.pnref and Auth.trxtype = 'D') else Auth.amount end) as Auth.total, "User"."user_num" AS "User__user_num" FROM "public"."system_users" AS "User" WHERE "Auth"."pnref" NOT IN (SELECT "Auth2"."origid" FROM auth AS "Auth2" WHERE "Auth2"."add_date BETWEEN '2013/02/19 06:00:00' AND '2013/02/22 10:34:17'" AND "Auth2"."trxtype" = 'V' ) AND (("Auth"."date" BETWEEN '2013/02/19 06:00:00' and '2013/02/22 10:34:17') AND ("Auth"."trxtype" = 'F') AND ("Auth"."user_num" = 68)) GROUP BY "Auth"."card_type" ORDER BY "1" ASC
  
  Notice: If you want to customize this error message, create app\View\Errors\pdo_error.ctp
  </code></pre>
  
  <hr>
  
  <p>Anyway, thanks.</p>