Repository navigation
Order By Function Akin To MySQL #3310
Description
Suggestion
When querying data, most filters are customizable and flexible enough to get the desired results except for the order filter which is very limited to 'propertyName <ASC|DESC>'. Powerful functions in MySQL or statements cannot be used.
Use Cases
- Using if statements in ordering like
ORDER BY IF (`arg1` IS NULL, `arg2`, `arg1`) - Using functions like
ORDER BY ROUND(`arg`)orORDER BY GREATEST(`arg1`, `arg2`) - Getting on-the-fly values or applying arithmetic operations to the arguments like
ORDER BY `quantityOrdered` * `priceEach`orORDER BY `arg`*2
Not being able to do any of this means having to loop through the results after fetching then doing some processing on them one by one.
Examples
One example is ordering results by more than one property. By default, when the first property is null, it comes up at the top when ordering ascendingly since NULL values are considered lower than any non-NULL values. Here are a few takes on Stackoverflow on the topic. This lingering issue also came up a few years ago in a Gitter discussion inside loopback community.
Here is an example where some products have prices for both local and abroad users while others have a price for only one of them:
MySQL: SELECT ... ORDER BY `feesLocal` ASC, `feesAbroad` ASC gives:
+----+------------+-------------+
| id | feesLocal | feesAbroad |
+----+------------+-------------+
| 1 | NULL | 30 |
| 2 | 10 | 20 |
| 3 | 65 | NULL |
+----+------------+-------------+
Which is obviously different from the desired result. But the following:
MySQL: SELECT ... ORDER BY IF (`feesLocal` IS NULL, `feesAbroad`, `feesLocal`)
gives:
+----+------------+-------------+
| id | feesLocal | feesAbroad |
+----+------------+-------------+
| 1 | 10 | 20 |
| 2 | NULL | 30 |
| 3 | 65 | NULL |
+----+------------+-------------+
Acceptance criteria
TBD - will be filled by the team.
Activity
allow objects as input for order filter
allow using functions in order filter
shadyanwar commentedon Jul 6, 2019
Here is my proposed implementation:
The resulting query:
Fork: loopback-datasource-juggler:
loopbackio/loopback-datasource-juggler@master...shadyanwar:patch-1
Fork: loopback-connector:
loopbackio/loopback-connector@master...shadyanwar:patch-1
@raymondfeng
shadyanwar commentedon Jul 12, 2019
@raymondfeng @bajtos thoughts?
bajtos commentedon Jul 30, 2019
Thank you @shadyanwar for starting this discussion. I see how more advanced sorting criteria can be useful!
At the moment, LB4
Filter.orderis defined as follows:https://github.com/strongloop/loopback-next/blob/cb4178ad5b5011a3a22107ae243e1372fe465676/packages/repository/src/query.ts#L189-L197
In other words, it is an array of property names with an optional ASC/DESC suffix.
While your proposed format
order: [{ func: '....' }]would work, I find it rather complex. How about allowingorderto be set to the string to be used in the SQL query?Syntax aside, there is a big catch in our proposals. We cannot allow arbitrary
orderstrings because that would open a door to SQL injection attacks.Consider the following request:
Unless we implement strict validation of the
ordervalue, this could lead to the following query being executed:(I am not fluent in SQL these days, sorry if the query is not valid. I hope it does show the intent & the problem though.)
shadyanwar commentedon Jul 7, 2020
@bajtos Thanks for your thoughtful response. Regarding SQL injection, is there a way to prevent the filter from being used externally through the REST API and restrict it to only be used internally using the internally defined methods like find and findById only?