The SQL subquery syntax unsupported

📚 Official Books Server Side Swift with Vapor
1

I didn’t find any solution to run subquery using pure Fluent syntax , i was forced to use pur sql

func getFavorite(_ req: Request) throws -> Future<[Article]> {
    return try req.parameters.next(User.self).flatMap(to: [Article].self) { user in
        guard let id  = user.id else {
            throw Abort(HTTPStatus.notFound)
        }
        return req.withPooledConnection(to: .psql) { conn in
            return conn
                .raw("Select * From \"Article\" as Ar where Ar.id in (select UA.articleid From  \"Article_User\" as UA where UA.userid = \(id) and UA.favorite = \(true) group by UA.userid, UA.articleid)").all(decoding: Article.self)
        }
    }
}

Now i’m struggling, how i can use Pagination framework ? with such custom query .

2

@0xtim Can you please help with this when you get a chance? Thank you - much appreciated! :]

3

@swiftios which Pagination framework? The Nodes one? If so, I’m not sure if they would support raw SQL (though if it’s a QueryBuilder thing, you should be able to do something like

return conn.raw("Select * From \"Article\" as Ar where Ar.id in (select UA.articleid From  \"Article_User\" as UA where UA.userid = \(id) and UA.favorite = \(true) group by UA.userid, UA.articleid)").paginate(for: req, decoding: Article.self)

I suspect that would require a change in the framework to support a decoding type. You could raise an issue on their GitHub for it

4

i’m using this framework GitHub - vapor-community/pagination: Simple Vapor 3 Pagination

5

Looks like an issue has already been raised similar to this Support for `join`/`alsoDecode` · Issue #12 · vapor-community/pagination · GitHub

I’d ask on the Vapor Discord to see if any maintainers have an idea

6

also i have another question

“Select * From "Article" as Ar where Ar.id in (select UA.articleid From "Article_User" as UA where UA.userid = (id) and UA.favorite = (true) group by UA.userid, UA.articleid)”

like if have changed the in to not in it’s will impossible to use direct Join operation.
Do you have any idea how run such query using just fluent ?

7

I’m not sure to be honest :thinking:

Theoretically, Fluent should support it I think. But if you’re having issues, try asking in Vapor’s Discord to see if anyone there knows!