Thanks for your restless exploration of uncharted territories of GRDB :-)
You can grab inspiration from this sample request:
// SELECT "score" AS "s" FROM "player" ORDER BY s DESC
let request = Player
May I ask a question? Did you see "Fatal error: Ordering literals can't be reversed. To resolve this error, order by expression literals instead."? I'm sure this error message could be enhanced in order to drive users towards SQL("...").sqlExpression.
I'd rather say that this is a failure of the documentation You know better then me the trail of "information smell" you have followed, and where you would have expected to see the answer to your question. So if you have any hint at what should be enhanced where, while your still have this journey in your memory, this would be helpful.
How would such Column then contain an expression?
It is not a column, it is an "aliasedExpression" of type SQLSelection. Such is (Column("score") + 1).forKey("incrementedScore"), for example. Being an SQLSelection is what prevents the compiler from compiling this value as an expression or an ordering term:
If I would change the type of such a value to the type SQLExpression, it would become usable as an expression anywhere (with the alias, "s" or "incrementedScore", as the generated SQL). But it would still be possible to adapt its generated SQL in the SELECT clause ("score AS s"). To do so, we'd need a switch in this method in order to make a special case when such an expression is asked for its selection facet.
I have to fiddle with the idea and see if this gives the expected results, while not generating invalid SQL.
A probable issue is that once this value is an expression, the compiler will accept:
Column("score").forKey("s") + 1
Maybe I can do something sensible in this case (discard the alias, probably), or maybe I'll have to discard the idea.
Well, I'm not sure doing anything special for such an expression is needed: this will just generate s + 1. And if the user forgets to put the aliased expression in the selection, so that the "s" identifier is defined, SQLite will just throw an error, and the user will have the opportunity to fix the request.
Would it be possible to refer to this alias expression from 'external'?
I've a function that returns the modified select() and a separate function that returns the order() as I want to be able to use the ordering also without the special select. The ordering functions needs to refer to the aliased expression or the normal table column, which both happen (by design) to have the same name.
(The also both need to refer to the same table alias that modifies the column, but it appears that table aliases are merged when they are the same)
But in your case, it is very heavy, and that's why I'm looking for other solutions.
Given that making expression.forKey(...) an expression would help some use cases, but not yours, I was also contemplating introducing Column("order").detached (so that it is never qualified with any table name - but still escaped).
Searching for 'what exactly is an expression' I did find the SQLExpression in the main README, which sounded 'raw enough' to be helpful here. I then searched the source code for this type to see what it could do for me. I found some references to columns, but I couldn't use them. They were 'internal' if I remember correctly. The relation-image also didn't tell me that I could get an SQLExpression from SQL. That's clearly my mistake, as with this knowledge and double checking the image, I now see how I can follow the arrows to get from SQL to 'produces SQLExpression'.
It seems I was confused by the fact the inheritance seems 'upside down' in this image (i.e. Column is above ColumnExpression). My natural feeling would be the other way around, but that might not make the image clearer to understand though, as that doesn't fit nice with the 'produces' arrows.
All right! Well, this was a very informative conversation. I'm sure we'll enhance both the lib and the doc shortly. Thank you very much for your time (You can still add other ideas if you want, of course )