sorting multiple fields of the same table

I have a sorting function that, according to the specified table, should filter the data in the order I need..

public function getOrderBy(): array
    {
        $sortOrder = in_array(
            $this->fieldSort,
            ['price_desc'],
            true
        ) ? SORT_DESC : SORT_ASC;

        $by = in_array(
            $this->fieldSort,
            ['price_asc', 'price_desc'],
            true
        ) ? 'hotel_price.price' : null;

        return $by ? [$by => $sortOrder] : [];
    }

$query = self::getActive();

$query->select([self::tableName() . '.*']);

if ($validate && !empty($filter->getOrderBy())) {
  $query->joinWith('hotelPrice');
  $query->orderBy($filter->getOrderBy());
}

The table looks like this:

As a result, sorting for price_asc works as it should, I get values ​​from minimum to large.

But there are some problems with price_desc. It sorts, but not quite right.

The fact is that here an array is taken from the table, let's say for hotel_id = 1, it has values ​​2 and 3. And sorting works by the first element of the array. For price_asc, this is normal, but for price_desc, you need to make sure that sorting starts from the last element of the array.

But I still can’t figure out how to do this, do I need to do this in the sort function itself, or will I have to dig deeper?

UPDATE

It is the array of values, let's say now hotel_id = 5, that has an array of price values ​​[1, 2, 3], and now sorting for asc and desc starts from the first element of the array. And I need for desc to start from the last.

The only idea I have so far is to sort the id column along with the price_range column.

But how do I pass such a value to SQL. Should be something like:

$by = 'hotel_price.id', 'hotel_price.price';

If I make DESC for the id, then the last value will be in the first place in the table and the array will look like this price values ​​[3, 2, 1]

I'll try to explain again, my function, which is in the code, does the following:

>SELECT DISTINCT hotels.*, FROM hotel LEFT JOIN hotel_price ON hotels.id = hotel_price.hotel_id WHERE (hotels.status=1) ORDER BY hotel_price.price DESC

I need to make it so that sorting is applied to id along with price.

And the response should be something like:

>ORDER BY hotel_price.id DESC, hotel_price.price DESC

Need to apply double sorting.

I know it's quite possible for different tables, but is it possible to do it for one table but for different fields?

I output something like this in the query, but in the end only the first field is sorted:

>ORDER BY hotel_price.id DESC, hotel_price.price DESC

===========================

1. I have a table like this

I need two fields here, id and price.

When I apply sorting by only one price field or one id field, then everything works.

2. Here is the query I am running

>SELECT DISTINCT hotels.*, FROM hotel LEFT JOIN hotel_price ON hotels.id = hotel_price.hotel_id WHERE (hotels.status=1) ORDER BY hotel_price.id DESC, hotel_price.price DESC

3. I expect that at first my table will be sorted by id and it will look like this

And the second sorting by the price field will be applied to this sorted table by id

4. But in the end, only the first sort by id works for me, that is, the table becomes the same as in the last screenshot, but the second sort by price is not applied to it.

5. Addition
   If I send a request like this
   >SELECT DISTINCT hotels.*, FROM hotel LEFT JOIN hotel_price ON hotels.id = hotel_price.hotel_id WHERE (hotels.status=1) ORDER BY hotel_price.price DESC, hotel_price.id DESC

Ie, first I'll try to sort by price, then by ID, then on the contrary, it sorts by price, but by id it's gone.

In short, sorting is always applied only to the first field, although I send two in the request.

===========================

I added my table to the post. And when displaying on the frontend, let's say we take a hotel with ID 5, and it has an array with prices [1, 2, 3].

And when I sort hotels by price before hotels are always sorted by price where he takes the first value. In this case it's 1.

When I sort by ASC, everything is displayed correctly, but when I sort by DESC, the first price value is still taken in the sort, and then the sort looks incorrect.

That is, to sort by DESC, I need to get the array with prices from the table in the reverse order [3, 2, 1], so that the largest value is taken for sorting.

You might need to modify the getOrderBy function to sort by the last element of the array when sorting in descending order

Try this using the array_reverse function :

public function getOrderBy(): array
{
    $sortOrder = in_array(
        $this->fieldSort,
        ['price_desc'],
        true
    ) ? SORT_DESC : SORT_ASC;

    $by = in_array(
        $this->fieldSort,
        ['price_asc', 'price_desc'],
        true
    ) ? 'hotel_price.price' : null;

    if ($sortOrder === SORT_DESC) {
        $by = "SUBSTRING_INDEX(hotel_price.price, ',', -1)";
    }

    return $by ? [$by => $sortOrder] : [];
}

If I understand the question correctly, then a second sort is needed.

This is how your first criteria would be price:

