[5.x]: Error in advanced element query "select" when adding column alias on custom field

[danbrellis]

What happened?

Description

When trying to do an advanced element query calling a custom field in the select statement, I get an SQL error if I give the custom field a column alias.

This works

{% set critters = craft.entries()
  .section('critters')
  .orderBy('title')
  .limit(null)
  .select(['title as t', 'scientificName'])
  .asArray()
  .all() %}

This doesn't work

{% set critters = craft.entries()
  .section('critters')
  .orderBy('title')
  .limit(null)
  .select(['title as t', 'scientificName as l'])
  .asArray()
  .all() %}

Note the as l for the scientificName custom field.

Expected behavior

An array of objects with keys t and l

Actual behavior

SQL error: Column not found: 1054 Unknown column 'scientificName' in 'field list'.

Craft CMS version

5.4.6

PHP version

8.3.10

Operating system and version

No response

Database type and version

mysql 8.0

Image driver and version

No response

Installed plugins and versions

No response

[brandonkelly]

This is currently expected, as aliased columns are excluded from being mapped to the actual custom field value expressions:

cms/src/elements/db/ElementQuery.php

Lines 3389 to 3390 in d44a2d4

	// Is this a mapped column name (without a custom alias)?
	if ($alias === $column && isset($this->_columnMap[$alias])) {

That said, I can’t find any reason for that limitation, and it seems to be just erring on the safe side, so I’ve removed that check for Craft 4.13 and 5.5. (39bd164)

[brandonkelly]

Craft 4.13.0 and 5.5.0 are out now with that change 🎉