CODEWITHSUNDEEP
cakephpcakephp-2.0
BadHorsie
BadHorsie

Reputation: 14564

CakePHP - How to use SQL NOW() in find conditions

I am having trouble getting any find operations to work using the SQL function NOW() in my conditions.

I am effectively trying to build a find query that says:

Desired SQL:

WHERE (NOW() BETWEEN Promotion.start AND Promotion.end) AND Promotion.active = 1

I have tried many combinations but no matter what I do when using NOW() in the condition, it doesn't work because the query Cake builds puts ' quotation marks around the model fields so they are interpreted by MySQL as a string.

$this->find('all', array(
    'conditions' => array(
        '(NOW() BETWEEN ? AND ?)' => array('Promotion.start', 'Promotion.end'),
        'Promotion.active' => 1
    )
));

CakePHP created SQL:

Notice the single quotes around the model fields in the BETWEEN(), so they are treated as strings.

WHERE (NOW() BETWEEN 'Promotion.start' AND 'Promotion.end') AND `Promotion`.`active` = '1'

This doesn't work either.

$this->find('all', array(
    'conditions' => array(
        'NOW() >=' => 'Promotion.start',
        'NOW() <=' => 'Promotion.end',
        'Promotion.active' => 1
    )
));

I know why these solutions don't work. It's because the model fields are only treated as such if they are the array key in the conditions, not the array value.

I know I can get this to work if I just put the whole BETWEEN() condition as a string:

$this->find('all', array(
    'conditions' => array(
        'NOW() BETWEEN Promotion.start AND Promotion.end',
        'Promotion.active' => 1
    )
));

Another example of the same problem is, which is simpler to understand:

Desired SQL:

WHERE Promotion.start > NOW() AND Promotion.active = 1

So I try this:

$this->find('all', array(
    'conditions' => array(
        'Promotion.start >' => 'NOW()',
        'Promotion.active' => 1
    )
));

And again it doesn't work because Cake puts ' quotations around the NOW() part.

CakePHP created SQL:

WHERE `Promotion`.`start` > 'NOW()' AND `Promotion`.`active` = '1''

Upvotes: 0

Views: 2637

Answers (2)

RichardAtHome
RichardAtHome

Reputation: 4313

Better to not use NOW() as its a function and functions don't use indexes. A better solution would be:

$this->find('all', array(
    'conditions' => array(
        "'" . date('Y-m-d') . "' BETWEEN Promotion.start AND Promotion.end",
        'Promotion.active' => 1
    )
));

Upvotes: 0

ADmad
ADmad

Reputation: 8100

$this->find('all', array(
    'conditions' => array(
        'NOW() BETWEEN Promotion.start AND Promotion.end',
        'Promotion.active' => 1
    )
));

Upvotes: 1

Related Questions