Using a Joined Table column as the first column in the .order() function

[martindufort]

Hey Groue, ca va?

It's been a while and I thinking I'm losing my GRDB IQ.

So here goes:

Using Joined Table Column in order clause

I have a table A with a hasMany relationship to table B.
I want to perform a query where the column name in the joined table B is the first item in the order statement.

I've tried this:

try TableA
   .joining(required: TableA.tableBAssociation
		.filter(TableB.Columns.count > 0)
		.order(TableB.Columns.name.desc)
		)
   .group(TableA.Columns.uuid)
   .order(TableA.Columns.subject)
   .fetchAll(db)

This puts the TableB.name column as the last item in the order by clause.

Then I read the documentation and tried asking you know who.
Finally I was able to resolve this by declaring the TableB column explicitly

Here's the sample code

let firstItem = SQL(“tableB.name”).sqlExpression
try TableA
   .joining(required: TableA.tableBAssociation
		.filter(TableB.Columns.count > 0)
		)
   .group(TableA.Columns.uuid)
   .order(firstItem.desc, TableA.Columns.subject)
   .fetchAll(db)

Question?

Is this the best way? I would rather not specify the column name of TableB using this string construction.
Let me know what I have missed.

Thanks

[groue]

Hello @martindufort,

Sorry for the late response, I clearly missed a notification.

Your code works, so that's good 👍

The "official" way to implement this is a TableAlias:

let bAlias = TableAlias()
try TableA
   .joining(required: TableA.tableBAssociation
                            .aliased(bAlias)
		            .filter(TableB.Columns.count > 0))
   .group(TableA.Columns.uuid)
   .order(bAlias[TableB.Columns.name].desc, TableA.Columns.subject)
   .fetchAll(db)