Loading Products Efficiently

When to use a product collection instead of a repository, what addAttributeToSelect changes, and how to page a large catalog.

Syntax$collection->addAttributeToSelect($attribute, $joinType = false)

There are two ways to load many products. ProductRepositoryInterface::getList() takes a SearchCriteria and returns ProductInterface objects; it is the right choice for anything exposed over an API and for code other developers will extend. A collection, Magento\Catalog\Model\ResourceModel\Product\Collection, is the lower-level object that builds the SQL, and it is the only one with the catalog-specific helpers such as price data and store availability filtering.

addAttributeToSelect

A product collection selects almost nothing by default. Each attribute you name is fetched: a static one such as sku comes from the entity table in the main select, an EAV attribute such as name costs an extra query against its value table. An attribute you do not name returns null from its getter rather than raising an error, so a listing template printing $product->getColor() quietly prints nothing.

addAttributeToSelect('*') works and is the usual reason a category page is slow. Name the attributes you need.

The queries a loop makes

The collection itself is a handful of queries. The loop body is where cost appears: getCategoryIds(), getMediaGalleryImages() and getTypeInstance()->getChildren() each query once per product.

Paging and filters

setPageSize() and setCurPage() add LIMIT and OFFSET. Build a fresh collection for each page; a loaded one holds every item in memory. A value in a filter is not escaped for you, so use the array condition form, addFieldToFilter('sku', ['in' => $skus]), and bind on a raw select. getSelect()->__toString() prints the SQL when a filter misbehaves.

Example

Example · php
<?php
// app/code/Vendor/Catalog/Model/SkuExporter.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 SkuExporter
{
    private const PAGE_SIZE = 500;

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

    /**
     * @return iterable<array{sku: string, name: string}>
     */
    public function export(int $storeId): iterable
    {
        $page = 1;

        do {
            // A new collection per page, so loaded items are not kept.
            $collection = $this->collectionFactory->create();
            $collection->addStoreFilter($storeId)
                ->addAttributeToSelect(['sku', 'name'])
                ->addAttributeToFilter('status', ['eq' => Status::STATUS_ENABLED])
                ->setPageSize(self::PAGE_SIZE)
                ->setCurPage($page);

            foreach ($collection as $product) {
                yield [
                    'sku' => (string)$product->getSku(),
                    'name' => (string)$product->getName(),
                ];
            }

            $lastPage = $collection->getLastPageNumber();
            $page++;
        } while ($page <= $lastPage);
    }
}

// Called from a console command or a cron job. addStoreFilter() both scopes
// attribute values to that store view and limits the result to products
// assigned to its website. Nothing here writes, so no reindex is needed.

When to use it

  • Export every enabled SKU and name from a 200,000-product catalog without exhausting memory.
  • Work out why a listing template renders blank values for a custom attribute.
  • Decide between a collection and the repository for a new service another module will call.
  • Track down the extra query per row that makes a category page slow.

More examples

The same query through the repository

Use the repository when the result crosses a module boundary: a web API endpoint, a GraphQL resolver, a service another team calls. It returns fully loaded products with every attribute, which is heavier than a collection with two attributes selected, but it is stable across upgrades and other modules can extend it through plugins. Note the paging method is setCurrentPage() here and setCurPage() on a collection; the two APIs do not share names.

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

namespace Vendor\Catalog\Model;

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

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

    /**
     * @return ProductInterface[]
     */
    public function page(int $pageNumber, int $pageSize): array
    {
        $sortOrder = $this->sortOrderBuilder
            ->setField('entity_id')
            ->setAscendingDirection()
            ->create();

        $criteria = $this->searchCriteriaBuilder
            ->addFilter('status', Status::STATUS_ENABLED)
            ->addSortOrder($sortOrder)
            ->setPageSize($pageSize)
            ->setCurrentPage($pageNumber)
            ->create();

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

Filtering without building SQL by hand

Both forms are safe; neither is safe by accident. addFieldToFilter() with an array condition passes the values to the adapter, which quotes them, while addFieldToFilter('sku', "IN ('" . $input . "')") hands raw input straight into the query. On a raw select, the ? placeholder with an array expands into a quoted list. getAllIds() is useful here because it runs one slim query and never instantiates product models.

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

namespace Vendor\Catalog\Model;

use Magento\Catalog\Model\ResourceModel\Product\CollectionFactory;
use Magento\Framework\App\ResourceConnection;

class SkuLookup
{
    public function __construct(
        private readonly CollectionFactory $collectionFactory,
        private readonly ResourceConnection $resourceConnection
    ) {
    }

    /**
     * @param string[] $skus
     * @return int[]
     */
    public function idsBySku(array $skus): array
    {
        // Array condition form: the adapter quotes each value.
        $collection = $this->collectionFactory->create();
        $collection->addFieldToFilter('sku', ['in' => $skus]);

        return array_map('intval', $collection->getAllIds());
    }

    /**
     * @param string[] $skus
     * @return string[]
     */
    public function rawLookup(array $skus): array
    {
        // On a raw select, bind. Never concatenate the value into the string.
        $connection = $this->resourceConnection->getConnection();
        $select = $connection->select()
            ->from($this->resourceConnection->getTableName('catalog_product_entity'), ['sku'])
            ->where('sku IN (?)', $skus);

        return $connection->fetchCol($select);
    }
}

Seeing the SQL a collection will run

This is the first thing to do when a collection returns the wrong rows or too many. Printing the select shows exactly which joins the filters added, which is how you discover that a store filter or a website join is missing. Call getSelect() before the foreach: iterating loads the collection, and a filter added after load has no effect and raises no error, which is its own source of confusion.

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

namespace Vendor\Catalog\Model;

use Magento\Catalog\Model\ResourceModel\Product\CollectionFactory;
use Psr\Log\LoggerInterface;

class CollectionDebug
{
    public function __construct(
        private readonly CollectionFactory $collectionFactory,
        private readonly LoggerInterface $logger
    ) {
    }

    public function logCategoryQuery(int $categoryId): void
    {
        $collection = $this->collectionFactory->create();
        $collection->addAttributeToSelect(['sku', 'name'])
            ->addCategoriesFilter(['eq' => $categoryId]);

        // Call this before iterating. After load() the select is already run.
        $this->logger->debug($collection->getSelect()->__toString());
    }
}

// Writes the SELECT statement to var/log/debug.log, which only records
// debug entries when developer mode is on (bin/magento deploy:mode:set
// developer) or Stores > Configuration > Advanced > Developer enables it.

Related

m2 collectionsm2 repositoriesm2 search criteriam2 eav queriesm2 perf mysql

Discussion

  • Be the first to comment on this lesson.