Description
Preconditions (*)
- Magento v2.4.2.
- A catalog with one parent anchor category and a few subcategories for it.
- Products assigned to both the parent category and to its subcategories.
- Different product positions in each category the product is in.
Steps to reproduce (*)
- Set up a small catalog with the conditions mentioned above: one or more products that are present in the parent category and in its children.
- For each product, assign a different position for each category that product is in.
- Add an admin side template or component that displays a product list, filtered by the parent category. This can be achieved with an admin side PHTML file or by using a Page Builder component such as the products list.
It's important that that widget is processed in the default store (Store::DEFAULT_STORE_ID), so the _applyZeroStoreProductLimitations call is used in the following code.
In \Magento\Catalog\Model\ResourceModel\Product\Collection:
public function addCategoryFilter(\Magento\Catalog\Model\Category $category)
{
$this->_productLimitationFilters['category_id'] = $category->getId();
if ($category->getIsAnchor()) {
unset($this->_productLimitationFilters['category_is_anchor']);
} else {
$this->_productLimitationFilters['category_is_anchor'] = 1;
}
if ($this->getStoreId() == Store::DEFAULT_STORE_ID) {
$this->_applyZeroStoreProductLimitations();
} else {
$this->_applyProductLimitations();
}
return $this;
}Expected result (*)
The products list is rendered in the admin.
Actual result (*)
The products list rendering fails with the following error message:
'Item (Magento\Catalog\Model\Product\Interceptor) with the same ID "<ID>" already exists.'
(where <ID> is a product ID)
The issue can be tracked down to the _applyZeroStoreProductLimitations call mentioned above:
protected function _applyZeroStoreProductLimitations()
{
$filters = $this->_productLimitationFilters;
$categories = $this->getChildrenCategories((int)$filters['category_id']);
$conditions = [
'cat_pro.product_id=e.entity_id',
$this->getConnection()->quoteInto(
'cat_pro.category_id IN (?)',
$categories
),
];
$joinCond = join(' AND ', $conditions);
$fromPart = $this->getSelect()->getPart(\Magento\Framework\DB\Select::FROM);
if (isset($fromPart['cat_pro'])) {
$fromPart['cat_pro']['joinCondition'] = $joinCond;
$this->getSelect()->setPart(\Magento\Framework\DB\Select::FROM, $fromPart);
} else {
$this->getSelect()->join(
['cat_pro' => $this->getTable('catalog_category_product')],
$joinCond,
['cat_index_position' => 'position']
);
}
$this->_joinFields['position'] = ['table' => 'cat_pro', 'field' => 'position'];
return $this;
}The JOIN with the catalog_category_product generates a SQL query like this:
SELECT
DISTINCT `e`.*,
/* ... */
`cat_pro`.`position` AS `cat_index_position`
FROM `catalog_product_entity` AS `e`
/* ... */
INNER JOIN `catalog_category_product` AS `cat_pro`
ON cat_pro.product_id = e.entity_id
AND cat_pro.category_id IN (<LIST OF CATEGORY IDS>)
/* ... */(Where <LIST OF CATEGORY IDS> includes the parent category and its children)
Now, this query is supposed to return 1 row per product. But the join with catalog_category_product can return multiple entries per product, if the product has different positions in different categories. It can even be reproduced by running the simplified query above with the appropriate category IDs. Even though the query has a DISTINCT clause, the fact that a product can have different positions will make it return duplicate entries.
This, in turn, will cause an exception when adding the product to the collection, in \Magento\Framework\Data\Collection::addItem.
A workaround can be implemented by replacing the JOIN above with the following via a patch:
protected function _applyZeroStoreProductLimitations()
{
$filters = $this->_productLimitationFilters;
$categories = $this->getChildrenCategories((int)$filters['category_id']);
$categoryProductSelect = $this->getConnection()->select();
$categoryProductSelect->from("catalog_category_product");
$categoryProductSelect->reset(\Magento\Framework\DB\Select::ORDER);
$categoryProductSelect->reset(\Magento\Framework\DB\Select::LIMIT_COUNT);
$categoryProductSelect->reset(\Magento\Framework\DB\Select::LIMIT_OFFSET);
$categoryProductSelect->reset(\Magento\Framework\DB\Select::COLUMNS);
$categoryProductSelect->columns([
"product_id" => "product_id",
"min_position" => new \Zend_Db_Expr("MIN(position)")
]);
$categoryProductSelect->where("category_id IN (?)", $categories);
$categoryProductSelect->group("product_id");
$joinCond = "cat_pro.product_id = e.entity_id";
$fromPart = $this->getSelect()->getPart(\Magento\Framework\DB\Select::FROM);
if (isset($fromPart['cat_pro'])) {
$fromPart['cat_pro']['joinCondition'] = $joinCond;
$this->getSelect()->setPart(\Magento\Framework\DB\Select::FROM, $fromPart);
} else {
$this->getSelect()->join(
['cat_pro' => $categoryProductSelect],
$joinCond,
['cat_index_position' => 'min_position']
);
}
$this->_joinFields['position'] = ['table' => 'cat_pro', 'field' => 'min_position'];
return $this;
}This JOIN will return one entry per product and will pick only one position for each one.
Note that I grouped the products by the minimum position they have in any of the categories they're in. A more sophisticated solution might prefer a specific category (perhaps the one used for the filtering) over the other ones.
Please provide Severity assessment for the Issue as Reporter. This information will help during Confirmation and Issue triage processes.
- Severity: S0 - Affects critical data or functionality and leaves users without workaround.
- Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
- Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
- Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
- Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “usability”.
I marked this as an S1 because it affects any admin area where filtered product lists are being used. This is an issue that will show up after the catalog has been used for a while (products might have different positions due to the normal catalog administration), and will break the admin rendering of the content areas that include those products.