Reading EAV Efficiently

What addAttributeToSelect and addAttributeToFilter actually do to the query, and why asking for every attribute is slow.

SyntaxaddAttributeToSelect($attribute, $joinType = false) / addAttributeToFilter($attribute, $condition = null, $joinType = 'inner')

A product collection starts as a select over catalog_product_entity alone. Nothing but the static columns is there, so you have to ask for every field you intend to read.

Two methods, two mechanisms

addAttributeToSelect($attribute, $joinType = false) with the default join type adds no join at all. It records the attribute, and after the main query runs, the collection fetches the values with one extra query per value table, combined with UNION ALL where the database allows it. Ten varchar attributes cost one extra query, not ten.

addAttributeToFilter($attribute, $condition = null, $joinType = 'inner') does join, immediately, because the condition has to be in the WHERE clause. For a store-scoped attribute on a non-default store there are two joins: one to the store 0 row and one to the store's own row, with the value chosen by IF.

SELECT e.entity_id, e.sku,
       IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS name
FROM catalog_product_entity AS e
INNER JOIN catalog_product_entity_varchar AS at_name_default
  ON at_name_default.entity_id = e.entity_id
 AND at_name_default.attribute_id = ?
 AND at_name_default.store_id = 0
LEFT JOIN catalog_product_entity_varchar AS at_name
  ON at_name.entity_id = e.entity_id
 AND at_name.attribute_id = ?
 AND at_name.store_id = 1

Why '*' hurts

addAttributeToSelect('*') loads every attribute of the entity type, description and other text blobs included, for every row in the page. Name the four or five codes you need instead.

Example

Example · php
<?php
// app/code/Vendor/Catalog/Model/OriginFinder.php
declare(strict_types=1);

namespace Vendor\Catalog\Model;

use Magento\Catalog\Model\Product\Attribute\Source\Status;
use Magento\Catalog\Model\ResourceModel\Product\CollectionFactory;

class OriginFinder
{
    public function __construct(private readonly CollectionFactory $collectionFactory)
    {
    }

    /**
     * @return array<string, string> sku => name
     */
    public function fromOrigin(int $optionId, int $storeId, int $limit = 50): array
    {
        $collection = $this->collectionFactory->create();

        // Store id first: the filter joins are built against it.
        $collection->setStoreId($storeId);

        // Shorthand for two addAttributeToSelect() calls:
        $collection->addAttributeToSelect(['name', 'country_of_origin']);

        // The condition array is bound, not interpolated.
        $collection->addAttributeToFilter('country_of_origin', ['eq' => $optionId]);
        $collection->addAttributeToFilter('status', ['eq' => Status::STATUS_ENABLED]);
        $collection->setPageSize($limit);

        $result = [];

        foreach ($collection as $product) {
            $result[$product->getSku()] = (string) $product->getName();
        }

        return $result;
    }
}

// Never build the condition as a string:
//     addAttributeToFilter('country_of_origin', 'option_id = ' . $optionId)
// addFieldToFilter and addAttributeToFilter are not automatically safe.
// A condition array goes through the adapter's quoting; a raw string does not.

When to use it

  • A report that reads sku, name and one custom attribute for 50,000 products.
  • Fixing a listing page that times out because something asked for every attribute.
  • Reading a store-view-scoped value and getting the default value instead.

More examples

Print the SQL a collection will run

getSelect() returns the Select object, and casting it to a string assembles the SQL without running it. Use it the moment a collection returns the wrong rows or takes too long: the aliases at_<code>, at_<code>_default and at_<code>_store tell you exactly which attributes caused joins. Note that addAttributeToSelect does not appear in this SQL at all; its values arrive in the follow-up queries after load().

Example · php
<?php
// app/code/Vendor/EavDemo/Console/Command/ShowSql.php
// Fragment of execute().
declare(strict_types=1);

$collection = $this->collectionFactory->create();
$collection->setStoreId(1);
$collection->addAttributeToSelect(['name', 'price']);
$collection->addAttributeToFilter('visibility', ['in' => [2, 4]]);

// Nothing has run yet. The select is assembled, not executed.
$output->writeln((string) $collection->getSelect());

// SELECT `e`.* FROM `catalog_product_entity` AS `e`
// INNER JOIN `catalog_product_entity_int` AS `at_visibility_default` ...
//  LEFT JOIN `catalog_product_entity_int` AS `at_visibility` ...

$output->writeln((string) $collection->getSize());

The repository alternative, and what it costs

The repository is the supported API and the one to expose to other modules, but it gives you no control over which attributes load, so each item is a fully loaded product. Use it for small result sets, for anything public, and for code that must survive an upgrade. Drop to the collection when you are reading thousands of rows and need two fields from each.

Example · php
<?php
// app/code/Vendor/Catalog/Model/EnabledProducts.php
declare(strict_types=1);

namespace Vendor\Catalog\Model;

use Magento\Catalog\Api\ProductRepositoryInterface;
use Magento\Catalog\Model\Product\Attribute\Source\Status;
use Magento\Framework\Api\SearchCriteriaBuilder;

class EnabledProducts
{
    public function __construct(
        private readonly ProductRepositoryInterface $productRepository,
        private readonly SearchCriteriaBuilder $searchCriteriaBuilder
    ) {
    }

    /**
     * @return \Magento\Catalog\Api\Data\ProductInterface[]
     */
    public function page(int $pageSize, int $currentPage): array
    {
        $criteria = $this->searchCriteriaBuilder
            ->addFilter('status', Status::STATUS_ENABLED)
            ->setPageSize($pageSize)
            ->setCurrentPage($currentPage)
            ->create();

        return $this->productRepository->getList($criteria)->getItems();
    }
}

Related

m2 collectionsm2 eav introm2 eav flatm2 product collectionsm2 security sql

Discussion

  • Be the first to comment on this lesson.