Cannot use order with composite columns

[efraimrodrigues]

Currently the order property only works for single column.

Let's say we would want to order by the difference of two numeric columns. Consider a Product table with price and discount columns. Seems like we cannot order by price - discount directly. I know we could have a modifier adding a new netPrice column with the difference, but sometimes we want to do this type of order without having to change the original model.

From looking at the code I see that applyOrder is splitting the string by , and only considering the first item. In the example above price - discount only takes price for parsing and ignores the rest.

@jeff3yan please let me know if it makes sense and I can work on a PR for this. I guess we would have to extract the columns and would parse each one individually and then combine them.

[jeff3yan]

Ordering by column expressions is a bit trickier than it initially seems but it should be doable.

The main reason is that the order expression encompasses order statements for all eager expressions.

If you take a filter object, it might look something like this (querying users, then including their manager):

{
    "eager": {
        "manager": {
            "$where": {
                "firstName": "John"
            }
        }
    },
    "fields": [
        "firstName",
        "lastName",
        "X",
        "Y",
        "manager.firstName",
        "manager.lastName"
    ],
    "order": "X - Y DESC, manager.X - manager.Y DESC"
}

users/managers are the same table, and they have an X and Y column we can use for subtraction.

However each is retrieved with separate SQL queries - which ordering can be applied on separately.

The main problems I can see are:

1. Parsing out column names from each part of the expression (eg something like A - (B + C))
2. Creating fully qualified names for each root table field (see here).
3. Ensuring that fields from different queries aren't mixed (this could be just ignored and would essentially be user error).