cakephp中的mysql between语句

Question

我正在尝试从表allowances中检索行，其中当前日期位于两个字段date_from和date_to之间。下面是sql查询：

SELECT * FROM `allowances` WHERE desg_id=4  AND CURDATE() between date_from and date_to

现在，如何在cakephp2 find中编写此查询。我正在尝试这样做：

$allowance = $this->  Allowance->find('all',array(
'conditions'=>array(
'? BETWEEN ? AND ?'=>array( date('Y-m-d'), 'Allowance.date_from', 'Allowance.date_to')
)));

我得到了sql输出：

SELECT `Allowance`.`id`, `Allowance`.`date_from`, `Allowance`.`date_to`, FROM `db_demo`.`allowances` WHERE '2017-03-16' BETWEEN 'Allowance.date_from' AND 'Allowance.date_to'

这就是引号的问题。不幸的是，这个查询并不是working.whats错误。

Answer 1

您可以尝试这样做：

$this->Order->find('all', array(
    'conditions' => array(
        'id' => $id,
        'created >=' => $start_date,
        'created <=' => $end_date . ' 23:59:59',
        'my_field LIKE' => '%whatever%'
));

Answer 2

您不能以这种方式绑定标识符，因为绑定的字符串以字符串文字结尾，这在生成的查询中可以看到。在条件左侧的SQL代码片段中添加标识符：

'? BETWEEN Allowance.date_from AND Allowance.date_to' => date('Y-m-d')

或者使用表达式(尽管这并不值得这么麻烦)：

$dataSource = $this->Allowance->getDataSource();

// ...

'? BETWEEN ? AND ?' => array(
    date('Y-m-d'),
    // note that using expressions is unsafe for user input!
    $dataSource->expression('Allowance.date_from'),
    $dataSource->expression('Allowance.date_to')
)

另请参阅