I try to migrate a legacy MySQL query to TYPO3 8.7 with Doctrine.
Legacy SQL statement:
SELECT
tt_address.*,
( 6371.41 * acos( cos( radians('.$lat.') ) * cos( radians(tt_address.tx_geosearch_lat ) ) * cos( radians(tt_address.tx_geosearch_lng ) - radians('.$lng.') ) + sin( radians('.$lat.') ) * sin( radians(tt_address.tx_geosearch_lat ) ) ) ) AS distance
FROM tt_address
My attempt with TYPO3 8.7:
$statement = $queryBuilder
->select(
'tt_address.*',
'( 6371.41 * acos( cos( radians(' . $queryBuilder->createNamedParameter($lat, \PDO::PARAM_STR) . ') ) * cos( radians(tt_address.tx_geosearch_lat ) ) * cos( radians(tt_address.tx_geosearch_lng ) - radians(' . $queryBuilder->createNamedParameter($lng, \PDO::PARAM_STR) . ') ) + sin( radians(' . $queryBuilder->createNamedParameter($lat, \PDO::PARAM_STR) . ') ) * sin( radians(tt_address.tx_geosearch_lat ) ) ) ) AS distance'
)
->from('tt_address')
This leads to a completely broken SQL query:
SELECT `tt_address`.*,
`( 6371`.`41 * acos( cos( radians(48.0818583) ) * cos( radians(tt_address`.`tx_geosearch_lat ) ) * cos( radians(tt_address`.`tx_geosearch_lng ) - radians(11.9879884) ) + sin( radians(48.0818583) ) * sin( radians(tt_address`.`tx_geosearch_lat ) ) ) )` AS `distance`
FROM `tt_address`
Any pointers on how to solve this?
I'm not sure if this is possible with the querybuilder. Nevertheless I would do it with a connection:
$connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($tableName);
$row = $connection->executeQuery('select * from ' . $tableName . ' where uid = 1')->fetchAll();