Yii Выборка из базы по датам. Что не так?

Question

[Максим]

Всем привет!
Есть три таблицы: appointment, profile, event (назначения, профиль, мероприятия)

Данные:
Мероприятие 1 проходит в даты 2018-11-24 по 2018-11-25 (продолжительность 2 дня)
Мероприятие 2 проходит в даты 2018-11-25 по 2018-11-25 (продолжительность 1 день)

Формат:
Даты в базе данных хранятся в формате Y-m-d

Мне необходимо осуществить проверку на свободные даты пользователя.

Свободная дата определяется по мероприятию (таблица event). При назначении пользователя (таблица appointment) на мероприятие - делаем выборку всех назначений текущего пользователя, кроме текущего события и выполняем поиск на свободные даты по таблице с мероприятиями (таблица event). Если мы находим назначение, диапазон дат мероприятия или дата мероприятия которая совпадает/входит в даты текущего мероприятия, то выдаем ошибку о том, что пользователь уже занят в данные даты.

Я это делаю таким запросом:

$appointmentsInDates = Appointment::find()
                ->joinWith(['event', 'profile'])
                ->andWhere([Profile::tableName() . '.user_id' => $model->certification->user_id])
                ->andWhere(['!=', Event::tableName() . '.id', $model->event_id])
                ->andWhere(['>=', Event::tableName() . '.date_from', '2018-11-25'])
                ->andWhere(['<=', Event::tableName() . '.date_to', '2018-11-25'])
                ->groupBy(Event::tableName() . '.id')
                ->exists();

SQL

SELECT `appointment`.* FROM `appointment` 
LEFT JOIN `event` ON `appointment`.`event_id` = `event`.`id`
LEFT JOIN `certification` ON `appointment`.`judge_comitet_id` = `certification`.`id` 
LEFT JOIN `profile` ON `certification`.`user_id` = `profile`.`user_id` 
WHERE (`profile`.`user_id`=139) 
AND (`event`.`id` != 55) 
AND (`event`.`date_from` >= 2018-11-25) 
AND (`event`.`date_to` <= 2018-11-25) 
GROUP BY `event`.`id` 
ORDER BY `event`.`date_to`

Данный запрос работает только при условии, если у мероприятия, на которое осуществляется назначение, диапазон дат шире, чем у мероприятий на которые пользователь так же был назначен...

Однако, при назначении на Мероприятие 2, которое проходит в один день с 2018-11-25 по 2018-11-25 и имеет диапазон дат меньше, чем у мероприятий на которые пользователь так же был назначен — не находит ни одного назначения. Хотя данное назначение существует на Мероприятие 1, которое проходит в два дня с 2018-11-24 по 2018-11-25 с более широким диапазоном дат.

Как мне сделать запрос на свободные даты пользователя, чтобы он учитывал все мероприятия и проверял все диапазоны дат, даже те, которые выходят за рамки текущего мероприятия?

Благодарю!

Comments

[Дмитрий]

Добрый вечер.
В каком формате хранится дата в базе?

[Максим]

slo_nik, доброго вечера) 2018-11-24 date('Y-m-d')
Формат даты, скорее всего не причем. Потому что когда полный диапазон - работает!

[Сергей Маловичко]

Было бы здорово, если бы упростил вопрос без этих страшных картинок)

[Максим]

Сергей Маловичко, пока только так знаю как сделать и то не полностью работает(( Можете помочь?) Буду благодарен

[Сергей Маловичко]

Максим Ворожцов, телега @ mrSerg161 , попробую помочь, пока не спится

[Максим]

Сергей Маловичко, мне вот тоже не спится потому что не могу решить задачу)) Что за чудеса у меня)) Даже не говорю об оптимизации запроса)

[Сергей Маловичко]

Максим Ворожцов, пиши в телеграмм, порешаем вместе

[Dmitry Bay]

Максим Ворожцов, Решили? Попробуйте битвин запрос сделать.
Ковыряюсь в своих вопросах, там парень описывал какой то баг с датами именно при использовании >= , но найти не могу...

[Dmitry Bay]

Максим Ворожцов, нашел, но там не по теме немного. может вам полезно будет - STR_TO_DATE в MSSQL 2008?
как вариант, может временные зоны?

[Максим]

Dmitry Bay, я уже всяко пробовал. В том числе и between. Попробую ещё как вы скинули пример)) Пока не решил проблему)

[Максим]

