2

First question I guess is "should I be using CDbCriteria at all, or am I overcomplicating things"?

Secondly if I'm ok using it, how do I achieve the following SQL query:

SELECT * FROM myTable 
WHERE cottage=3 
AND (fromDate BETWEEN '2015-01-01' AND '2015-01-11' 
OR toDate BETWEEN '2015-01-01' AND '2015-01-11');

So far I've got this:

$criteria = new \CDbCriteria();
$criteria->addCondition('cottage=' . $booking->cottage);
$criteria->addBetweenCondition('fromDate', $booking->fromDate, $booking->toDate);
$criteria->addBetweenCondition('toDate', $booking->fromDate, $booking->toDate, 'OR');
$previousBookings = BookingEngine_BookingsRecord::model()->findAll($criteria);

Which nearly gets me there, the only issue is when I dump the $criteria->condition, the brackets are in the wrong place. So I get this:

((cottage=3) AND (fromDate BETWEEN :ycp2 AND :ycp3)) OR (toDate BETWEEN :ycp4 AND :ycp5)

The cottage and fromDate are bracketed together, whereas I want the fromDate and toDate bracketed together instead.

Other than manually writing the SQL, is there a more straightforward way of doing this, I feel I'm really overdoing a seemingly easy query!

UPDATE: I did rearrange the addCondition and addBetweenCondition calls as such:

$criteria = new \CDbCriteria();
$criteria->addBetweenCondition('fromDate', $booking->fromDate, $booking->toDate);
$criteria->addBetweenCondition('toDate', $booking->fromDate, $booking->toDate, 'OR');
$criteria->addCondition('cottage=' . $booking->cottage);
$previousBookings = BookingEngine_BookingsRecord::model()->findAll($criteria);

and that did in fact solve the problem. However it does feel like it's working by accident rather than by design. I think Jørgen's answer below is a bit more straightforward, so I went with that instead.

3

Try this (untested)

$criteria = 'cottage = :cottage and ((fromdate between :d1 and :d2) or(toDate between :d1 and :d2))';
$params = array('cottage' => $booking->cottage, 'd1' => $booking->fromDate, 'd2' => $booking->toDate);
$previousBookings = BookingEngine_BookingsRecord::model()->findAll($criteria, $params);
2
  • Thanks, that works fine. I don't know why I was so resistant to writing out the sql! At least with the params it's getting some protection from SQL injection problems. Doing it this way does seem to make using the CDbCriteria object a bit pointless. I like your way better :) Jun 9 '15 at 15:40
  • Glad I could help. I find writing regular SQL easier than using the Criteria
    – fuling
    Jun 9 '15 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.