How to limit the number of fetched records in the context of an HasMany association?

[adilrc]

Hello, I was reading through the association documentation and I was wondering if there was way to fetch only a subset in a HasMany relationship.

Let's take the documentation's example:

extension Author: TableRecord {
    static let books = hasMany(Book.self)
}

struct AuthorInfo: Decodable, FetchableRecord {
    struct PartialBook: Decodable {
        var title: String
        var year: Int
    }
    var author: Author       // The base record
    var books: [PartialBook] // A collection of partial associated records
}

let authorInfos = try Author
    .including(all: Author.books.select(Column("title"), Column("year")))
    .asRequest(of: AuthorInfo.self)
    .fetchAll(db)

Is there a way to

1. Limit the number of fetched books? I was initially thinking of using limit(...) on hasMany but this is not supported.
2. Fetch only one element, for example the first book of the author:

struct AuthorInfo: Decodable, FetchableRecord {
    struct PartialBook: Decodable {
        var title: String
        var year: Int
    }
    var author: Author       // The base record
    var firstBook: PartialBook?
}

Thank you.

[groue]

Hello @adilrc,

Is there a way to limit the number of fetched books?

Yes, but not trivially, because this is not a built-in GRDB feature.

You'll need to do by hand what including(all:) does for you.

The easiest technique is to perform as many book requests as there are authors:

extension Author {
    static let books = hasMany(Book.self)
}

struct AuthorInfo: Decodable, FetchableRecord {
    struct PartialBook: Decodable, FetchableRecord {
        var title: String
        var year: Int
    }
    var author: Author       // The base record
    var books: [PartialBook] // A collection of partial associated records
}

// Fetch authors (filtered, ordered, whatever)
let authors = try Author.fetchAll(db)

// Fetch the three most recent partial books for each author
let authorInfos = try authors.map { author in
    let books = try author
        .request(for: Author.books)
        .select(Column("title"), Column("year"))
        .order(Column("year").desc, Column("title"))
        .limit(3)
        .asRequest(of: AuthorInfo.PartialBook.self)
        .fetchAll(db)
    return AuthorInfo(
        author: author,
        books: books)
}

A more involved technique performs only two SQL requests, but it is much more complex:

1. Fetch authors
2. Fetch the last three partial books for those authors, using an SQLite window function (we'll need raw SQL here).
3. Hydrate the array of AuthorInfo from both results.

Sample code

struct AuthorInfo: Decodable, FetchableRecord {
    struct PartialBook: Decodable, FetchableRecord {
        var authorId: Int64 // Needed for grouping by author
        var title: String
        var year: Int
    }
    var author: Author
    var books: [PartialBook]
}

// Fetch authors (filtered, ordered, whatever)
let authors = try Author.fetchAll(db)

// The request for partial books
let authorIds = authors.map(\.id)
let partialBooksRequest = Book.all()
    .filter(authorIds.contains(Column("authorId")))
    .select(
        Column("authorId"), // Needed for grouping by author
        Column("title"),
        Column("year"))

// The common table expression for the three most recent partial books.
let partialBooksCTE = CommonTableExpression(
    named: "rankedBooks",
    request: partialBooksRequest.annotated(with: SQL("""
        ROW_NUMBER() OVER (PARTITION BY authorId ORDER BY year DESC, title)
        """).forKey("rn")))

// Fetch partial books
let partialBooks = try partialBooksCTE.all().with(partialBooksCTE)
    .filter(Column("rn") <= 3)
    .order(Column("year").desc, Column("title"))
    .asRequest(of: AuthorInfo.PartialBook.self)
    .fetchAll(db)

// Build the result from fetched authors and partial books
let partialBooksByAuthorId = Dictionary(grouping: partialBooks, by: \.authorId)
let authorInfos = authors.map { author in
    AuthorInfo(
        author: author,
        books: partialBooksByAuthorId[author.id, default: []])
}

Fetch only one element, for example the first book of the author

I suppose this can be derived from the previous sample codes.

[adilrc]

That clarifies things a lot. Thank you 🙏

[groue]

You're welcome! I've discovered this ROW_NUMBER() technique today, by asking ChatGPT. Maybe we'll be able to implement the limit method on associations, after all 😉