CakePHP 2.3中字段和子查询中的子查询

问题描述：

如何在CakePHP中将它点出来？我尝试我什么都知道，但不工作：CakePHP 2.3中字段和子查询中的子查询

 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

子查询的领域
子查询在

[我试试这个]：

$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

无论如何，感谢。

显示您尝试过的代码。 – Rikesh 2013-02-22 12:55:46

至少对于子查询（select origid ...）我会保持简单并为此使用单独的查询，然后将结果用作第二个查询的过滤器/条件 – thaJeztah 2013-02-22 13:01:36

查询中的问题非常明显，该标识符被错误地关联。错误消息的查询的相关条款是“Auth2”。“add_date BETWEEN'2013/02/19 06:00:00'AND'2013/02/22 10:34:17'”'我怀疑你有一个布尔列名为''add_date BETWEEN'2013/02/19 06:00:00'AND'2013/02/22 10:34:17'“'所以这个问题应该仅限于CakePHP语法。 – 2013-04-30 05:40:23

答

赞@Chris Traveres评论，这只是一个语法错误。

查询中的问题很明显，标识符被错误关联。错误消息的查询的相关条款是“Auth2”。“add_date BETWEEN'2013/02/19 06:00:00'和 '2013/02/22 10:34:17'”，我怀疑你有一个命名布尔列 “add_date BETWEEN‘2013年2月19日06:00:00’与‘2013年2月22日10点34分十七秒’”，所以真正的问题应该仅限于CakePHP的语法

问题解决了！谢谢你们。

CakePHP 2.3中字段和子查询中的子查询

相关推荐