Set a default ordering for `get` APIs
Overview
In SQL/MySQL, SELECT
queries accept an option ORDER BY
. If the option is not given, then the DBMS can choose an arbitrary ordering -- although, most of the time, it's a facsimile of ORDER BY id
. This creates a false expectation of ordering. This behavior has a knock-on effect - it also applies to API requests.
Example use-case
civicrm_api3('Contact', 'get', []);
civicrm_api3('Dedupe', 'get', []);
civicrm_api4('Group', 'get', []);
civicrm_api4('Activity', 'get', []);
Current behaviour
In absence of an explicit orderBy
/ sort
option, the get
API relies on the DBMS's default ordering... which usually returns records in order of ID/creation, but it sometimes returns records with an arbitrary ordering.
Proposed behaviour
In the absence of an explicit orderBy
/ sort
option, the get
API should set ORDER BY id
(provided that id
is a sensible column for the given query).
This makes the actual behavior align reliably with the typical/expected behavior.
Comments
A person looking at a typical result-set (esp on a developmental system, with its synthetic data) can incorrectly infer that records are being returned in a sensible order. But they're not - and the misunderstanding can lead to subtle bugs that are difficult to reproduce. (Ex: SyntaxConformanceTest::testSqlOperators()
has been flaky for a long time and ultimately needed a patch to use explicit ordering. We only diagnosed the bug by luck -- it was easy to reproduce with one particular entity on one particular version of MySQL.)
Setting the default is a systemic way to prevent this class of bug.