Ambiguous column name when joining tables with FluentSQLite

Question

I am trying to write a route that returns all the posts written by users who are followed by a particular user. This is the data model that I'm using:

struct Follow: Content, SQLiteModel, Migration {
    var id: Int?
    var follower: String
    var following: String
}

struct Post: Content, SQLiteModel, Migration {
    var id: Int?
    var username: String
    var message: String
}

struct User: Content, SQLiteStringModel, Migration {
    var id: String?
    var password: String
}

And this is the route:

router.get(String.parameter, "timeline") { req -> Future<[Post]> in
    let username = try req.parameters.next(String.self)

    return Follow.query(on: req).filter(\Follow.follower == username).join(\Follow.following, to: \Post.username).alsoDecode(Post.self).all().map { tuples in
        return tuples.map { tuple in
            return tuple.1
        }
    }
}

The code compiles, but at runtime I get this JSON error dictionary:

{"error":true,"reason":"ambiguous column name: main.Follow.id"}

And I also have an idea that the problem is that since I am making a join, then there is a duplicate id field (Follow.id, Post.id), but how to solve this problem? in sql I would just specify something like 'as followId' in order to rename the field, but how to do this in FluentSQLite?

Update

This is how I modified the "timeline" route in the end:

return User.find(username, on: req).flatMap { user in
    guard let user = user else {
        throw Abort(.notFound)
    }

    return try user.followers.query(on: req)
        .join(\Post.username, to:\FollowUp.following)
        .alsoDecode(Post.self).all()
        .flatMap { tuples in
        return tuples.map { tuple in
            return tuple.1
        }
    }
}

I get this error: "Cannot convert value of type '[Post]' to closure result type 'EventLoopFuture<[Post]>'"

Answer 1

Follow is essentially a Pivot table and formally implementing it as one should get rid of your problem. However, you can run into difficulty, See:

Siblings relationship between same models in Vapor

So, in your case, make your follower and following fields into User.ID type and add the following to your Follow model:

struct Follow: Content, Pivot, Migration
{
    static var idKey: WritableKeyPath<FollowUp, Int?> = .id
    typealias Database = SQLiteDatabase
    typealias ID = Int
    var id:Int?
    var follower:User.ID
    var following:User.ID

    typealias Left = User
    typealias Right = User

    static var leftIDKey: WritableKeyPath<Follow, Int> {
        return \.follower
    }

    static var rightIDKey: WritableKeyPath<Follow, Int> {
        return \.following
    }
}

And then create this extension:

extension User:
{
    var followers: Siblings<User, User, Follow> {
    return siblings(Follow.leftIDKey, Follow.rightIDKey)
    }
}

So, finally, your query becomes:

return User.find(username, on: req).flatMap { user in
    guard let user = user else {
        throw Abort(.notFound)
    }

    return try user.followers.query(on: req)
        .join(\Post.username, to:\FollowUp.following)
        .alsoDecode(Post.self).all()
        .map { tuples in
        return tuples.map { tuple in
            return tuple.1
        }
    }
}

You'll need to tweak Post to hold the User.ID rather than username to make the join work.