Yii2 - Model: find where two different dates are in range

Question

I want to select items which are in a range of two different dates (datetime type in Database). In this case want to select all items in range of dateStart and dateEnd.

Example table data:

id | dateStart  | dateEnd
---+------------+-----------+
3  | 2017-01-05 | 2017-01-07
6  | 2017-01-06 | 2017-01-10
8  | 2017-01-05 | 2017-01-11
11 | 2017-01-03 | 2017-01-04
12 | 2017-01-04 | 2017-01-10
15 | 2017-01-04 | 2017-01-18
17 | 2017-01-08 | 2017-01-10
25 | 2017-01-12 | 2017-01-15
31 | 2017-01-11 | 2017-01-24

Image for clarification:

---

Please note that I don't want to search between dates like:

Model::find()
    ->where(['between', 'dateStart', "2017-01-06", "2017-02-11"])
    ->all();

.. because between is not in range.

Using between results in this wrong output:

id | dateStart  | dateEnd
---+------------+-----------+
6  | 2017-01-06 | 2017-01-10
8  | 2017-01-05 | 2017-01-11
11 | 2017-01-03 | 2017-01-04
12 | 2017-01-04 | 2017-01-10
15 | 2017-01-04 | 2017-01-18

E.g. preferred ouput:

The selected (based on the table data above) items by searching startDate >= 2017-01-05and endDate <= 2017-01-11 should look like this:

id | dateStart  | dateEnd
---+------------+-----------+
3  | 2017-01-05 | 2017-01-07
6  | 2017-01-06 | 2017-01-10
8  | 2017-01-05 | 2017-01-11
12 | 2017-01-04 | 2017-01-10
15 | 2017-01-04 | 2017-01-18
17 | 2017-01-08 | 2017-01-10
31 | 2017-01-11 | 2017-01-24

---

Note: I'am looking for an answer based on Yii2 ActiveRecord models.

Answer 1

Using ActiveRecord you can do like this sequence of clauses ->orWhere:

$dateStart = '2017-01-05';
$dateEnd = '2017-01-11';

Model::find()
   ->orWhere(['between', 'dateEnd', $dateStart , $dateEnd]) 
   ->orWhere(['between', 'dateStart', $dateStart , $dateEnd]) 
   ->orWhere(['and', "dateEnd>='".$dateEnd."'", "dateStart<='".$dateStart ."'"])
   ->all();