[5.x]: Selecting a slug in an entries query for an entries field returns no results

[MoritzLost]

What happened?

Description

I just encountered a bug with an entry query that worked in Craft 4, but is broken in Craft 5:

$categories = $job->jobCategories->select('slug')->column();

$job is an entry and jobCategories is an Entries field. This returned an array of slugs (strings) in Craft 4, but in Craft 5 it's always returning an empty array, regardless of how many entries are selected in the field.

Using ->all() instead of ->column shows the same behaviour. In Craft 4 this returns an array of entries (with all other properties and fields empty, of course). In Craft 5, this still returns an empty array.

I also tried selecting some other fields – id works, but title doesn't. I also tried to explicitly select the table and column (elements_sites.slug), but that isn't working either.

Not sure if I'm doing something wrong here. If the select statement was just wrong, I would get an error, e.g. select('foobar') throws an error.

Notably, if I use a plain entries query without starting from an Entries field, the query works as expected. This returns an array of entry slugs:

Entry::find()->limit(5)->select('slug')->column();

So it might be related to the parameters set by relations fields?

Steps to reproduce

1. Add an Entries field to an entry type's field layout.
2. Create a new entry and select some entries in the Entries field.
3. In the template for the entry, access the Entries field and select the slug using the query above.

Craft CMS version

5.2.8

PHP version

8.3

Operating system and version

No response

Database type and version

No response

Image driver and version

No response

Installed plugins and versions

No response

[brandonkelly]

Just tested this locally on 5.2.8 and I’m getting an array with all the related entries’ slugs back as expected.

Try enabling the safeMode config setting and see if it helps?

[MoritzLost]

@brandonkelly Still seeing this in save mode, unfortunately. Tested on two sites that are based on our internal boilerplate project, and one independent site that doesn't share any code with that. All have the same issue.

I've also tested this in a completely fresh Craft installation based on craftcms/craft. In this install, the query works correctly and returns an array of slugs as expected.

To find out what's happening, I've used getRawSql() on both queries and compared the SQL statements. I've pasted the SQL query from the site where the error is happening below. They are mostly identical except for one major difference, this additional condition which isn't present in the fresh installation:

(`elements`.`id` IS NULL) OR (`elements`.`id` IS NULL)`

This looks like it's the cause of my empty result set. Any idea why this might be added to the query?

Edit: Just compared this query to the query without the select() statement. In place of the condition above is a condition that limits the results by the ID of the entries selected in the Entries field:

`elements`.`id` IN (104, 86)

Not sure where the query is getting those IDs before the query is even executed? Is it running an initial query to find those IDs before executing the main query?

SELECT `slug`
FROM (
        SELECT `elements`.`id` AS `elementsId`,
            `elements_sites`.`id` AS `siteSettingsId`,
            `structureelements`.`structureId`
        FROM `elements` `elements`
            INNER JOIN `entries` `entries` ON `entries`.`id` = `elements`.`id`
            INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
            LEFT JOIN `structureelements` `structureelements` ON (
                `structureelements`.`elementId` = `elements`.`id`
            )
            AND (
                EXISTS (
                    SELECT *
                    FROM `structures` use index(primary)
                    WHERE (`id` = `structureelements`.`structureId`)
                        AND (`dateDeleted` IS NULL)
                )
            )
            INNER JOIN `relations` `relations_huaucnnutp` ON (
                `relations_huaucnnutp`.`targetId` = `elements`.`id`
            )
            AND (
                (`relations_huaucnnutp`.`sourceId` = 85)
                AND (`relations_huaucnnutp`.`fieldId` = 41)
            )
            AND (
                (`relations_huaucnnutp`.`sourceSiteId` IS NULL)
                OR (`relations_huaucnnutp`.`sourceSiteId` = 1)
            )
        WHERE (
                (`elements`.`id` IS NULL)
                OR (`elements`.`id` IS NULL)
            )
            AND (
                (
                    (`elements`.`enabled` = TRUE)
                    AND (`elements_sites`.`enabled` = TRUE)
                )
                AND (`entries`.`postDate` <= '2024-07-29 13:42:59')
                AND (
                    (`entries`.`expiryDate` IS NULL)
                    OR (`entries`.`expiryDate` > '2024-07-29 13:42:59')
                )
            )
            AND (`elements`.`archived` = FALSE)
            AND (`elements`.`dateDeleted` IS NULL)
            AND (`elements`.`draftId` IS NULL)
            AND (`elements`.`revisionId` IS NULL)
        ORDER BY `structureelements`.`lft`,
            `entries`.`postDate` DESC,
            `elements`.`id` DESC
    ) `subquery`
    INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
    INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`siteSettingsId`
    INNER JOIN `entries` `entries` ON `entries`.`id` = `subquery`.`elementsId`
    LEFT JOIN `structureelements` `structureelements` ON (
        `structureelements`.`elementId` = `subquery`.`elementsId`
    )
    AND (
        `structureelements`.`structureId` = `subquery`.`structureId`
    )
    INNER JOIN `relations` `relations_huaucnnutp` ON (
        `relations_huaucnnutp`.`targetId` = `elements`.`id`
    )
    AND (
        (`relations_huaucnnutp`.`sourceId` = 85)
        AND (`relations_huaucnnutp`.`fieldId` = 41)
    )
    AND (
        (`relations_huaucnnutp`.`sourceSiteId` IS NULL)
        OR (`relations_huaucnnutp`.`sourceSiteId` = 1)
    )
ORDER BY `structureelements`.`lft`,
    `entries`.`postDate` DESC,
    `elements`.`id` DESC

[brandonkelly]

Thanks! That query was modified per the “Maintain hierarchy” setting. When I enabled that I was able to reproduce locally. Fixed now in 5.2.9.

[MoritzLost]

@brandonkelly Awesome, that did the trick, thanks!

The commit looks magical – if you have some time, I would love an explanation of what the issue was and how that additional line fixed it 👀 Never seen the '**' syntax in a selector before … 

[brandonkelly]

@MoritzLost That’s just a placeholder value we assign to select by default for element queries:

https://github.com/craftcms/cms/blob/af8fb1aec8793ce06d86e73d44dbf02548cba52d/src/elements/db/ElementQuery.php#L580-L581

When preparing the query to be executed, we check if the ** key is still in there, and if so, we know to include the default columns in the generated query:

https://github.com/craftcms/cms/blob/af8fb1aec8793ce06d86e73d44dbf02548cba52d/src/elements/db/ElementQuery.php#L3274-L3275

When relational fields are fetching any missing relations that should be injected into the results due to the “Maintain hierarchy” setting, they clone the main element query, along with any modifications you might have made to it, like overriding the select clause. So the fix was to revert select back to **, meaning the default select columns should be included, just for that additional query.

[MoritzLost]

@brandonkelly Ah got it, that's why there's a second query, makes sense that the missing items in the hierarchy need to be fetched and filled in at some point. Thanks for the explanation!