Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-06-09T15:40:20.273Z
Answers.Text: 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 :)
Answers.UserDisplayName: Steve Holland
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-06-09T16:09:44.420Z
Answers.Text: Glad I could help. I find writing regular SQL easier than using the Criteria
Answers.UserDisplayName: fuling
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-06-09T15:24:18.763Z
Answers.LastActivityDate: 2015-06-09T15:24:18.763Z
Answers.UserDisplayName: fuling
Body: <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>
  
Tags: <plugin-development>
Title: Using Yii CDbCriteria to query a table in a plugin
ThreadText: # 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](http://www.yiiframework.com/doc/api/1.1/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  
  
  ---
  
  