We are showing product list on homepage. The default sorting for the product is name ASC. Now, we want to show product based on country first and then the remaining product as Name ASC. For this, we have added new attribute in product as 'geo_country' as varchar and in admin added 'PH' as value for some product. Next, we have created plugin for Magento\Catalog\Block\Product\ProductList\Toolbar as aroundSetCollection to modify the sorting. Below is the code we have written :

public function aroundSetCollection(
    \Magento\Catalog\Block\Product\ProductList\Toolbar $subject,
    \Closure $proceed,
) {
    $direction = $subject->getCurrentDirection();
    $writer = new \Zend_Log_Writer_Stream(BP . '/var/log/toolbar.log');
    $logger = new \Zend_Log();
     $case = new \Zend_Db_Expr('CASE when geo_country = "PH" then 1 else 2 end');
    // $collection->getSelect()->order($case);

    $result = $proceed($collection);

    $select = $collection->load()->getSelect();
    $selectOrder = $select->getPart(\Magento\Framework\DB\Select::ORDER);
    foreach($selectOrder as $k => $_order){
        if( is_array($_order) ){
            $select->order($_order[0].' '.$_order[1]);

    // $collection->getSelect()->order(
    //     new \Zend_Db_Expr("CASE WHEN your_condition THEN 0 ELSE 1 END"),
    //     \Magento\Framework\DB\Select::SQL_ASC
    // );

    //echo $collection->load()->getSelect();exit;

    return $result;

We have tried multiple option as adding setOrder with case expression and attributeToSort with case expression. But both this didn't work as expected. Sorting query was not appended in product load collection. Later after trying multiple solution, we are able to see the case sorting option in product load collection with above code. But now the problem is that in list.phtml, before foreach, we are able to see the collection as expected but in foreach loop we are getting different data. There is no change in list.phtml file. We tried with luma theme as well. In both scenario in foreach loop, data is different. You guidance will be appreciated.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.