How to sort product at laravel by relationship?

I filter and list the products listed with the code samples on my model page below with some data from the user.

I want to sort the listed products according to their prices. However, as it is seen in the minprice-maxprice sample code block, relation depends on several conditions.

From the period consisting of postFrom and postTo dates received by the user, if the daily is 0, it should be listed according to the old_daily price, if the daily is not 0, it should be listed according to the daily price.

How can I do that?

my model page

public $belongsTo = [
    'price' => [
        'ac\prices\models\Price',
        'key' => 'id',
        'otherKey' => 'pro_id',
    ],
]

public static $allowedSortingOptions = array (
    'name desc' => 'Name - desc',
    'name asc' => 'Name - asc',
    'price desc' => 'Price - desc',
    'price asc' => 'Price - asc',
);

public function scopeListFrontEnd($query, $options = []){

    extract(array_merge([
        'page' => 1,
        'perPage' => 10,
        'sort' => 'created_at desc',
        'postFrom' => null,
        'postTo' => null,
        'minPrice' => null,
        'maxPrice' => null,
    ], $options));

    if(!is_array ($sort)){
        $sort = [$sort];
    }

    foreach ($sort as $_sort){
        if(in_array($_sort, array_keys(self::$allowedSortingOptions))){
            $parts = explode(' ', $_sort);

            if(count($parts) < 2){
                array_push($parts, 'desc');
            }

            list($sortField, $sortDirection) = $parts;

            $query->orderBy($sortField, $sortDirection);

        }
    }

    if($minPrice != null) {

        if(!is_array($minPrice)){
            $minPrice = [$minPrice];
        }

        foreach ($minPrice as $mnPrice){

            $query->whereHas('price', function($q) use ($mnPrice,$maxPrice,$postFrom,$postTo){
                $q->where('daily', '==', '0')
                ->where(function( $query ) use ( $mnPrice, $maxPrice ) {
                    $query->where('old_daily', '>=', $mnPrice);
                    $query->where('old_daily', '<=', $maxPrice[0]);
                });
                $q->orWhere('daily', '!=', '0')
                ->where(function( $query ) use ( $mnPrice, $maxPrice ) {
                    $query->where('daily', '>=', $mnPrice);
                    $query->where('daily', '<=', $maxPrice[0]);
                });
                $q->when($postFrom == '0', function ($sq) {
                        $sq->where('id', '>', '0');
                    }, function ($ssq) use ($postFrom, $postTo) {
                        $ssq->where(function($q) use ($postFrom) {
                            $q->whereDate('start_date', '<=', $postFrom[0])
                                ->whereDate('end_date', '>=', $postFrom[0]);
                        })->orWhere(function($q) use ($postTo) {
                            $q->whereDate('start_date', '<=', $postTo[0])
                                ->whereDate('end_date', '>=', $postTo[0]);
                        });
                        
                    });
            });

        }
    
    }

    $lastPage = $query->paginate($perPage, $page)->lastPage();

    if($lastPage < $page){
        $page = 1;
    }
    
    return $query->paginate($perPage, $page);

}

Without trying to decode exactly what you are trying to do here, I would be adding a sub-query select that pulls a sort_price field into the results that you can then orderBy.

$query->selectRaw('CASE WHEN daily = 0 THEN old_daily ELSE daily END as sort_price');

$query->orderByRaw('(SELECT sort_price)');

You can also do this directly in the sort condition as per MYSQL ORDER BY CASE Issue if you don't need this price in your result.

You can do this in the orderByRaw builder method.