This article describes the low-level mechanisms for product retrieval in Magento Open Source 2.4.6 for category listing (PLP) and search pages. It is relevant to the default Luma theme and custom themes inherited from it. Other themes may override these mechanisms, and the article also discusses the specifics of product retrieval in the Hyva theme. At the end of the article, there is a note about a bug in Magento 2.4.5.

Product collection initialization occurs in the category.products block, which calls the getProductListHtml() method:

// vendor/magento/module-catalog/view/frontend/templates/category/products.phtml
<?php
/**
 * Copyright © Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
?>
<?php
/**
 * Category view template
 *
 * @var $block \Magento\Catalog\Block\Category\View
 */
?>
<?php if (!$block->isContentMode() || $block->isMixedMode()) :?>
   <?= $block->getProductListHtml() ?>
<?php endif; ?>

The _beforeToHtml() method is called by a chain, inside this method a collection is initialized with a call to $collection->load(): 

ListProduct.php:194,
Magento\Catalog\Block\Product\ListProduct->_beforeToHtml()
#1 AbstractBlock.php:1094,
Magento\Framework\View\Element\AbstractBlock->Magento\Framework\View\Element\
{closure:/var/www/source/vendor/magento/framework/View/Element/AbstractBlock.php
:1089-1096}()
#2 AbstractBlock.php:1099,
Magento\Framework\View\Element\AbstractBlock->_loadCache()
#3 AbstractBlock.php:660,
Magento\Framework\View\Element\AbstractBlock->toHtml()
#4 Layout.php:578, Magento\Framework\View\Layout->_renderBlock()
#5 Layout.php:555, Magento\Framework\View\Layout->renderNonCachedElement()
#6 Layout.php:510, Magento\Framework\View\Layout->renderElement()
#7 AbstractBlock.php:507,
Magento\Framework\View\Element\AbstractBlock->getChildHtml()
#8 View.php:100, Magento\Catalog\Block\Category\View->getProductListHtml()

// Magento\Catalog\Block\Product\ListProduct
protected function _beforeToHtml()
{
    $collection = $this->_getProductCollection();

    $this->addToolbarBlock($collection);

    if (!$collection->isLoaded()) {
        $collection->load();
    }

    $categoryId = $this->getLayer()->getCurrentCategory()->getId();

    if ($categoryId) {
        foreach ($collection as $product) {
            $product->setData('category_id', $categoryId);
        }
    }

   return parent::_beforeToHtml();
}

Subsequently, the _renderFilters() method is called through a chain of methods, where filters are prepared if not already done:

#0 AbstractDb.php:343, Magento\Framework\Data\Collection\AbstractDb->_renderFilters()
#1 Collection.php:582, Magento\CatalogSearch\Model\ResourceModel\Fulltext\Collection->_renderFilters()
#2 AbstractCollection.php:929, Magento\Eav\Model\Entity\Collection\AbstractCollection->load()
#3 Collection.php:801, Magento\Catalog\Model\ResourceModel\Product\Collection->load()
...

// Magento\CatalogSearch\Model\ResourceModel\Fulltext\Collection
protected function _renderFilters()
{
   if ($this->_isFiltersRendered) {
       return $this;
   }

   $this->_renderFiltersBefore();

   foreach ($this->_filters as $filter) {
       switch ($filter['type']) {
           // ...
       }
   }
   $this->_isFiltersRendered = true;
   return $this;
}

Hyva theme

In the Hyva theme, the catalog.leftnav block is rendered before category.products, so _renderFilters() is called for the first time from \Magento\CatalogSearch\Model\ResourceModel\Fulltext\Collection::getFacetedData(). Therefore, when the collection is initialized in the category.products block, filters are already rendered.

Next, the Magento\Framework\Search\Search::search() method is called through a chain.

#0 Search.php:85, Magento\Framework\Search\Search->search()
#1 Collection.php:464, Magento\CatalogSearch\Model\ResourceModel\Fulltext\Collection->_renderFiltersBefore()
...

This method sets filters and sorting and sends a query to the search engine. In Magento 2.4.6, OpenSearch is the default search engine, while Elasticsearch was used prior to Magento 2.4.6.

