Performance optimization & extendability improvement for eager loading of authors #9907

lenvanessen · 2021-10-01T09:39:57Z

lenvanessen
Oct 1, 2021

Background:
We have saved the author ids into a separate table. This way, we can have a different author for each site, and in the EntryQuery we have done some magick to join the tables value as the "authorId" field so that Craft pulls the site-specific author.

Problem & Proposed solution:
However, if we want to eager-load the authors, this becomes a problem. In the Entry's eagerLoadingMap a separate query is being made that specifically pulls the id and authorId column from the entries table.

Which happens here:
https://github.com/craftcms/cms/blob/develop/src/elements/Entry.php#L599-L621

This query is unnecasarry, as the sourceElements are being passed trough anyways, so the combination of id and authorId can be extracted from there, removing an unneeded query (and in our case, making the last piece of the puzzle fit). We can filter and map the sourceElements array to match the output currently generated by the extra query.

Old method:

public static function eagerLoadingMap(array $sourceElements, string $handle)
{
    if ($handle === 'author') {
        // Get the source element IDs
        $sourceElementIds = ArrayHelper::getColumn($sourceElements, 'id');

        $map = (new Query())
            ->select(['id as source', 'authorId as target'])
            ->from([Table::ENTRIES])
            ->where(['and', ['id' => $sourceElementIds], ['not', ['authorId' => null]]])
            ->all();

        return [
            'elementType' => User::class,
            'map' => $map,
            'criteria' => [
                'status' => null,
            ],
        ];
    }

    return parent::eagerLoadingMap($sourceElements, $handle);
}

Proposed solution:

public static function eagerLoadingMap(array $sourceElements, string $handle)
{
    if ($handle === 'author') {
        $sourceElementsWithAuthors = array_filter($sourceElements, function(Entry $entry) {
            return $entry->authorId !== null;
        });

        $map = array_map(function(Entry $entry){
            return [
                'source' => $entry->id,
                'target' => $entry->authorId
            ];
        }, $sourceElementsWithAuthors);

        return [
            'elementType' => User::class,
            'map' => $map,
            'criteria' => [
                'status' => null,
            ],
        ];
    }

    return parent::eagerLoadingMap($sourceElements, $handle);
}