API4 error "Column in order clause is ambiguous"
I have a specific query that used to work but seems to be broken since around CiviCRM 5.60.
I was able to reproduced in master.
This is the api4 call:
$addresses = \Civi\Api4\Address::get()
->addJoin('Contact AS contact', 'LEFT', ['master_id.contact_id', '=', 'contact.id'])
->addOrderBy('location_type_id:label', 'ASC')
->setLimit(25)
->execute();
location_type_id
is in Address and as it it doing a join for another address to get the master_id address, it is ambiguous.
Looking at the generated query, we can see that the table prefix is not added in the order by :
SELECT `a`.`id` AS `id`, `contact`.`display_name` AS `contact.display_name`
FROM civicrm_address a
LEFT JOIN `civicrm_address` `master_id_1` ON `a`.`master_id` = `master_id_1`.`id`
LEFT JOIN `civicrm_contact` `contact` ON `master_id_1`.`contact_id` = `contact`.`id`
ORDER BY FIELD(`location_type_id`,'16','14','1','19','3','15','4','12','2','17','18','20','21','5','11') ASC;
Giving the error :
ERROR 1052 (23000): Column 'location_type_id' in order clause is ambiguous