6

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?

5 Answers 5

6

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;
}
1
  • 2
    This is a good solution, except you should use a after method interceptor when you can as it's better for performance reasons. Also, separate orders for Search and Catalog pages can be added by adding the plugin to the child classes \Magento\Catalog\Model\Layer\Search and \Magento\Catalog\Model\Category instead.
    – micwallace
    May 7, 2020 at 12:09
4

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;
  }
}
3
  • 1
    Perfect answer!!! Made my day!!! +1 :) Jul 28, 2021 at 6:36
  • 1
    @SagarPPanchal which version you are using, this is not working for in M2.4.0 Jul 28, 2021 at 12:02
  • @WajahatBashir Magento ver. 2.3.5-p2 Jul 28, 2021 at 12:06
2

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.

5
  • Hi @Abhinav where i have to add this code in layer.php file Jun 5, 2018 at 13:55
  • @shivashankarm have you added this?
    – Mesk
    Jul 16, 2018 at 6:00
  • @Abhinav kindly let me know where to add this?
    – Mesk
    Jul 16, 2018 at 6:01
  • I was added it in given file layer.php file Jul 16, 2018 at 6:10
  • Is it working for you? If yes, kindly let me know the exact path and the function to add this code. Because I have found many functions in layer.php file under vendor/magento/module-catalog/Block/Product directory
    – Mesk
    Jul 16, 2018 at 6:13
2
  1. Make a preference for class "Magento\Catalog\Block\Product\ListProduct"
  2. Find line $this->_productCollection = $layer->getProductCollection();
  3. Add this after that line $this->_productCollection->getSelect()->order('is_salable DESC');

This may be not the perfect solution but it works.

4
  • this line $this->_productCollection = $layer->getProductCollection(); not found in magento 2.2.5
    – Gagan
    Jun 17, 2019 at 6:24
  • @Gagan Line has sligtly changed $collection = $layer->getProductCollection();
    – Macas
    Jun 18, 2019 at 8:06
  • After add this line $this->_productCollection->getSelect()->order('is_salable DESC'); in below $collection = $layer->getProductCollection(); show error
    – Gagan
    Jun 20, 2019 at 13:53
  • Have you done any debugging at all? As I see it now - you are just blindly add code from stackoverflow and say that it doens't work. You DO NOT HAVE this value $this->_productCollection yet, because you are currently creating it. Try using $collection in this cases. $collection wit be set to the $this->_productCollection later on.
    – Macas
    Jun 21, 2019 at 20:32
1

You can use this free extension to show out of stock items at the end of the catalog.

https://github.com/bikashkaushik/magento2-OutOfStockLast

1
  • This one is actually works (Magento version 2.4.3-p2)
    – Akif
    Jun 7, 2022 at 9:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.