Have `.order` refer to columns in the result set

Hi,

Is it possible to use .order(_ orderings:) and refer to columns in the result set?
It seems that if I do .order([Column("name"]) it will generate "tableName"."name".

The problem is that my .select() modifies the column, so the ordering is not valid when ordered by the original value in the table.

I tried using .order(literal:) but it doesn't allow using .reversed(), which is quite handy in my case.

Thanks in advance.

Kind regards,

Remco Poelstra

Hello @remcopoelstra,

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
    .select(Column("score").forKey("s"))
    .order(SQL("s").sqlExpression)
    .reversed()

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.

1 Like

And if you need to start from DESC and reverse it into ASC:

// SELECT "score" AS "s" FROM "player" ORDER BY s ASC
let request = try Player
    .select(Column("score").forKey("s"))
    .order(SQL("s").sqlExpression.desc)
    .reversed()

I'm considering giving such value an "expression" facet. It currently only has a "selection" facet, so it can't be used as an expression or an ordering term.

You'd then write:

// SELECT "score" AS "s" FROM "player" ORDER BY "s" DESC
let sColumn = Column("score").forKey("s")
let request = try Player
    .select(sColumn)
    .order(sColumn) // would compile
    .reversed()

I just have to look whether SQLite accepts such alias anywhere in a query. I know some databases do not.

Marvellous!
I did run into a small problem: I've a column named 'order', which is not automatically escaped. Easy to fix though.

Yes I did and it got me to the documentation and source code to play around with SQLExpression, but that didn't get me to SQL().sqlExpresion.

I tried deciphering the image at QueryInterfaceOrganization, but couldn't make head or tails from it. But that's probably my limited experience with protocols.

How would such Column then contain an expression?

I'd rather say that this is a failure of the documentation :wink: 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.

Column("score").forKey("s")

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:

Player.order(Column("score").forKey("s")) // won't compile

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)

All right. So in your case, you really need a "free column" that is not tied to any table, as SQL("\"order\"").sqlExpression, then.

SQL(...).sqlExpression is currently documented at Adding support for missing SQL functions or operators, where I'm satisfied since we're in a context where the user expects to write custom SQL.

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).

1 Like

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.

1 Like

That was the place where I did find out about SQLExpression, so you got me close there :slight_smile:

I definitely should have read that part better, but I think 'missing operators' made me skip most of it.

1 Like

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 :+1: (You can still add other ideas if you want, of course :sweat_smile:)

The topic has been turned into an issue: Support for "Detached Columns" · Issue #1127 · groue/GRDB.swift · GitHub

@remcopoelstra,

I've added support for Column("order").detached in the dev/detached-column branch.

The documentation updates:

  • ColumnExpression.detached
  • Column(_:).detached is mentioned in the inline doc for expression.forKey(_:)
  • Added a specific paragraph for expression AS alias at the end of the SQL Operators chapter, with a mention of Column(_:).detached.
  • Replaced the "Adding support for missing SQL functions or operators" chapter with a new general Embedding SQL in Query Interface Requests chapter.
  • Updated the fatal error message you have seen:

    Ordering literals can't be reversed. To resolve this error, order by expression literals instead. For example: order(SQL("(score + bonus)").sqlExpression)

  • Used the "expression literal" wording in the Embedding SQL in Query Interface Requests chapter.

I hope those updates will help users find Column(name).detached. And those who don't will be suggested to use SQL(name).sqlExpression which is less pretty, but strictly equivalent.

Please tell if you think of any further enhancement.

Hi @gwendal.roue ,

This is much clearer I think. I really like the 'Embedding SQL in Query Interface Requests' chapter.
I tried your branch and it works great for me.

Thanks!

1 Like