Народ, всем привет!
Столкнулся тут с такой/ими ситуациями и не знаю что делать.
В общем xPDO:
1. обрезает длинное WHERE-условие;
2. автоматически добавляет название класса в WHERE когда это не надо.
Понадобилось мне тут посчитать расстояние между двумя точками, основанными на широте и долготе.
Нагуглил умную формулу и даже SQL-реализацию.
Пишу запрос:
$q = $modx->newQuery('modResource'); $q->select(array( 'modResource.id', 'modResource.pagetitle', '`TVlat`.`value` as `lat`', '`TVlng`.`value` as `lng`', '`TVprice`.`value` as `price`', '((ACOS(SIN(53.2242846 * PI() / 180) * SIN(`TVlat`.`value` * PI() / 180) + COS(53.2242846 * PI() / 180) * COS(`TVlat`.`value` * PI() / 180) * COS((50.197219700000005 - `TVlng`.`value`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515 * 1.609344 * 1000) AS `radius`' )); $q->leftJoin('modTemplateVarResource', 'TVlat', 'TVlat.contentid=modResource.id AND TVlat.tmplvarid=15'); $q->leftJoin('modTemplateVarResource', 'TVlng', 'TVlng.contentid=modResource.id AND TVlng.tmplvarid=16'); $q->leftJoin('modTemplateVarResource', 'TVprice', 'TVprice.contentid=modResource.id AND TVprice.tmplvarid=3'); $q->where(array( '`TVprice`.`value`' => 5000, '((ACOS(SIN(53.2242846 * PI() / 180) * SIN(`TVlat`.`value` * PI() / 180) + COS(53.2242846 * PI() / 180) * COS(`TVlat`.`value` * PI() / 180) * COS((50.197219700000005 - `TVlng`.`value`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515 * 1.609344 * 1000):<=' => 1500 // расстояние меньше полутора километра )); $q->prepare(); return $q->toSQL();
В этом запросе джойнятся 3 твшки — цена, широта и долгота. Большая и умная формула — это расчёт расстояния от определённой точки (с широтой 53.2242846 и долготой 50.197219700000005), до точки из записи таблицы. В принципе это не суть.
Важно то, что запрос выйдет такой:
SELECT modResource.id, modResource.pagetitle, `TVlat`.`value` as `lat`, `TVlng`.`value` as `lng`, `TVprice`.`value` as `price`, ((ACOS(SIN(53.2242846 * PI() / 180) * SIN(`TVlat`.`value` * PI() / 180) + COS(53.2242846 * PI() / 180) * COS(`TVlat`.`value` * PI() / 180) * COS((50.197219700000005 - `TVlng`.`value`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515 * 1.609344 * 1000) AS `radius` FROM `modx_do_dbsite_content` AS `modResource` LEFT JOIN `modx_do_dbsite_tmplvar_contentvalues` `TVlat` ON TVlat.contentid=modResource.id AND TVlat.tmplvarid=15 LEFT JOIN `modx_do_dbsite_tmplvar_contentvalues` `TVlng` ON TVlng.contentid=modResource.id AND TVlng.tmplvarid=16 LEFT JOIN `modx_do_dbsite_tmplvar_contentvalues` `TVprice` ON TVprice.contentid=modResource.id AND TVprice.tmplvarid=3 WHERE ( `((ACOS(SIN(53`.`2242846 * PI() / 180) * SIN(`TVlat` <= '1500' AND `TVprice`.`value` = '5000' )
Всё внимание на WHERE:
WHERE (`((ACOS(SIN(53`.`2242846 * PI() / 180) * SIN(`TVlat` <= '1500' AND `TVprice`.`value` = '5000' )
xPDO беспардонно обрезал необходимое мне условие :-(
А почему в условии просто не написать WHERE `radius` <= '1500'? Спросите вы… Зачем понадобилось дублировать формулу в WHERE?
А потому, что mysql парсит и обрабатывает запрос справа налево, и в момент, когда, он встречает в условии `radius` — он ещё не знает про этот алиас, а поля такого не существует. Соответсвенно, мускул кидает ошибку.
Но есть и второй вариант с HAVING'ом — это то же самое, что и WHERE, но для вычисляемых полей, таких как SUM, AVG или как моего. И из-за особенностей всё того же mysql'я, в конструкции HAVING можно писать алиас, потому что HAVING обрабатывается позже SELECT'а. Такие дела.
Поэтому переписываю запрос вот так:
$q = $modx->newQuery('modResource'); $q->select(array( 'modResource.id', 'modResource.pagetitle', '`TVlat`.`value` as `lat`', '`TVlng`.`value` as `lng`', '`TVprice`.`value` as `price`', '((ACOS(SIN(53.2242846 * PI() / 180) * SIN(`TVlat`.`value` * PI() / 180) + COS(53.2242846 * PI() / 180) * COS(`TVlat`.`value` * PI() / 180) * COS((50.197219700000005 - `TVlng`.`value`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515 * 1.609344 * 1000) AS `radius`' )); $q->leftJoin('modTemplateVarResource', 'TVlat', 'TVlat.contentid=modResource.id AND TVlat.tmplvarid=15'); $q->leftJoin('modTemplateVarResource', 'TVlng', 'TVlng.contentid=modResource.id AND TVlng.tmplvarid=16'); $q->leftJoin('modTemplateVarResource', 'TVprice', 'TVprice.contentid=modResource.id AND TVprice.tmplvarid=3'); $q->where(array( '`TVprice`.`value`' => 5000, )); $q->having(array( '`radius`:<=' => 1500 // расстояние меньше полутора километра )); $q->prepare(); return $q->toSQL();
И что мы видим?
SELECT modResource.id, modResource.pagetitle, `TVlat`.`value` as `lat`, `TVlng`.`value` as `lng`, `TVprice`.`value` as `price`, ((ACOS(SIN(53.2242846 * PI() / 180) * SIN(`TVlat`.`value` * PI() / 180) + COS(53.2242846 * PI() / 180) * COS(`TVlat`.`value` * PI() / 180) * COS((50.197219700000005 - `TVlng`.`value`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515 * 1.609344 * 1000) AS `radius` FROM `modx_do_dbsite_content` AS `modResource` LEFT JOIN `modx_do_dbsite_tmplvar_contentvalues` `TVlat` ON TVlat.contentid=modResource.id AND TVlat.tmplvarid=15 LEFT JOIN `modx_do_dbsite_tmplvar_contentvalues` `TVlng` ON TVlng.contentid=modResource.id AND TVlng.tmplvarid=16 LEFT JOIN `modx_do_dbsite_tmplvar_contentvalues` `TVprice` ON TVprice.contentid=modResource.id AND TVlng.tmplvarid=3 WHERE `TVprice`.`value` = '5000' HAVING `modResource`.`radius` <= '1500'
Снова внимание в WHERE:
WHERE `TVprice`.`value` = '5000' HAVING `modResource`.`radius` <= '1500'
К псевдониму `radius` xPDO добавил `modResource`, хотя он здесь не нужен и я его об этом не просил. Соответственно результат снова пустой :-(
К слову сказать, все запросы рабочие и проверены на живой базе. Осталось только воспроизвести их через построитель запросов xPDO.
Блин, ну почему он так делает? Как это побороть?
Вот некогда особо вчитываться, хотя топик интересный. Я думаю, вас спасет один простенький финт: в PHP-условиях отказаться от ассоциативных массивов, а писать просто
$q->where(array( "моя длинная строка-запрос вместе с > и т.п.", ));
Тогда xPDO не будет парсить эту строку и манипулировать с ней.
Вот же ж блин! И ведь правда сработало! Как я мог забыть про это?
Спасибо огромное!
Пожалуйста