Decoding Vapor raw query to MySQLModel

Question

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
    }
}

The table does exists but it was created in MySQLWorkbench not with migration. I don't think that's the problem also because the ClassB does work, if it didn't existed that would not work, it has to with the MySQLModel class. @Nick — Juan Carlos
And I can use the class perfectly with the ORM is when I use Raw Query the problem. — Juan Carlos
Can you post the raw query and how you are attempting to decode it? — Nick
I made an edit @Nick the sql is in fact part of the problem. — Juan Carlos
@Nick apparently the problem lies in the naming of tables to "c". — Juan Carlos

Juan Carlos Juan Carlos · Accepted Answer · 2020-01-02T23:47:03

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 ...);

Decoding Vapor raw query to MySQLModel

1 Answers