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?


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.

  • Hi @Abhinav where i have to add this code in layer.php file – shivashankar m Jun 5 '18 at 13:55
  • @shivashankarm have you added this? – Mesk Jul 16 '18 at 6:00
  • @Abhinav kindly let me know where to add this? – Mesk Jul 16 '18 at 6:01
  • I was added it in given file layer.php file – shivashankar m Jul 16 '18 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 '18 at 6:13
  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.

  • this line $this->_productCollection = $layer->getProductCollection(); not found in magento 2.2.5 – Gagan Jun 17 '19 at 6:24
  • @Gagan Line has sligtly changed $collection = $layer->getProductCollection(); – Macas Jun 18 '19 at 8:06
  • After add this line $this->_productCollection->getSelect()->order('is_salable DESC'); in below $collection = $layer->getProductCollection(); show error – Gagan Jun 20 '19 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 '19 at 20:32

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;

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.