Sub-queries in fields and where in CakePHP 2.3

Question

How can I dot that in CakePHP? I try everything I know, but doesn't work:

        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->date1' and '$this->date2'
            and     (trxtype = 'S' or trxtype = 'F')
            and     user_num = $this->user_num
            and     pnref not in (select    origid
                                    from        auth a
                                    where       add_date between '$this->date1' and '$this->date2'
                                    and trxtype = 'V')
        group by card_type
        order by 1

Sub-query in fields
Sub-query in where

[I try this]:

    $conditionsSubQuery['"Auth2"."add_date BETWEEN ? AND ?"'] = array($inicial_date, $final_date);
    $conditionsSubQuery['"Auth2"."trxtype"'] = 'V';

    $db = $this->User->getDataSource();
    $subQuery = $db->buildStatement(
        array(
            'fields'     => array('"Auth2"."origid"'),
            'table'      => 'auth',
            'alias'      => 'Auth2',
            'limit'      => null,
            'offset'     => null,
            'joins'      => array(),
            'conditions' => $conditionsSubQuery,
            'order'      => null,
            'group'      => null
            ),
        $this->Auth
    );

    $subQuery = ' "Auth"."pnref" NOT IN (' . $subQuery . ') ';
    $subQueryExpression = $db->expression($subQuery);

    $conditions[] = $subQueryExpression;
    $conditions[] = array(
        'Auth.date BETWEEN ? and ?' => array($inicial_date, $final_date),
        'OR' => array(
            'Auth.trxtype' => 'S',
            'Auth.trxtype' => 'F'
        ),
        'Auth.user_num' => $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->Auth->find('all', compact('fields', 'conditions', 'group', 'order'));

[ERROR]

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

Anyway, thanks.

Sub-queries in fields and where in CakePHP 2.3

Answers (1)

Related Questions