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
===========================
- 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.
- 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
- 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
-
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.
-
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.
2
Answers
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 :
If I understand the question correctly, then a second sort is needed.
This is how your first criteria would be price:
And this is the other way around:
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
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
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
order by price desc, id desc
order by hotel_id desc, price desc
order by price desc, hotel_id desc