How set order with custom field column magento 2

Question

I want to setOrder with custom field column in get list product. I override toolbar.php but it's not working

di.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">

<type name="Magento\Catalog\Block\Product\ProductList\Toolbar">
        <plugin name="Namespace_Company::addPriceDecendingFilterInToolbar" type="Namespace\Product\Plugin\Product\ProductList\Toolbar" />
    </type>

</config>

Namespace\Product\Plugin\Product\ProductList\Toolbar in Toolbar.php

namespace Namespace\Product\Plugin\Product\ProductList;
class Toolbar
    {
    /**
     * Plugin
     *
     * @param \Magento\Catalog\Block\Product\ProductList\Toolbar $subject
     * @param \Closure $proceed
     * @param \Magento\Framework\Data\Collection $collection
     * @return \Magento\Catalog\Block\Product\ProductList\Toolbar
     */
    public function aroundSetCollection(
        \Magento\Catalog\Block\Product\ProductList\Toolbar $subject,
        \Closure $proceed,
        $collection
    ) {
        $currentOrder = $subject->getCurrentOrder();


            $subquery = new \Zend_Db_Expr("<query get min price>");                       



$collection->getSelect()->columns(array('price_sort' => $subquery));
//to debug



        $result = $proceed($collection);

        if ($currentOrder) {
            if ($currentOrder == 'price_high') {
                 $subject->getCollection()->setOrder('price_sort', ' DESC');
               # $subject->getSelect()->order('price_sort DESC');
            } elseif ($currentOrder == 'price_low') {
                $subject->getCollection()->setOrder('price_sort', 'asc');
            }elseif ($currentOrder == 'price_low') {
                $subject->getCollection()->setOrder('position', 'desc');
            }elseif ($currentOrder == 'bestseller') {  
                $subject->getCollection()->setOrder('price', 'asc');
            }
        }

        return $result;
    }

    public function aftergetAvailableOrders()
    {
        $sortFilter=array(
                            'price_high'=>__('Price High To Low'),
                            'price_low'=>__('Price Low To High'),
                            'position'=>__('Newlastest'),
                            'bestseller'=>__('Bestseller'),

                            );
        return $sortFilter;
    }
}

In product/list.phtml, I debug query but in order by it's not showing.

I want to show my query <query sql> order by price_sort DESC

But if in toolbar.php, I replace

$subject->getCollection()->setOrder('price_sort', ' DESC');

to

$subject->getCollection()->setOrder('price', ' DESC');

and then I debug the query, then in query it shows

order by price DESC

I want use column price_sort in setOrder.

Answer 1

Whenever you try to use setOrder for a custom column it will take no effect.

$subject->getCollection()->setOrder('price_sort', ' DESC');

You instead need to access the select directly

$subject->getCollection()->getSelect()->order('price_sort DESC');

It should be noted that a side effect of adding order queries this way will be placed before all order queries placed using the setOrder method.