1

What's the right way to execute the following typo3 SELECT with mysql @-variables:

$dbConnection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($extensionTable);
$queryBuilder = $dbConnection->createQueryBuilder();

$query = $queryBuilder
->select('extensionTable.*',
'@dist:=ACOS(ROUND(SIN(RADIANS(geo.coord_lat)) * SIN(RADIANS(52.5234743092278))
   + COS(RADIANS(geo.coord_lat)) * COS(RADIANS(52.5234743092278))
   * COS(RADIANS(geo.coord_lon)      - RADIANS(13.4122033308763)),10)) * 6380 AS distance',
'@distgrp:=CASE WHEN @dist <10 THEN 1 WHEN @dist<25 THEN 2 ELSE truncate(((@dist-25)/25)+3, 0) END AS dist_group')
->from('extensionTable  LEFT JOIN geoTable AS geo ON geo.zip=extensionTable.zip')
->where('pid IN (15,16)');

It's not working, typo3 is inserting a ' before the @dist var. The bad result is: 

SELECT 'extensionTable'.*, '@dist:=... ' AS 'distance';

Error: An exception occurred while executing... You have an error in your SQL syntax;

Related topic (solution doesn't work with $queryBuilder->select() ): How to use mysql variables in doctrine

I hope someone can help me. :-) (Or do I have to use a direct php mysqli() query.)

Mars
  • 27
  • 5
  • I would calculate `dist_group` in PHP. Even if you get this SQL to run - this way is not reliable. There is no guarantee that `@dist` will be calculated before `dist_group`. – Paul Spiegel Feb 04 '20 at 18:00
  • @Paul Spiegel: I use this select with mysql for many years. It's working fine and dist is calculated before dist_group. – Mars Feb 05 '20 at 13:42
  • "I use this select with mysql for many years. It's working fine" .. until it's not. – Paul Spiegel Feb 05 '20 at 15:59

1 Answers1

1

You can prevent escaping with a selectLiteral.

$queryBuilder
  ->select(...)
  ->addSelectLiteral('@dist:=COS(RADIANS(90))')
Jonas Eberle
  • 2,835
  • 1
  • 15
  • 25