SearchKit - Fix ORDER BY calculated fields when using GROUP BY
authorColeman Watts <coleman@civicrm.org>
Wed, 30 Mar 2022 12:55:00 +0000 (08:55 -0400)
committerColeman Watts <coleman@civicrm.org>
Mon, 18 Apr 2022 18:34:14 +0000 (14:34 -0400)
commitff2a73cb2a6bb07460edc0a368149a333dafd637
tree763def0eea04cf13af4254e844317dac93121494
parent13b73d76f5ca7381d1450990dcfad1c39a6e2509
SearchKit - Fix ORDER BY calculated fields when using GROUP BY

Before: Calculated field functions were repeated verbabim in the SELECT, ORDER BY & GROUP BY clause,
this would cause mySql error when using FULL GROUP BY mode.

After: Calculated field function defined in SELECT clause, alias used in ORDER BY & GROUP BY.
No error when grouping by an ordering by the same calculated field.

Side-benefit: When selecting and ordering by RAND(), the selected random numbers will be in order,
as it's now the same random function used for both instead of a different one.
Civi/Api4/Query/Api4SelectQuery.php
ext/search_kit/tests/phpunit/api/v4/SearchSegment/SearchSegmentTest.php
tests/phpunit/api/v4/Action/SqlFunctionTest.php