public function getOrderBy(): array
    {
        $sortOrder = in_array(
            $this->fieldSort,
            ['price_desc'],
            true
        ) ? SORT_DESC : SORT_ASC;

        $by = in_array(
            $this->fieldSort,
            ['price_asc', 'price_desc'],
            true
        ) ? 'hotel_price.price' : null;

        return $by ? [$by => $sortOrder, 'hotel_price.id' => SORT_ASC] : ['hotel_price.id' => SORT_ASC];
    }

And this is the other way around:

public function getOrderBy(): array
    {
        $sortOrder = in_array(
            $this->fieldSort,
            ['price_desc'],
            true
        ) ? SORT_DESC : SORT_ASC;

        $by = in_array(
            $this->fieldSort,
            ['price_asc', 'price_desc'],
            true
        ) ? 'hotel_price.price' : null;

        return $by ? ['hotel_price.id' => SORT_ASC, $by => $sortOrder] : ['hotel_price.id' => SORT_ASC];
    }

EDIT

Basically you have two sort criterias. It's important to understand that when we sort the r1 and r2 records, then we determine the order of these records, but in the result set r1 and r2 have a single order. We do not order individual fields; we order whole records based on some sort criterias. A sort criteria of

ORDER BY foo desc, bar desc

in plain words means the following:

**
If r1.foo <> r2.foo then the order of r1 and r2 is determined by foo descendingly
else the order of r1 and r2 is determined by bar descendingly
**

I have constructed a test case for this purpose.

Data build

create table hotels(
    id int primary key auto_increment,
    price int not null,
    hotel_id int not null
);

insert into hotels(price, hotel_id)
values
(2, 1),
(3, 1),
(1, 3),
(2, 3),
(2, 4),
(3, 4),
(1, 5),
(2, 5),
(3, 5),
(2, 6);

Fiddle: http://sqlfiddle.com/#!9/99f449/5

Below I discuss several cases, please look into the code and especially to the comments.

order by id desc, price desc

/*
This orders by id, price
We expect for any r1, r2 records that
- if r1.id > r2.id, then r1 is before r2
- if r1.id = r2.id, then we order them by price
    - if r1.price > r2.price then r1 is before r2
    - if r1.price = r2.price then our sort criteria does not determine their order
    - if r1.price < r2.price then r1 is after r2
- if r2.id < r2.id, then r1 is after r2
As we can see above, r1 and r2 have a single order, which is determined by id and it would only be
determined by price if r1.id = r2.id, so, if there is a tie in the order of ids, then price is
the tiebreaker. Yet, the id field is unique (it's even a primary key), so we never end up using
price as an order criteria
*/
select *
from hotels
order by id desc, price desc;

order by price desc, id desc

/*
This orders by price, id
We expect for any r1, r2 records that
- if r1.price > r2.price, then r1 is before r2
- if r1.price = r2.price, then
    - if r1.id > r2.id, then r1 is before r2
    - if r1.id = r2.id, then our sort criteria does not determine their order
    - if r1.id < r2.id, then r1 is after r2
- if r1.price < r2.price, then r1 is after r2
So, our first sort criteria is price and our records are ordered by price, except when their prices
are equal, in which case we break the tie by our second sorting criteria
*/

select *
from hotels
order by price desc, id desc;

order by hotel_id desc, price desc

/*
This orders by hotel_id, price
We expect for any r1, r2 records that
- if r1.hotel_id > r2.hotel_id, then r1 is before r2
- if r1.hotel_id = r2.hotel_id, then we order them by price
    - if r1.price > r2.price then r1 is before r2
    - if r1.price = r2.price then our sort criteria does not determine their order
    - if r1.price < r2.price then r1 is after r2
- if r2.hotel_id < r2.hotel_id, then r1 is after r2
As we can see above, r1 and r2 have a single order, which is determined by hotel_id and it would 
only be determined by price if r1.hotel_id = r2.hotel_id, so, if there is a tie in the order of
hotel ids, then price is the tiebreaker.
*/

select *
from hotels
order by hotel_id desc, price desc;

order by price desc, hotel_id desc

/*
This orders by price, hotel_id
We expect for any r1, r2 records that
- if r1.price > r2.price, then r1 is before r2
- if r1.price = r2.price, then
    - if r1.hotel_id > r2.hotel_id, then r1 is before r2
    - if r1.hotel_id = r2.hotel_id, then our sort criteria does not determine their order
    - if r1.hotel_id < r2.hotel_id, then r1 is after r2
- if r1.price < r2.price, then r1 is after r2
So, our first sort criteria is price and our records are ordered by price, except when their prices
are equal, in which case we break the tie by our second sorting criteria
*/

select *
from hotels
order by price desc, hotel_id desc;