How to fetch associated model within CTE?

[rezuanb (Rezuan)]

Hello everyone!

I have a Test Chat App where I am trying to fetch chat list using this solution

let latestMessageRequest = Message
    .annotated(with: max(Column("date")))
    .group(Column("chatID"))

But I also need to fetch latest message's owner, so I just add this:
.including(required: Message.user.forKey("owner"))

struct MessageInfo: Decodable, FetchableRecord {
  var message: Message
  var owner: User
}

struct ChatInfo: Decodable, FetchableRecord {
 var chat: Chat
 var latestMessage: Message?
}

let latestMessageRequest = Message
    .including(required: Message.user.forKey("owner"))   
   .annotated(with: max(Column("date")))
    .group(Column("chatID"))

Then I get this error:
GRDB/FetchableRecord+Decodable.swift:7: Fatal error: 'try!' expression unexpectedly raised an error: Swift.DecodingError.keyNotFound(CodingKeys(stringValue: "owner", intValue: nil), Swift.DecodingError.Context(codingPath: , debugDescription: "No such key: message or owner", underlyingError: nil))

I suppose I don't understand how CTE works, do I? Could anyone help me?

UPDATED:

I found this solution

 let messageOwnerAssociation = latestMessageCTE.association(to: User.self) { message, user in
      user[Column("id")] == message[Column("userId")]
    }
    
  return Chat
       .with(latestMessageCTE)
       .including(required: latestMessageAssociation.forKey("latestMessage")
         .including(required: messageOwnerAssociation.forKey("owner")))
      .asRequest(of: ChatInfo.self)

Is it the best way?

[gwendal.roue (Gwendal Roué)]

Hello @rezuanb,

It looks like you just forgot to tell which type the request should decode :-)

// QueryInterfaceRequest<MessageInfo>
let latestMessageRequest = Message
    .including(required: Message.user.forKey("owner"))
    .annotated(with: max(Column("date")))
    .group(Column("chatID"))
    .asRequest(of: MessageInfo.self) // <- there

Full sample code

import GRDB

struct User: Codable, FetchableRecord, PersistableRecord {
    var id: UUID
    var name: String
}

struct Message: Codable, FetchableRecord, PersistableRecord {
    var id: UUID
    var chatID: UUID
    var userID: UUID
    var text: String
    var date: Date
    
    static let user = belongsTo(User.self)
}

struct Chat: Codable, FetchableRecord, PersistableRecord {
    var id: UUID
}

let dbQueue = DatabaseQueue()
try dbQueue.write { db in
    try db.create(table: "chat") { t in
        t.column("id", .blob).notNull().primaryKey()
    }
    
    try db.create(table: "user") { t in
        t.column("id", .blob).notNull().primaryKey()
        t.column("name", .text).notNull()
    }
    
    try db.create(table: "message") { t in
        t.column("id", .blob).notNull().primaryKey()
        t.column("chatID", .blob).notNull().references("chat")
        t.column("userID", .blob).notNull().references("user")
        t.column("date", .datetime).notNull()
        t.column("text", .text).notNull()
    }
    
    // -
    
    let chat1 = Chat(id: UUID())
    try chat1.insert(db)
    
    let chat2 = Chat(id: UUID())
    try chat2.insert(db)
    
    let user1 = User(id: UUID(), name: "Arthur")
    try user1.insert(db)
    
    let user2 = User(id: UUID(), name: "Barbara")
    try user2.insert(db)
    
    try Message(id: UUID(), chatID: chat1.id, userID: user1.id, text: "1", date: Date()).insert(db)
    try Message(id: UUID(), chatID: chat1.id, userID: user2.id, text: "2", date: Date().addingTimeInterval(1)).insert(db)
    try Message(id: UUID(), chatID: chat1.id, userID: user1.id, text: "3", date: Date().addingTimeInterval(2)).insert(db)
    
    try Message(id: UUID(), chatID: chat2.id, userID: user1.id, text: "4", date: Date()).insert(db)
    try Message(id: UUID(), chatID: chat2.id, userID: user2.id, text: "5", date: Date().addingTimeInterval(1)).insert(db)
    
    // -
    
    struct MessageInfo: Decodable, FetchableRecord {
        var message: Message
        var owner: User
    }
    
    let latestMessageRequest = Message
        .including(required: Message.user.forKey("owner"))
        .annotated(with: max(Column("date")))
        .group(Column("chatID"))
        .asRequest(of: MessageInfo.self)
    
    for messageInfo in try latestMessageRequest.fetchAll(db) {
        print(messageInfo)
    }
}

[rezuanb (Rezuan)]

It doesn't work I got the same error

Only this solution works:

 let messageOwnerAssociation = latestMessageCTE.association(to: User.self) { message, user in
      user[Column("id")] == message[Column("userId")]
    }
    
  return Chat
       .with(latestMessageCTE)
       .including(required: latestMessageAssociation.forKey("latestMessage")
         .including(required: messageOwnerAssociation.forKey("owner")))
      .asRequest(of: ChatInfo.self)

[gwendal.roue (Gwendal Roué)]

Only this solution works:

OK so it looks like you found your solution

[rezuanb (Rezuan)]

Yeah, it seems like magic because I couldn't found a solution for 2 days, but after created a new topic I found it within 20 min.
But anyway is my solution correct?

[gwendal.roue (Gwendal Roué)]

@rezuanb

But anyway is my solution correct?

It is hard to tell with the partial information you give. Did you notice you never gave any runnable sample code? Well, I do not have magical guessing powers, and my brain is not a Swift compiler and SQLite runtime

If your request gives the data you expect, I guess it is correct. Maybe check the results of your request with a few interesting test databases? Tests are a great way to build confidence.

[rezuanb (Rezuan)]

Oh, sorry.
In my original question I mentioned:

I have a Test Chat App where I am trying to fetch chat list using this solution

I pasted the link to sample code from GRDB documentation.

This means that my example code is exactly the same as the code from the GRDB documentation, but with just only one exception: I also get the owner of the last message.

Next, I simply extend the example code from docs with:

struct MessageInfo: Decodable, FetchableRecord {
  var message: Message
  var owner: User // <- there
}

struct ChatInfo: Decodable, FetchableRecord {
 var chat: Chat
 var latestMessage: MessageInfo? // <- there
} 

let latestMessageRequest = Message
    .including(required: Message.user.forKey("owner")) // <- there  
   .annotated(with: max(Column("date")))
    .group(Column("chatID"))

I didn't provide just that part of my sample below. But there's obviously nothing unusual here.

 struct User: Codable, FetchableRecord, PersistableRecord {
     var id: Int64
 }
 
 extension   Message {
  static let user = belongsTo(User.self)
 }

Then when I waited for any answers I could found my own solution. (Then I updated my original question)

//`latestMessageCTE` is from sample from docs 
 let messageOwnerAssociation = latestMessageCTE.association(to: User.self) { message, user in
      user[Column("id")] == message[Column("userId")]
    }

let request = Chat
    .with(latestMessageCTE)
    .including(optional: latestMessage
         .including(required: messageOwnerAssociation.forKey("owner"))) // <- there
    .asRequest(of: ChatInfo.self)

I thought I didn't have to show the full code of the sample, because it almost just repeats the sample from the documentation.

So I promise that in the future I will show the full code anyway.

(But anyway I think you have some magical powers )