Order By Function Akin To MySQL

[shadyanwar]

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`) or ORDER BY GREATEST(`arg1`, `arg2`)
• Getting on-the-fly values or applying arithmetic operations to the arguments like ORDER BY `quantityOrdered` * `priceEach` or ORDER 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.

[shadyanwar]

Here is my proposed implementation:

await this.exampleDataRepo.find({
    order: [{ func: 'IF ($:feesLocal:$ IS NULL, $:feesAbroad:$, $:feesLocal:$)'}]
});

The resulting query:

SELECT * FROM `example` ORDER BY IF (`feesLocal` IS NULL, `feesAbroad`, `feesLocal`)

Fork: loopback-datasource-juggler:
loopbackio/loopback-datasource-juggler@master...shadyanwar:patch-1

Fork: loopback-connector:
loopbackio/loopback-connector@master...shadyanwar:patch-1

@raymondfeng

[shadyanwar]

@raymondfeng @bajtos thoughts?

[bajtos]

Thank you @shadyanwar for starting this discussion. I see how more advanced sorting criteria can be useful!

At the moment, LB4 Filter.order is 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 allowing order to be set to the string to be used in the SQL query?

await this.exampleDataRepo.find({
    order: 'IF ($:feesLocal:$ IS NULL, $:feesAbroad:$, $:feesLocal:$)'
});

Syntax aside, there is a big catch in our proposals. We cannot allow arbitrary order strings because that would open a door to SQL injection attacks.

Consider the following request:

GET http://localhost:3000/examples?filter={"order":"1 LIMIT 0; SELECT * FROM users"}

Unless we implement strict validation of the order value, this could lead to the following query being executed:

SELECT * FROM Examples ORDER BY 1 LIMIT 0; SELECT * FROM users

(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]

@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?