Актуальный статус заказа и поиск по нему?

Question

[Радость моя]

Привет друзья!

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

Есть таблица с заказами:

orders

id | fio  | phone
------------------
25 | Ivan | +7982...

есть таблица со статусами:

orders_log

id  | order_id | action     | action_date
-----------------------------------------------
335 |       25 | CREATED    | 2018-11-09 08:00:00
336 |       25 | APPROVED   | 2018-11-09 08:30:00
336 |       25 | SENT       | 2018-11-09 09:20:00
336 |       25 | CHANGE_SYS | 2018-11-09 09:25:00
...

есть таблица с типами статусов log_actions (технические статусы is_technical = 1 их не показываем операторам, и публичный который могут видеть операторы is_technical = 0)

log_actions

id | action     | is_technical
------------------------------
1  | CREATED    | 0
2  | APPROVED   | 0
3  | SENT       | 0
5  | CHANGE_SYS | 0

операторы видят в вебморде список заказов с текущим статусом. запрос для этого такой:

SELECT 
	`orders`.*, 
	(
		SELECT ol.`action` 
			FROM `orders_log` ol 
			LEFT JOIN log_actions la ON ol.`action` = la.`action` 
		WHERE 
			ol.order_id = orders.id AND 
			la.is_technical = 0 
		ORDER BY  ol.`dttm` 
		DESC LIMIT 1
	) as `status` 
FROM `orders`

и все хорошо и быстро работает до тех пор пока оператор не захочет найти заказы с определенным
текущим статусом. Например все заказы которые на данный момент имеют последний не технический статус "Создан". Для поиска по конкретному статусу дописываю так:

...
FROM `orders`
HAVING (`status`='CREATED')

получается что он вытаскивает сначала все записи (а их много), а потом фильтрует уже. из за этого так долго. А просто status = 'CREATED' выдает ошибку что нет такого поля, т.к. оно динамическое получается, и не работает в WHERE.

Вопрос: как избавится от HAVING в данном случае, чтобы выборки были быстрыми? Мучаюсь страдаю 2-ой день уже.

Answer 1

[Rsa97]

А что мешает помимо истории статусов хранить текущий статус заказа непосредственно в таблице заказов? Тогда всё становится гораздо проще.

Comments

[Радость моя]

Просто текущий статус хранится. Но он может быть техническим и не должен быть виден операторам.

[Rsa97]

Радость моя, Храните обычный и технический статусы в разных полях.

Answer 2

[Dmitry Bay]

!) log_actions - ACTION должно быть число в таблице. А в приложении - константами.
Вам незачем при каждой выборке иметь лог необходимых статусов. Добавьте поле status в таблицу `orders ` и при изменении статуса, пишите новый статус в лог, и обновляйте из основной таблицы.

Comments

[Радость моя]

Просто текущий статус хранится. Но он может быть техническим и не должен быть виден операторам. Этот момент описан в вопросе, если внимательно почитать.

[Dmitry Bay]

Радость моя, они могут в принципе работать с заказом, у которого есть технический статус?
Что делает технический статус в данном виде?

[Dmitry Bay]

Радость моя, если я правильно понял, технический - это что-то типо deleted.
Но это не отменяет идеи статуса в orders.

Подгружаете таблицу статусов в php, сверяете с теми статусами, с которыми можно работать менеджеру, и выводите заказы с нужным статусом.

[Радость моя]

Dmitry Bay, технический статус это обычный статус только не отображаемый операторам. в зависимости от технического статуса выполняются какие-то автоматические действия. Их не показываем операторам чтобы не путать их, и чтобы было более юзер-френдли. Например такие статусы как "адрес валидирован" "смс доставлено" "трекинг сработал" и прочее - не дают операторам никакой полезной информации. для них этот заказ в статусе "отправлен". Нет смысла сейчас говорить о том как надо сделать правильно, т.к. это на данный момент уже давно сделано и работает. сейчас нужно настроить нормальный поиск по любому не-техническому статусу, чтобы он был без having, т.к. он очень ресурсоемкий и на 100 тыс заказов (+ ~1млн статусов) думает долго.

[Радость моя]

Dmitry Bay,

если я правильно понял, технический - это что-то типо deleted.
Но это не отменяет идеи статуса в orders.

