Complex query using Fluent in Vapor 4

Question

I want to query chats, that contain certain user in siblings field.

Simplified models:

final class Chat: Model, Content {

  @Siblings(through: ChatUser.self, from: \.$chat, to: \.$user)
  var users: [User]

}

final class User: Model, Content {

  @Siblings(through: ChatUser.self, from: \.$user, to: \.$chat)
  var chats: [Chat]
}

My fetch route:

func fetch(req: Request) throws -> EventLoopFuture<[Chat]> {

    let user = try req.auth.require(User.self)

    return Chat
      .query(on: req.db)
      .filter("users", .contains(inverse: false, .anywhere), user)
      .all()
}

I get [ ERROR ] server: column chats.users does not exist (errorMissingColumn)

How can I do such filters in a right way?

Answer 1

If I have understood your requirement correctly, you should be starting with the sibling relationship from the User end, not Chat. Your query then simplifies to:

func fetch(req: Request) throws -> EventLoopFuture<[Chat]> {

    let user = try req.auth.require(User.self)

    return User
      .query(on: req.db)
      .filter(\.$id == user.id!)
      .with(\.$chats)
      .first()
      .unwrap(or:Abort(.internalServerError))
      .map
      {
           chattyUser in
           return chattyUser.chats
      }
}