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.