CODEWITHSUNDEEP
swiftvapor
JC_
JC_

Reputation: 577

Decoding Vapor raw query to MySQLModel

I'm trying to run a SQL Raw Query in a Vapor application, and decoded to a MySQLModel but apparently it returns an error.

final class ClassA: MySQLModel {    
    var id: Int?
    var title: String
    var description: String
}

But when I run a simple Select From raw query it returns a decoding error: "Value of type 'String' required for key 'title'." with it's weird because the sql run in the MySQL console does run correctly. The thing is when I decode the same query but instead of using a MySQLModel I use a Content like this:

final class ClassB: Content {    
    var id: Int?
    var title: String
    var description: String
}

And this is actually ends up decoding without errors.

Edit: The ClassA works perfectly with the regular ORM, it is only when I try to decode from Raw Query that it fails.

Edit: As ask by @Nick the sql does affect the response, when run:

SELECT * FROM ClassA

The return comes back with no error, but my sql includes a complex Subquery like this: 

SELECT c.* FROM ClassA c WHERE c.id IN (SELECT id FROM ...);

Note: When run in MySQL Console the query returns no Error and as said the query can be decoded to a Content class exactly like ClassA Example: ClassB.

Edit: The code for running and decoding the raw query is simply: 

return request.withNewConnection(to: .mysql) { (con) -> EventLoopFuture<[ClassA]> in
    return con.raw(sql).all(decoding: ClassA.self).map { (classes) -> [ClassA] in
        return classes
    }
}

Upvotes: 1

Views: 358

Answers (1)

JC_
JC_

Reputation: 577

I resolve the problem by trying different permutation of the same Query as proposed that the problem was there by @Nick.

The problem lies here in the query (Not the Subquery as initially thought), the difference between this query:

SELECT * FROM ClassA;

And this query:

SELECT c.* FROM ClassA c WHERE c.id IN (SELECT id FROM ...);

besides the subquery is the naming of the table to "c" this notation for some reason is invalid for decoding the MySQLModel type in Vapor they will only work on Content types.

The solution is to remove the notation and use the entire name in the Query:

SELECT ClassA.* FROM ClassA WHERE ClassA.id IN (SELECT id FROM ...);

Upvotes: 2

Related Questions