I want to display out of stock products at the end of the listing page.
I have followed the link here, getting error: Exception #0 (Zend_Db_Select_Exception): You cannot define a correlation name '_inventory_table' more than once
How can I do?
Magento Stack Exchange is a question and answer site for users of the Magento e-Commerce platform. It only takes a minute to sign up.
Sign up to join this communityI want to display out of stock products at the end of the listing page.
I have followed the link here, getting error: Exception #0 (Zend_Db_Select_Exception): You cannot define a correlation name '_inventory_table' more than once
How can I do?
This is the only that worked for me. It sorts by the selected sorted attribute AND set the out of stock products in the end of the listings:
You need to create a Plugin (type="\Magento\Catalog\Model\Layer") with the following function:
/**
* Retrieve current layer product collection
*
* @return \Magento\Catalog\Model\ResourceModel\Product\Collection
*/
public function aroundgetProductCollection(
\Magento\Catalog\Model\Layer $subject,
callable $proceed
) {
$collection = $proceed();
$collection->getSelect()->order('is_salable DESC');
return $collection;
}
Use a custom module plugin to add sort criteria to the select. Uses afterGetProductCollection()
for performance.
app/code/My/Namespace/etc/module.xml
<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../../../lib/internal/Magento/Framework/Module/etc/module.xsd">
<module name="My_Namespace" setup_version="3.1.8" active="true">
<sequence>
<module name="Magento_Catalog"/>
<module name="Magento_CatalogSearch"/>
</sequence>
</module>
</config>
app/code/My/Namespace/etc/di.xml
<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../../../../lib/internal/Magento/Framework/ObjectManager/etc/config.xsd">
<type name="Magento\Catalog\Model\Layer">
<plugin name="my_namespace_catalog_model_layer" type="My\Namespace\Plugin\Catalog\Model\Layer" sortOrder="1000" />
</type>
</config>
app/code/My/Namespace/Plugin/Catalog/Model/Layer.php
<?php
namespace My\Namespace\Plugin\Catalog\Model;
/**
* Class Layer
* @package My\Namespace\Plugin\Catalog\Model\Layer
*/
class Layer
{
/**
* Sort items that are not salable last
*
* @return \Magento\Catalog\Model\ResourceModel\Product\Collection
*/
public function afterGetProductCollection(
\Magento\Catalog\Model\Layer $subject,
\Magento\Catalog\Model\ResourceModel\Product\Collection $collection
) {
$collection->getSelect()->order('is_salable DESC');
return $collection;
}
}
Go to /namespace/Catalog/Model/layer.php
and add given below code.
$collection->joinField('in_stock', 'cataloginventory_stock_item', 'is_in_stock', 'product_id=entity_id', 'in_stock>=0', 'left')->setOrder('in_stock','desc');
Note: This is working on default magento.
This may be not the perfect solution but it works.
You can use this free extension to show out of stock items at the end of the catalog.