Combine multiple joins with filters in Vapor

Question

In my backend, I have Posts and a PostController. A post may be visibleToFriendOnly.
In GET /posts, I want to return all posts that the current user is allowed to see.
The User model has a friends property, that is a pivot of User, User with the columns userID and friendID.

I currently get posts like this:

/// /posts
func all(req: Request) throws -> Future<[Post]> {
    let authenticated = try req.requireAuthenticated(User.self)
    return Post.query(on: req)
        .filter(\Post.visibleForUserType == authenticated.type)
        // FIXME: respect .visibleForFriendsOnly
        .all()
}

I'm having a hard time with this because for each Post, I'd have to get the userID and then the User (who is the author) and then query the user's friends and see whether the current user is in that list.
Is there a feasible way of doing this? Maybe a join or something?

Answer 1

It looks like you could build a custom raw query like this

return req.requestPooledConnection(to: .mysql).flatMap { conn in
    defer { try? req.releasePooledConnection(conn, to: .mysql) }
    return conn.raw("""
    SELECT posts.* FROM posts
    LEFT OUTER JOIN user_friends
        ON (user_friends.userID = posts.userID AND user_friends.friendID = 3)
        OR (user_friends.friendID = posts.userID AND user_friends.userID = 3)
    GROUP BY posts.id
    HAVING COUNT(user_friends.id) = 2 OR posts.userID = 3 OR visibleForFriendsOnly = 0
    """).all(decoding: Post.self)
}