Need help doing a many <-> many match with Fluent in Vapor 3

Question

I've got a Vapor 3 app in which I need to determine is 2 users share any common groups.

So I have 2 models User and Group. A user can belong to many groups. But I have a permission check that if both users share a common group they can send a messages to each other.

final public class User: PostgreSQLModel {
    public var id: Int?
    var firstName: String
    var lastName: String
}

extension User {
    var containers: Siblings<User, Group, UserGroups> {
        return siblings()
    }
}

final public class Group: PostgreSQLModel {
    public var id: Int?
    var name: String
}

What I'd like to do is this

UserGroups.query(to: request).group(.or) { $0.filter(\.userId == user.id!).filter(\.userId == user.id!) }.flatMap { ... }

plus a filter that says where groupId == groupId.

Any thoughts or suggestions?

Answer 1

I don't think you can do it in a single query, but you could try something like:

UserGroups.query(on:request).filter(\.userId == user1.id!).all().flatMap
{
    groups in
    groups.map { $0.id! }.flatMap
    {
        groupIds in
        UserGroups.query(on:request).filter(\.userId == user2.id!).filter(\.groupId ~~ groupIds).all().flatMap
        {
            // do your work here
        }
    }
}