Multiple Annotating

Related Projects GRDB
1

I have two tables "Conversation" and "Message"
Here is the structure of the "conversation" table
| ----- id ---- | ----- createdAt -----|

And you can see the structure of the "message" table below.
| ----- id ---- | ---- conversationId ---- | ---- body ---- | ---- isSeen ----- |

This is my way to fetch each conversation with number of all messages and number of messages where their isSeen field is equal to false.

struct ConversationInfo: Decodable, FetchableRecord {
  var id: Int
  var createdAt: Date
  var messageCount: Int
  var unreadCount: Int

  static func request() -> QueryInterfaceRequest<ConversationInfo> {
    Conversation
      .annotated(with:
                  Conversation.messages.count.forKey("messageCount"),
                 Conversation.messages.filter(Message.Columns.isSeen == false).count.forKey("unreadCount")
      )
      .asRequest(of: ConversationInfo.self)
  }
}

But when I run this request I always only get the result of unreadCount for both fields. For example, If I have 2 messages with field isSeen = false, then messageCount will be equal to this value. However if I remove Conversation.messages.filter(Message.Columns.isSeen == false).count.forKey("unreadCount") line from the request, then messageCount will be received correctly.

struct Conversation: Encodable, PersistableRecord {
  var id: Int
  var createdAt: Date

  static let messages = hasMany(Message.self)
}

struct Message: Encodable, PersistableRecord {
  var id: Int
  var conversationId: Int
  var body: String
  var isSeen: Bool

  static let conversation = belongsTo(Conversation.self)
}

Thanks for any help

2

-- (This is a copy of a message sent in private, during the time the OP was waiting for human review before it would become visible to everyone) --

Hello @rezuanb,

You get identical results for both counts because you are counting a single association twice, instead of counting two distinct associations.

This is surprising to you, I know. I provide the relevant documentation links at the bottom of this message.

The fix is to instruct GRDB that you want to count two distinct associations. To do so, give them distinct associations keys:

// Association key "message", probably (it is the name
// of the associated table unless stated otherwise).
let messages = Conversation.messages

// Distinct association key "unread"
let unreadMessages = Conversation.messages
    .filter(Message.Columns.isSeen == false)
    .forKey("unread")

// Count distinct associations
return Conversation
    .annotated(with:
        messages.count.forKey("messageCount"),
        unreadMessages.count.forKey("unreadCount"))
    .asRequest(of: ConversationInfo.self)

This technique is important. The next time you use several variants of one association in a single request, you will probably need it again. For more details about association keys, see:

1 Like