Grdb.swift: How can I request elements from two different tables being one of them specifically the most recent in a 1-to-many relation?

[brunomunizaf]

Hello,

I ran into some trouble trying to retrieve elements from two different tables and was wondering if anyone could please help me out figuring the best way to do this.

First some context:
I'm building a chat app that contains two tables: Chat and Message. The Chat table contains and autoincremented primary key column named id and another column named title. The Message table on the other hand contains an autoincremented primary key column named id, a column named text, a column named date and last but not least a column named chatId. As usual, a single chat contains zero or n messages, and every message belongs to exactly one chat.

Q: How can I fetch a virtual element (either through a request or a join maybe?) that contains chat.id, chat.title, message.id, message.text, message.date - being the message in this context the last one for a given chat (last = most recent)?

Here's some visual guidance on what I'm trying to achieve:

Thanks in advance!

Bruno.

[brunomunizaf]

BTW - Previously I tried having a column 'lastMessageId' on my Chat table and using belongsTo on my Chat model create a relation but then I had to update my Chat model everytime a new message was stored and I'm pretty sure GRDB has a better way to do this.

[groue]

Hello @brunomunizaf,

Building a list of all chats along with their latest message is indeed pretty common in chat apps! And it's not possible to express such a request with the current GRDB apis.

The lastMessageId column is a valid way to deal with it, even if it creates synchronization difficulties. Kudos for finding a solution!

Writing a raw SQL request that joins chats and their latest message is also an option. The Joined Queries Support chapter explains how to decode regular records from such a raw query.

The general topic is that we sometimes need to build a "to-one" association from a "to-many" association by sorting the associated records and selecting the first or the last. It was the goal of the #767 pull request, which I could not complete due to technical difficulties. It's hard to generate a SQL query that implements this feature 😅

That's the current landscape. I or someone may eventually find a way to complete #767. It's likely that we have to implement support for common table expressions (CTEs) or correlated subqueries first.

[groue]

@brunomunizaf, there is another solution, which you may find simpler. It involves performing two requests. You'll find a working sample code below, that you can adapt for your application:

import Foundation
import GRDB

let dbQueue = DatabaseQueue()

struct Chat: Codable, FetchableRecord, PersistableRecord {
    static let messages = hasMany(Message.self)
    var id: Int64
}

struct Message: Codable, FetchableRecord, PersistableRecord {
    static let chat = belongsTo(Chat.self)
    var id: Int64
    var chatID: Int64
    var date: Date
    var text: String
}

try! dbQueue.write { db in
    try db.create(table: "chat") { t in
        t.autoIncrementedPrimaryKey("id")
    }

    try db.create(table: "message") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("chatID", .integer).notNull().references("chat", onDelete: .cascade)
        t.column("date", .datetime).notNull()
        t.column("text", .text).notNull()
    }

    try Chat(id: 1).insert(db)
    try Chat(id: 2).insert(db)
    try Chat(id: 3).insert(db)
    try Chat(id: 4).insert(db)

    try Message(id: 1, chatID: 1, date: Date(timeIntervalSince1970: 0), text: "message 0").insert(db)
    try Message(id: 2, chatID: 1, date: Date(timeIntervalSince1970: 2), text: "message 2").insert(db) // latest
    try Message(id: 3, chatID: 1, date: Date(timeIntervalSince1970: 1), text: "message 1").insert(db)

    try Message(id: 4, chatID: 2, date: Date(timeIntervalSince1970: 1), text: "message 1").insert(db)
    try Message(id: 5, chatID: 2, date: Date(timeIntervalSince1970: 0), text: "message 0").insert(db)
    try Message(id: 6, chatID: 2, date: Date(timeIntervalSince1970: 2), text: "message 2").insert(db) // latest

    try Message(id: 7, chatID: 3, date: Date(timeIntervalSince1970: 1), text: "message 1").insert(db) // latest
}

struct ChatInfo {
    var chat: Chat
    var latestMessage: Message?
}

let chatInfos: [ChatInfo] = try! dbQueue.read { db in
    // Fetch chats
    let chats = try Chat.fetchAll(db)

    // Fetch latest messages of fetched chats
    //
    // SELECT *, MAX(date)
    // FROM message
    // WHERE chatId IN (...)
    // GROUP BY chatId
    //
    // We rely on a special processing performed by SQLite on max():
    //
    // https://sqlite.org/lang_select.html:
    // > When the min() or max() aggregate functions are used in an aggregate
    // > query, all bare columns in the result set take values from the input
    // > row which also contains the minimum or maximum.
    let chatIDs = chats.map { $0.id }
    let messages = try Message
        .annotated(with: max(Column("date")))
        .filter(chatIDs.contains(Column("chatId")))
        .group(Column("chatId"))
        .fetchAll(db)

    // Merge chats and their latest messages
    let messageByChatID = Dictionary(uniqueKeysWithValues: messages.map { message in
        (key: message.chatID, value: message)
    })
    return chats.map { chat in
        ChatInfo(chat: chat, latestMessage: messageByChatID[chat.id])
    }
}

// prints
// - chat 1: message 2
// - chat 2: message 2
// - chat 3: message 1
// - chat 4: (null)
for chatInfo in chatInfos {
    print("chat \(chatInfo.chat.id): \(chatInfo.latestMessage?.text ?? "(null)")")
}

[groue]

It looks like we can close this issue :-)