How to sort results by count aggregate

[gshaw]

Hi, hopefully somebody can help me out with this. I tried reading the docs and a number of different things but I'm a bit stuck.

What is the correct way to order a request a calculated column? E.g., I want to sort authors by descending book count.

struct AuthorInfo: Decodable, FetchableRecord {
    var author: Author
    var bookCount: Int
}

let ordering = Column("bookCount").desc // <- won't work, is escaped as author.bookCount
let request = Author.annotated(with: Author.books.count).order(ordering)
let authorInfos: [AuthorInfo] = try AuthorInfo.fetchAll(db, request)
// Crash: no such column: author.bookCount

[groue]

Hello @gshaw,

Yes, Column("bookCount") gets qualified with the author table whenever it enters a request of authors that involves other tables. This resolves ambiguities whenever two tables have a column with the same name. In case of bookCount, there is no such ambiguity, but columns like id or creationDate could well be, so columns are generally qualified.

To get a naked bookCount that is never qualified, use Column("bookCount").detached:

struct AuthorInfo: Decodable, FetchableRecord {
    var author: Author
    var bookCount: Int
}

let request = Author
    .annotated(with: Author.books.count)
    .order(Column("bookCount").detached.desc)
let authorInfos: [AuthorInfo] = try AuthorInfo.fetchAll(db, request)

[gshaw]

Thanks so much for quick reply (and the amazing library). detached was what I was looking for and works perfectly in my case.

[groue]

Happy GRDB!