Подгружаете таблицу статусов в php, сверяете с теми статусами, с которыми можно работать менеджеру, и выводите заказы с нужным статусом.

представьте ситуацию. оператор выбрал заказы с текущем статусом "Отправлен". по факту есть такой заказ который "отправлен", но после него есть еще 3 статуса технических - "смс оповещение отправлено" "смс получено" "и еще что-то". в таком случае он не увидит этот заказ. хотя он отправлен, а тех. статусы оператор выбрать не может, он их не видит.

[Dmitry Bay]

Радость моя,

без having, т.к. он очень ресурсоемкий и на 100 тыс заказов (+ ~1млн статусов) думает долго.

тепреь понятно, почему у вас лагает при вложенном запросе уже... этого следовало ожидать.

Вижу два способа
- записывать в orders только основные статусы, так как смежные нам не нужны, и они нужны только для лога.
- настроить маппинг статусов. оставить идею записывать любой статус в orders, а уже со стороны php запрашивать в виде - "where status in ('Отправлено', 'Отправлено [отправлено смс]')". Но это будет работать, если у отправлено смс есть привязка к статусу.

[Dmitry Bay]

Например все заказы которые на данный момент имеют последний не технический статус "Создан".

Собственно тут вы и намекаете на то, что нужно просто хранить последний не технический статус.

[Радость моя]

Dmitry Bay, не хотелось бы переписывать систему под запрос. хотелось переписать запрос под систему.

и тех. статусы нужны не только для лога. на них завязаны так же демоны и кроны.

[Dmitry Bay]

Я понял вашу проблему, сейчас попробую сформулировать.

[Dmitry Bay]

Радость моя, попробуйте проверить вот такой код

select *   from 
orders_log ol
inner join log_actions la ON ol.`action` = la.`action`
inner join orders o on o.id=ol.orders_id
WHERE 
ol.order_id = orders.id AND 
la.is_technical = 0 AND 
la.title = ':status'

Забирать все можно так -

select *, max(dmmt)   from 
orders_log ol
inner join log_actions la ON ol.`action` = la.`action`
inner join orders o on o.id=ol.orders_id
WHERE 
ol.order_id = orders.id AND 
la.is_technical = 0  
 group by order_id

Но я бы смотрел в сторону упрощения запроса, добавил бы как минимум ограничение по времени, и лимит на кол-во записей.

[Радость моя]

Dmitry Bay, спасибо за запросы. первый проверил - работает заметно быстрее, но к сожалению не точно. проверка случайно выбранного заказа показала что искомый статус не является последним не техническим.

[Dmitry Bay]

Радость моя, в первом запросе тогда вам нужно будет добавить max(dmmt) и делать группировку group by order_id, тогда вы будете вытаскивать максимальное (или минимальное) обновление статуса,

select *,max(dmmt)  from 
orders_log ol
inner join log_actions la ON ol.`action` = la.`action`
inner join orders o on o.id=ol.orders_id
WHERE 
ol.order_id = orders.id AND 
la.is_technical = 0 AND 
la.title = ':status'
group_by orders.id

[Радость моя]

Dmitry Bay, последний вариант по скорости как с having, и тоже выдает не правильные данные по статусам. точнее статусы правильные, только выборка по конкретному статусу игнорируется.

(ищу только что созданные, выдает уже выкупленные)

select o.*,max(ol.dttm) as maxdttm  from 
orders_log ol
inner join log_actions la ON ol.`action` = la.`action`
inner join orders o on o.id=ol.order_id
WHERE 
ol.order_id = o.id AND 
la.is_technical = 0 AND 
ol.`action` = 'CREATED'
group by o.id

[Dmitry Bay]

Радость моя, моя ошибка - ol.`action` = 'CREATED' - не должно быть.
видимо придется having добавлять.

[Радость моя]

Dmitry Bay, да, и с having'ом мы снова возвращаемся к сути вопроса - как от него избавиться :)

[Dmitry Bay]

Радость моя, В любом случае, ваш самый первый запрос делает промежуточный select на каждую строку.
Думаю что having в последних запросах будет кушать на порядок меньше, + ограничьте выборку, и может быть будет результат.

[Радость моя]

Dmitry Bay, промежуточный селект работает очень быстро т.к. поставлены правильные ключи. без having запрос отрабатывает за 0.5с с вложенным запросом