<p>Try this (untested)</p>

<pre><code>$criteria = 'cottage = :cottage and ((fromdate between :d1 and :d2) or(toDate between :d1 and :d2))';
$params = array('cottage' =&gt; $booking-&gt;cottage, 'd1' =&gt; $booking-&gt;fromDate, 'd2' =&gt; $booking-&gt;toDate);
$previousBookings = BookingEngine_BookingsRecord::model()-&gt;findAll($criteria, $params);
</code></pre>

CC BY-SA 3.0
2015-06-09T15:40:20.273Z
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 :)
Steve Holland
CC BY-SA 3.0
2015-06-09T16:09:44.420Z
Glad I could help. I find writing regular SQL easier than using the Criteria
fuling
CC BY-SA 3.0
2015-06-09T15:24:18.763Z
2015-06-09T15:24:18.763Z
fuling
<p>First question I guess is "should I be using <a href="http://www.yiiframework.com/doc/api/1.1/CDbCriteria" rel="nofollow">CDbCriteria</a> at all, or am I overcomplicating things"? </p>

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

<pre><code>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');
</code></pre>

<p>So far I've got this:</p>

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

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

<pre><code>((cottage=3) AND (fromDate BETWEEN :ycp2 AND :ycp3)) OR (toDate BETWEEN :ycp4 AND :ycp5)
</code></pre>

<p>The <code>cottage</code> and <code>fromDate</code> are bracketed together, whereas I want the <code>fromDate</code> and <code>toDate</code> bracketed together instead.</p>

<p>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!</p>

<p>UPDATE: I did rearrange the <code>addCondition</code> and <code>addBetweenCondition</code> calls as such:</p>

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

<p>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.</p>

<plugin-development>
Using Yii CDbCriteria to query a table in a plugin
# Using Yii CDbCriteria to query a table in a plugin

(Asked by: username_0 on 2015-06-09)

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.

**Tags:** plugin-development


---

## 1 answers

### Answer by username_1 on 2015-06-09. Score: 3 | Accepted answer

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);

> **Comments:**
> **username_0**: 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 :)  
> **username_1**: Glad I could help. I find writing regular SQL easier than using the Criteria  

---