// Magento\Framework\Search\Search::search()
public function search(SearchCriteriaInterface $searchCriteria)
{
    ...
    // Filtering
    foreach ($searchCriteria->getFilterGroups() as $filterGroup) {
        foreach ($filterGroup->getFilters() as $filter) {
            $this->addFieldToFilter($filter->getField(), $filter->getValue());
        }
    }


    // Limiting response to current page and current page size
    $this->requestBuilder->setFrom($searchCriteria->getCurrentPage() * $searchCriteria->getPageSize());
    $this->requestBuilder->setSize($searchCriteria->getPageSize());

    // Set sort orders
    if (method_exists($this->requestBuilder, 'setSort')) {
        $this->requestBuilder->setSort($searchCriteria->getSortOrders());
    }
    $request = $this->requestBuilder->create();

The search engine is determined by the catalog/search/engine setting in Stores -> Configuration -> Catalog -> Catalog -> Catalog search -> Search engine. This setting is retrieved in the \Magento\Search\Model\EngineResolver::getCurrentSearchEngine() method.

Following the chain, the query() method of the search engine adapter (either \Magento\OpenSearch\SearchAdapter\Adapter or \Magento\Elasticsearch7\SearchAdapter\Adapter) is called.

#0 Adapter.php:111, \Magento\OpenSearch\SearchAdapter\Adapter->query()
#1 SearchEngine.php:42, Magento\Search\Model\SearchEngine->search()
#2 Search.php:85, Magento\Framework\Search\Search->search()
...

This method sends a query to the search engine and receives a response containing a list of product IDs that match the filter and are sorted according to the order.

// Both adapters have identical query() method implementation
// Magento\OpenSearch\SearchAdapter\Adapter::query()
// Magento\Elasticsearch7\SearchAdapter\Adapter::query()
public function query(RequestInterface $request) : QueryResponse
{
    ...
    $query = $this->mapper->buildQuery($request);
    ...

    try {
        $rawResponse = $client->query($query);
    } catch (\Exception $e) {
        ...
    }

    $rawDocuments = $rawResponse['hits']['hits'] ?? [];
    $queryResponse = $this->responseFactory->create(
        [
            'documents' => $rawDocuments,
            'aggregations' => $aggregationBuilder->build($request, $rawResponse),
            'total' => $rawResponse['hits']['total']['value'] ?? 0
        ]
    );
    return $queryResponse;
}

The structure of the $query object is as follows:

Array
(
  [index] => magento2_product_1
  [type] => document
  [body] => Array
    (
      [from] => 0 // starting element
      [size] => 12 // number of products
      [stored_fields] => _none_
      [docvalue_fields] => Array
        (
          [0] => _id
          [1] => _score
        )
      [sort] => Array
        (
          [0] => Array
            (
              [position_category_4] => Array // sort field
                (
                  [order] => asc // sort order
                )
            )
        )
      [query] => Array
        (
          [bool] => Array
            (
              [must] => Array // search terms
                (
                  [0] => Array
                    (
                      [term] => Array
                        (
                          [category_ids] => 4
                        )
                    )
                  [1] => Array
                    (
                      [terms] => Array
                        (
                          [visibility] => Array
                            (
                              [0] => 2
                              [1] => 4
                            )
                        )
                    )
                )
            )
        )
      [aggregations] => Array // groups by different fields
        (
          [price_bucket] => Array 
            (
              [extended_stats] => Array
                (
                  [field] => price_0_1
                )
            )
          ... other aggregations
        )
    )
  [track_total_hits] => 1
)

The query() method returns a QueryResponse, which, in turn, is returned to the _renderFiltersBefore() method, where it is assigned to the searchResult field. The searchResult contains sorted products.

// Magento\CatalogSearch\Model\ResourceModel\Fulltext\Collection 
protected function _renderFiltersBefore()
{
    ...
    $this->searchResult = $this->getSearch()->search($searchCriteria);
    ...
    $this->getSearchResultApplier($this->searchResult)->apply();
    parent::_renderFiltersBefore();
}

In the apply() method of Magento\Elasticsearch\Model\ResourceModel\Fulltext\Collection\SearchResultApplier, the product IDs are extracted from the searchResult and embedded into the SQL query:

// Magento\Elasticsearch\Model\ResourceModel\Fulltext\Collection\SearchResultApplier
public function apply()
{
    // ...
    $items = $this->sliceItems($this->searchResult->getItems(), $this->size, $this->currentPage);
    $ids = [];
    foreach ($items as $item) {
        $ids[] = (int)$item->getId();
    }
    $orderList = implode(',', $ids);
    $this->collection->getSelect()
        ->where('e.entity_id IN (?)', $ids)
        ->reset(\Magento\Framework\DB\Select::ORDER)
        ->order(new \Zend_Db_Expr("FIELD(e.entity_id,$orderList)"));
}

The final SQL query looks something like this (example shown for illustrative purposes):

SELECT 
  `e`.*, 
  `price_index`.`price`, 
  `price_index`.`tax_class_id`, 
  `price_index`.`final_price`, 
  IF(
    price_index.tier_price IS NOT NULL, 
    LEAST(
      price_index.min_price, price_index.tier_price
    ), 
    price_index.min_price
  ) AS `minimal_price`, 
  `price_index`.`min_price`, 
  `price_index`.`max_price`, 
  `price_index`.`tier_price`, 
  IFNULL(review_summary.reviews_count, 0) AS `reviews_count`, 
  IFNULL(
    review_summary.rating_summary, 0
  ) AS `rating_summary`, 
  `stock_status_index`.`stock_status` AS `is_salable` 
FROM 
  `catalog_product_entity` AS `e` 
  INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id 
  AND price_index.customer_group_id = 0 
  AND price_index.website_id = '1' 
  LEFT JOIN `review_entity_summary` AS `review_summary` ON e.entity_id = review_summary.entity_pk_value 
  AND review_summary.store_id = 1 
  AND review_summary.entity_type = (
    SELECT 
      `review_entity`.`entity_id` 
    FROM 
      `review_entity` 
    WHERE 
      (entity_code = 'product')
  ) 
  INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id 
WHERE 
  (
    stock_status_index.stock_status = 1
  ) 
  AND (
    e.entity_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
  ) 
ORDER BY 
  -- Order of IDs in this array is defined by search engine and depends on sort
  -- order. For example IDs from sample data sorted by name will look like
  -- ‘9,3,12,11,6,7,1,13,14,5,10,2’
FIELD(
    e.entity_id, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
  )

To sum up, the process of retrieving products in Magento involves:

  1. creating a list of filters and sorting criteria
  2. passing them to a search engine
  3. receiving a list of matching product IDs
  4. embedding the list of product IDs into an SQL query
  5. retrieving the product data from the database.

Bug in Magento 2.4.5

There is a bug in Magento 2.4.5, that causes all out-of-stock products in a product listing to appear regardless of applied filters. Additionally, these products are placed at the end of the list, disrupting user sorting. For example, when sorting alphabetically, in-stock products are sorted first, followed by out-of-stock products sorted alphabetically. A potential solution to this bug is to apply a patch that disables the plugin responsible for recalculating product counts. Here's the suggested patch:

@package magento/module-inventory-catalog

--- etc/di.xml	2023-09-28 10:52:43.062601405 +0000
+++ etc/di.xml	2023-09-28 10:52:24.054826335 +0000
@@ -189,6 +189,7 @@
         </arguments>
     </type>
     <type name="Magento\Catalog\Block\Product\ProductList\Toolbar">
-        <plugin name="update_toolbar_count" type="Magento\InventoryCatalog\Plugin\Catalog\Block\ProductList\UpdateToolbarCount"/>
+<!--     plugin should be disabled only in magento 2.4.5; after upgrade to 2.4.6 this patch should be removed-->
+        <plugin name="update_toolbar_count" type="Magento\InventoryCatalog\Plugin\Catalog\Block\ProductList\UpdateToolbarCount" disabled="true"/>
     </type>
 </config>