Dmitry Bay, не помогло(

Answer 1

[Дмитрий]

Если дата у Вас хранится в виде 2018-11-24, то как можно их сравнить?
Получается что-то вроде этого
2018-11-24 >= 2018-11-24
Насколько я понимаю никак.
Я бы добавил время в сравнение

->andWhere(['>=', Event::tableName() . '.date_from', '2018-11-24 00:00:00'])

Но и это может не сработать, так как дата в базе без времени.
Добавьте время, даже желательно в timestamp
Тогда сравнить можно будет так:

->andWhere(['>=', Event::tableName() . '.date_from', strtotime('2018-11-24' . '00:00:00')])

Тут Event::tableName() . '.date_from', timestamp, это тоже выдаёт timestamp strtotime('2018-11-24' . '00:00:00')
А вторую дату можно будет сравнить так:

->andWhere(['<=', Event::tableName() . '.date_to', strtotime('2018-11-25' . '23:59:59')])

Вот как-то так, если я конечно не ошибаюсь.

Comments

[Максим]

Скорее всего есть доля правды в вашем ответе, но мне что-то не помогло. Ну и ещё strtotime формирует строковое выражение... Тогда и дату из базы в строку формировать?

[Дмитрий]

Максим Ворожцов, Вы же внимательно почитайте

Но и это может не сработать, так как дата в базе без времени.
Добавьте время, даже желательно в timestamp

Timestamp будет лучше, чем такая дата "2018-11-25", будет учитываться часовой пояс ещё.

[Дмитрий]

Максим Ворожцов, сделайте тип данных в базе для даты int(11) и записывайте туда timestamp

[Максим]

slo_nik, да я ступил, когда сделал тип данных дата)) Теперь мучаюсь с ним, но нужно переводить в int(11) согласен. Все остальные даты именно такие. Придется переводить и исправлять в коде все данные..

Самое интересное, что даже так не работает....

->andWhere( 'UNIX_TIMESTAMP (event.date_to) >= '.strtotime('2018-11-25' . '00:00:00'))
                ->andWhere( 'UNIX_TIMESTAMP (event.date_to) <= '.strtotime('2018-11-25' . '23:59:59'))

[Дмитрий]

Максим Ворожцов, Вы посмотрите, что возвращает 'UNIX_TIMESTAMP (event.date_to)'?
Если дата в базе без времени, то UNIX_TIMESTAMP превращает дату со временем '00:00:00'.
Хотя... Вы же дату выбираете в форме без учёта времени? Может надо ещё указывать время проведения мероприятия? Тогда точно должно работать.

[Максим]

slo_nik, либо хранить все в одном поле, но время выбирать отдельно. а потом складывать дату и время в int(11)

[Дмитрий]

Максим Ворожцов, не знаю, что за виджет Вы используете в форме, но есть datepicker, там, насколько я помню, можно выбрать дату с временем.
Получится что в input будет это выглядеть так
22 окт 2018 11:45
Вот эту строку приводите к timestamp и записываете в базу.
При выводе Вы можете при помощи formatter показывать или полностью или по частям, где дату, а где только время.
DateControl kartik возьмите, там точно такое есть.

[Дмитрий]

либо хранить все в одном поле, но время выбирать отдельно. а потом складывать дату и время в int(11)

Максим Ворожцов, зачем, сразу пишите всё в одно поле. Меньше возни будет.

[Максим]

slo_nik, да, можно выбирать сразу дату и время в виджете, но хотел бы разделить, поэтому спрашивал) Ну тогда буду писать все в одно поле с виджетом где время выбирается сразу... Благодарю)))))))))))))))))))))

[Дмитрий]

Максим Ворожцов, напишите потом, что получилось.

[Максим]

slo_nik, хорошо

[Максим]

slo_nik, что-то не помогло)) Все таки проблема осталась.. Если например дата проведения Мероприятия 1 13-15 октября, а дата проведения Мероприятия 2 15-15 октября, то не находит)) Печально) Что посоветуете? Может по новой задать вопрос?

[Дмитрий]

Максим Ворожцов, не понял насчёт дат.
Что значит 13-15? С 13-го по 15-е число?
Как именно сейчас хранится дата в базе и как Вы задали условие в поиске?

[Максим]

slo_nik, дату переделал в int(11). Условие не изменилось...

