4

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?

Improve this question
5

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;
}
Improve this answer
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 '20 at 12:09
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.

Improve this answer
5
  • 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
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.

Improve this answer
4
  • 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
2

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;
  }
}
Improve this answer

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.