Multi-field sorting: can orders differ per key?

[nikicc]

I was thinking whether it would be possible to define different orders for different keys?

For example, let's say my instances have these fields:

type Instance struct {
    Level  int
    Length int
}

I would like to achieve something like this:

SELECT *
FROM instance
ORDER BY level ASC, length DESC

From my brief investigation, it seems like something like this is currently not possible, as we only store one value for order. Is this correct? If yes, would it be worth to think about whether this could be extended so it would be possible to pass one order value for each of the keys? Perhaps as an idea for v2?

As an idea, we could change SetOrder to SetOrders, and make it behave similarly to SetKeys — which just appends stuff. In this scenario the interface could look something like this (just to illustrate, didn't give this much though):

paginate.SetKeys("Level", "Length")
paginate.SetOrders(paginator.ASC, paginator.DESC)

I suspect how this would complicate validation, but a simple rule could be like this: if there is more than one key set, then the order should either:

• have only one value, in this case use this value for all fields; or,
• have as many values as there are keys; in this case match them by position; for all other cases raise some sort of an error.

[pilagod]

Hello @nikicc

Yes, you are right. Currently there is only a global order can be set for each paginating query.

My current plan for v2 is to offer a more configurable interface, something like (just a draft):

type Rule struct {
    Key string
    Order Order
    SQLRepr string
} 

paginator.SetRules(
    Rule{
        Key: "Level", 
        Order: Order.ASC,
        SQLRepr: "instance.level"
    },
    Rule{
        Key: "Length",
        Order: Order.DESC,
        SQLRepr: "instance.length"
    }
)

With such interface, we can configure each paginated key flexibly, and it also provides extensibility for new options by extending fields on Rule.

Does this solution cover your needs? Any thought or advice is gratefully welcomed 😃

---

Although it is possible to customize each key's order, we should also take frontend API interface into account. Frontend may now use order query string to interact with the paginator:

[GET] /instances?order=asc

If keys can have their own order, is there a good way to present this on API endpoint?

For now, I only come up a straight thought which is to make order query become relative meaning but not absolute direction.

For instance, given default order of level is ASC, and length is DESC:

• Under /instances?order=asc condition, level will use ASC, and length will use DESC.
• Under /instances?order=desc condition, level will use DESC, and length will use ASC.

But this is not straight from API perspective, containing unclear logic behind the scenes.

I would be glad to know that if you have any thought about this problem, thanks! 😃

[nikicc]

@pilagod thanks for this!

Does this solution cover your needs?

Yes, that would cover all my needs. Looking forward to v2 😎

If keys can have their own order, is there a good way to present this on API endpoint?

Yes, that then becomes a problem. And I'm not aware of any standard solutions to this. But what I did in some of the APIs in the past was to allow for more complex format inside the order field. For example, it can be a comma-separated string of values in format <field>:<direction>, as in:

[GET] /instances?order=level:asc,length:desc

With this approach you can then encode arbitrarily complex sorting parameters. Though one caveat to this is that validation becomes a bit more complex.

[pilagod]

@nikicc thanks so much!

I truly appreciate your solution, it is quite helpful! I think it is currently the best way to solve this problem. I will upgrade query parsing mechanism in this direction.

Thank you so much again, let's look forward to the new version coming 💪

[pilagod]

Hello @nikicc,

I'm excited to inform you that v2 is now merged into master and released 🎉 It takes almost one month to finish this upgrade 😂

Feel free to let me know any your advice or feedback.

Cheers!

[nikicc]

@pilagod wuuhuuu 🚀

Will check it shortly 😎 Thanks for all the hard work!