How to write a where clause for ranges of numbers xyz x,y,z xy,z?

Question

MySQL

There is a database in which data for the filter is stored in string form in several formats:
10
10 or 20
10, 12, 16 or 20
10, 15, 20, 30, 40
12-29
Data for listing the number of lessons per week are displayed on the site as is, too lines.
The task is to make a selection filter with checkboxes several options and filter with the where condition:
Lessons per week
- less than 15
- 16-20
- 21-25
- 26-30
- more than 31
Tell me how to make such a condition. With the like operator, everything is not so simple, the data in the filter and in the database are different. I'm doing it in Yii2.
Here is an example code for the SchoolSearch model for making an ActiveDataProvider
query $query = School::find();
$dataProvider = new ActiveDataProvider([
'query' => $query,
'sort' => ['defaultOrder' => ['id' => SORT_DESC]]
]);
if (isset($params['lessons_week']) && count($params['lessons_week'])) {
$lessonsTimeList = array_keys($params['lessons_week']);
$query->joinWith('course')
->where( ... );
}
Or tell me how it is better to solve such tasks in Yii, so that it would be possible to store ranges of weeks in the database and then filter them with different options?

Answer 1

There is a database in which the data for the filter is stored in string form in several formats

Throw out such a database and stop doing perversions.
It must be stored in a structured ( normal ) form. As a last resort, filters can be stored in json, which can be done from ActiveQuery->where