代码之家  ›  专栏  ›  技术社区  ›  Salines

cakephp count()返回错误的结果?

  •  0
  • Salines  · 技术社区  · 7 年前

    CaKEPHP 3.614

    此代码复制错误的号码:

            $where = [
                'Postings.source' => $source,
                'Postings.approved' => 1,
                'Postings.deleted' => 0,
                'Postings.disabled' => 0
            ];
    
            if ($source !== null) {
                $where['Postings.created >='] = '(NOW() - INTERVAL 3 DAY)';
            }
            $count = $this->Postings
                ->find()
                ->where($where)
                ->count();
            debug($count); exit;
    
           // 77568 total of all records
    ########## DEBUG ##########
    [
        'Postings.source' => 'xzy',
        'Postings.approved' => (int) 1,
        'Postings.deleted' => (int) 0,
        'Postings.disabled' => (int) 0,
        'Postings.created >=' => '(NOW() - INTERVAL 3 DAY)'
    ]
    
    //SQL produced by this query:
    
    SELECT (COUNT(*)) AS `count` 
    FROM postings Postings 
    WHERE (
       Postings.source = 'xzy' 
       AND Postings.approved = 1 
       AND Postings.deleted = 0 
       AND Postings.disabled = 0 
       AND Postings.created >= '(NOW() - INTERVAL 3 DAY)' // <<<< with quotes
    )
    

    但原始SQL查询:

    SELECT COUNT(*) as `count
    FROM `postings` 
    WHERE `source` = 'xzy' 
    AND `approved` = 1 
    AND `deleted` = 0 
    AND `disabled` = 0 
    AND `created` >= (NOW() - INTERVAL 3 DAY) // <<< without quotes
    // return correct num 2119
    

    如何修复?

    2 回复  |  直到 7 年前
        1
  •  2
  •   ndm    7 年前

    值位于 key => value 条件总是受制于绑定/强制转换/转义,除非它是表达式对象。看看生成的查询,您的SQL代码段将以字符串文字结尾,即:

    created >= '(NOW() - INTERVAL 3 DAY)'
    

    长话短说,使用一个表达,或者是一个原始表达:

    $where['Postings.created >='] = $this->Postings->query()->newExpr('NOW() - INTERVAL 3 DAY');
    

    或者使用函数生成器:

    $builder = $this->Postings->query()->func();
    $where['Postings.created >='] = $builder->dateAdd($builder->now(), -3, 'DAY');
    

    也见

        2
  •  0
  •   kicaj    7 年前

    您应该使用查询生成器并添加到 select 方法 count 功能。

    这里描述了一切: https://book.cakephp.org/3.0/en/orm/query-builder.html#using-sql-functions