$appointmentsInDatesCount = Appointment::find()
                ->joinWith(['event', 'profile'])
                ->andWhere([Profile::tableName() . '.user_id' => $model->certification->user_id])
                ->andWhere(['!=', Event::tableName() . '.id', $model->event_id])
                ->andWhere(['>=', Event::tableName() . '.date_from', strtotime($model->event->date_from)])
                ->andWhere(['<=', Event::tableName() . '.date_to',  strtotime($model->event->date_to.)])
                ->groupBy(Event::tableName() . '.id')
                ->all();

В ручную добавил strotime, чтобы убедиться. Так же пробовал прибавлять 24 часа, но это тоже не помогало.

[Дмитрий]

Максим Ворожцов, как запрос в debug панели выглядит?

[Максим]

slo_nik,

SELECT `appointment`.* FROM `appointment` LEFT JOIN `event` ON `appointment`.`event_id` = `event`.`id` LEFT JOIN `certification` ON `appointment`.`judge_comitet_id` = `certification`.`id` LEFT JOIN `profile` ON `certification`.`user_id` = `profile`.`user_id` WHERE (`profile`.`user_id`=139) AND (`event`.`id` != 55) AND (`event`.`date_from` >= FALSE) AND (`event`.`date_to` <= FALSE) GROUP BY `event`.`id` ORDER BY `event`.`date_to`

[Дмитрий]

Максим Ворожцов, Вас не смущает это?

(`event`.`date_from` >= FALSE) AND (`event`.`date_to` <= FALSE)

[Максим]

slo_nik, да, не то скинул...

SELECT `appointment`.* FROM `appointment` LEFT JOIN `event` ON `appointment`.`event_id` = `event`.`id` LEFT JOIN `certification` ON `appointment`.`judge_comitet_id` = `certification`.`id` LEFT JOIN `profile` ON `certification`.`user_id` = `profile`.`user_id` WHERE (`profile`.`user_id`=139) AND (`event`.`id` != 55) AND (`event`.`date_from` >= 1539496800) AND (`event`.`date_to` <= 1539547200) GROUP BY `event`.`id` ORDER BY `event`.`date_to`

[Дмитрий]

Максим Ворожцов, а что попадает в $model->event->date_from?

[Максим]

slo_nik, 1539496800
Ваша временная зона: 14.10.2018, 9:00:00

[Максим]

slo_nik, в date_to 1539547200
Ваша временная зона: 14.10.2018, 23:00:00

[Дмитрий]

Максим Ворожцов, это я вообще не понял, откуда берётся?
Ваша временная зона: 14.10.2018, 23:00:00

[Дмитрий]

Максим Ворожцов, Вы, случаем, не массив дат передаёте? Попробуйте одну дату передать, для одного мероприятия.

[Максим]

slo_nik, это я перевел для понятности тут i-leon.ru/tools/time
То, что часы и секунды в поле int(11) учитываются правильно

[Дмитрий]

Максим Ворожцов, ну если в $model->event->date_from дата в timestamp, то зачем Вы опять пытаетесь получить временную метку через strtotime()?

[Максим]

slo_nik, проблема возникает именно тогда, когда Мероприятие проходит в даты меньше чем мероприятие с которым сравнивают. Как я уже писал в вопросе пример:

Мероприятие 1 проходит в даты 2018-11-24 по 2018-11-25 (продолжительность 2 дня)
Мероприятие 2 проходит в даты 2018-11-25 по 2018-11-25 (продолжительность 1 день)

и он в итоге не может найти дату Мероприятия 1, потому что у неё диапазон дат больше (2 дня)

Если мы сделаем наоборот:

Мероприятие 1 проходит в даты 2018-11-25 по 2018-11-25 (продолжительность 1 день)
Мероприятие 2 проходит в даты 2018-11-24 по 2018-11-25 (продолжительность 2 дня)

И назначение сделаем на Мероприятие 2, то всё работает. Подозреваю, что проблема не в часах и минутах... Что думаете? Я просто ни разу с таким не сталкивался и примеров не находил.

[Максим]

slo_nik,

ну если в $model->event->date_from дата в timestamp, то зачем Вы опять пытаетесь получить временную метку через strtotime()?

Я в запросе не пытаюсь получить её. Это я просто для наглядности сделал. Это нигде в коде не присутствует, как и в запросах. Запрос посылается в строковом формате и в базе строка...

[Максим]

slo_nik, ещё сейчас даже обратил внимание на ваш ответ...

2018-11-24 >= 2018-11-24

данное условие ведь всё равно будет выполнимо. У нас же есть знак равенства...

[Дмитрий]

Максим Ворожцов, совсем запутался, надо смотреть вживую.

[Максим]

slo_nik, можете скайп?