Skip to content

Custom Sorting in product collection

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 MagentoCatalogBlockProductProductListToolbar as aroundSetCollection to modify the sorting.
Below is the code we have written :

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

    //$collection->setOrder($case);
    
    
    $result = $proceed($collection);

    $select = $collection->load()->getSelect();
    $selectOrder = $select->getPart(MagentoFrameworkDBSelect::ORDER);
    $select->reset(MagentoFrameworkDBSelect::ORDER);
    $select->order($case);
    foreach($selectOrder as $k => $_order){
        if( is_array($_order) ){
            $select->order($_order[0].' '.$_order[1]);
        }
    }
    $logger->info(print_r($selectOrder,true));

    //print_r($selectOrder);
    //exit;
    
    //$collection->getSelect()->reset(MagentoFrameworkDBSelect::ORDER);
    // $collection->getSelect()->order(
    //     new Zend_Db_Expr("CASE WHEN your_condition THEN 0 ELSE 1 END"),
    //     MagentoFrameworkDBSelect::SQL_ASC
    // );

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

    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.