Can you change the order of "ORDER BY" columns created from "nested" associations in a request?

[SwiftedMind]

Hey 🙂

I am trying to build a request with multiple JOINs and I need to order the results by columns coming from multiple of these JOINed tables (and in a specific order). I have an example:

---

The database structure looks like this:

Item has one Presentation
Presentation belongs to one Item via an itemId column.
Presentation belongs to one Tag, via a relevantTagId column.
Tag has an importance column
Presentation has a nextPresentationDate column

try db.create(table: "item") { table in
  table.autoIncrementedPrimaryKey("id")
  // ...
}

try db.create(table: "tag") { table in
  table.autoIncrementedPrimaryKey("id")
  table.column("importance", .integer).notNull().defaults(to: 1)
  // ...
}

try db.create(table: "presentation") { table in
  table.autoIncrementedPrimaryKey("id")
  table.belongsTo("item", onDelete: .cascade).notNull()
  table.belongsTo("relevantTag", inTable: "tag")
  table.column("nextPresentationDate", .date)
  // ...
}

---

Now, I want to fetch all items and sort them based on the importance of the relevantTag of their respective presentation. But since importance is a value that has a small range (like between 0 and 5), there will be items with the same importance value, so I want to fall back on the nextPresentationDate of their respective presentation.

let observation = ValueObservation.tracking { db in
  try Item
    .including(
      required: Item.presentation // Join the item's Presentation
        .including(required: Presentation.relevantTag.order(Tag.Columns.importance.desc)) // Join the relevant tag and order by highest importance
        .order(Presentation.Columns.nextPresentationDate.desc) // If importance is equal, fallback to descending presentation date to guarantee a deterministic order
    )
    .asRequest(of: ItemInfo.self) // Format as info object
    .fetchAll(db)
}

There are two .order calls in that request and the generated SQL has the following ORDER BY clause:

ORDER BY "presentation"."nextPresentationDate" DESC, "tag"."importance" DESC

What I need is to change the order of those ORDER BY columns. I want tag.importance to take precedence over presentation.nextPresentationDate, but I can't figure out how to do it or if I need to use a raw SQL query for this request.

To change the order, I would need to be able to reference all the needed columns within the same .oder() call but that's not possible because I don't know how to reference a "nested" column on a JOINed table. Is that possible without using raw SQL?

One thing I played around with is using .select(Presentation.Columns.nextPresentationDate.forKey("MyKey)) to access that column separately but then the request fails because only one column is selected even though I need all of them.

I might be missing something obvious, but I've been searching the documentation and couldn't find anything that would solve this. But maybe I'm lacking the proper search terms/keywords.

I'd appreciate any help 🙂

[groue]

Hello @SwiftedMind,

To change the order, I would need to be able to reference all the needed columns within the same .order() call but that's not possible because I don't know how to reference a "nested" column on a JOINed table.

Your intuition is spot on. This is exactly how it should be done 👍

In the (too long) documentation for associations, the relevant section is Table Aliases.

Practically speaking, you can adapt your code as below. I moved the ordering up to the outer request of items, so I needed two aliases, one for presentations and one for tags:

let presentationAlias = TableAlias()
let tagAlias = TableAlias()
let presententation = Item.presentation.aliased(presentationAlias)
let tag = Presentation.relevantTag.aliased(tagAlias)

return try Item
    .including(required: presententation // Join the item's Presentation
        .including(required: tag)        // Join the relevant tag
    )
    // Order by tag importance and then presentation date
    // to guarantee a deterministic order
    .order(
        tagAlias[Tag.Columns.importance].desc,
        presentationAlias[Presentation.Columns.nextPresentationDate].desc
    )
    // Format as info object
    .asRequest(of: ItemInfo.self)
    .fetchAll(db)

[SwiftedMind]

💡 Oh wow, that's awesome, and exactly what I was looking for!
Is there even anything GRDB can't do? 😅 This is incredible.

I should probably take the time and skim through the entirety of the documentation at some point, to get a rough overview of all the terminology and things I can look for when trying to do something like this 😅

Thanks a lot! 🙂

[groue]

Thanks @SwiftedMind, for your compliments and even more for your high-quality original message :-)