Api4 :label suffix ignored if value used in SQL function like COALESCE
Overview
Without giving any error, just quietly returning the wrong results, CiviCRM ignores :label
on custom option value fields.
Reproduction steps
The standard buildkit install comes with a set of custom fields for Individuals. Pick an Individual and edit so that their 'most important issue' is Education.
Then run:
$contacts = \Civi\Api4\Contact::get(TRUE)
->addSelect('constituent_information.Most_Important_Issue:label', 'COALESCE(constituent_information.Most_Important_Issue:label, "oops") AS oops')
->addWhere('constituent_information.Most_Important_Issue', 'IS NOT NULL')
->execute()->getArrayCopy();
print_r($contacts);
You will see the label is correctly used for the first column (Education
) but in the oops
column it says Edu
.
I can understand why (the :label
conversion is done in PHP after the query, if I remember correctly) but the API should throw an error in cases like this when it can't handle the request, instead of using the optionvalue's value. (Or it should be clever enough to join the optionvalue table, but I can see that's a bit of wizardry 🪄)