Я пытаюсь использовать CakePHP3 Query Builder для следующего запроса, но пока не удалось … Моя главная проблема заключается в том, что я не знаю, как я должен псевдоним внешнего запроса для доступа в подзапросе.
Для полной истории у меня есть следующая таблица:
-------------------------------------------------------------
| from | to | type | dtime | price_1 | price_2 |
-------------------------------------------------------------
| AAAA | BBBB | T1 | 2017.01.01 11:00 | 200 | 300 |
| AAAA | BBBB | T2 | 2017.01.01 18:00 | 250 | 350 |
| CCCC | BBBB | T1 | 2017.01.01 12:00 | 100 | 200 |
| CCCC | BBBB | T2 | 2017.01.01 16:00 | 150 | 250 |
| AAAA | BBBB | T1 | 2017.01.02 10:00 | 2000 | 3500 |
| AAAA | BBBB | T2 | 2017.01.02 19:00 | 1900 | 3000 |
-------------------------------------------------------------
И я ожидаю получить следующее:
------------------------------------------------------------------
| from | to | type | dtime | least(price_1,price_2) |
------------------------------------------------------------------
| AAAA | BBBB | T1 | 2017.01.01 11:00 | 200 |
| CCCC | BBBB | T1 | 2017.01.01 12:00 | 100 |
| AAAA | BBBB | T2 | 2017.01.02 19:00 | 1900 |
------------------------------------------------------------------
Таким образом, в основном я собираю самые дешевые маршруты для каждого данного дня (имея в виду, что может быть больше разного времени для маршрута в день).
До сих пор мне удалось создать следующий запрос, который работает правильно:
SELECT from, to, type, CAST(dtime AS DATE) AS stdd
FROM routes AS r1
WHERE LEAST(price_1, price_2) IN (
SELECT MIN(price_1, price_2)
FROM routes
WHERE from = r1.from
AND to = r1.to
AND CAST(dtime AS DATE) = CAST(r1.dtime AS DATE)
GROUP BY CAST(dtime AS DATE), from, to
)
GROUP BY stdd, from, to;
И проблема возникает сейчас … Я не мог создать правильный запрос CakePHP3. Может ли кто-нибудь помочь мне?
Заранее спасибо,
Zoltan
Обновление 1: Добавлен текущий фрагмент кода CakePHP3:
Оригинальный код:
return $this
->find()
->select(['dtime', 'type', 'from', 'to', 'price_1', 'price_2', 'stdd' => 'cast(dtime as DATE)'])
->order(['dtime' => 'ASC', 'from' => 'ASC', 'to' => 'ASC'])
->group(['dtime','from','to']);
Я хотел как-то улучшить это (код, который я пробовал, уже удален, поэтому я пытаюсь воспроизвести его здесь):
$subQuery = $this
->find()
->select(['min(least(price_1, price_2))'])
->where(['from' => ???outer.from???,
'to' => ???outer.to???,
'cast(dtime as DATE)' => 'cast(???outer.dtime??? as DATE)'])
->group(['cast(dtime as DATE)', from, to]);
return $this
->find()
->select(['dtime', 'type', 'from', 'to', 'price_1', 'price_2', 'stdd' => 'cast(dtime as DATE)'])
->where(['least(price_1, price_2)' => $subQuery])
->order(['dtime' => 'ASC', 'from' => 'ASC', 'to' => 'ASC'])
->group(['dtime','from','to']);
Обновление 2: более конкретный фрагмент кода:
$text = "(1000,3000,5000)"; //select should return something like this
$subQuery = $this
->find()
->select(['X' => 'min(least(price_1, coalesce(price_1, price_2), '
. 'coalesce(price_1, price_3), coalesce(price_1, price_4), '
. 'coalesce(price_1, price_5), coalesce(price_1, price_6)))'])
->where(['from' => ???outer.from???,
'to' => ???outer.to???,
'cast(dtime as DATE) = cast(???outer.dtime??? as DATE)'])
->group(['cast(dtime as DATE)', 'from', 'to']);
return $this
->find()
->select(['dtime', 'from', 'to', 'type', 'price_1', 'price_2', 'price_3', 'price_4', 'price_5', 'price_6',
'stdd' => 'cast(dtme as DATE)'])
->where(['least(price_1, coalesce(price_1, price_2), '
. 'coalesce(price_1, price_3), coalesce(price_1, price_4), '
. 'coalesce(price_1, price_5), coalesce(price_1, price_6)) in ' . $text])
//=> $ar])
//. ' in (37090,111)'])
//=> $ar])//$subQuery->toArray()])
->order(['dtime' => 'ASC', 'from' => 'ASC', 'to' => 'ASC'])
->group(['stdd','from','to']);
Я создал запрос и подзапрос. Если я заменю переменную ??? тогда заполнители возвращают правильное значение. Но тогда конкретный вопрос: как я могу создать псевдоним внешнего «основного» запроса для доступа к его полям в подзапросе?
Задача ещё не решена.
Других решений пока нет …