Vapor join and alsoDecode produces lots of nested tuples

Question

According to answer from question: vapor - Obtaining data from mysql using alsodecode() I was able to reproduce this for my model with multiple joins.

This resulted with multiple nested tuples which I have to access to get joined values. Is it possible to make other type of join or flatten this tuple structure to one level?

struct MyTuple: Encodable, Content {
    let title: String
    let publicationDate: Date
    let authorID: Author.ID
    let authorName: String
    let categoryID: Category.ID
    let categoryName: String
    let language: String
}

struct MyContext: Encodable {
    let title: String
    let books: [MyTuple]
}

func getBooks(_ req: Request) throws -> Future<[MyTuple]> {
    return Book.query(on: req)
        .join(\Category.id, to: \Book.categoryID)
        .join(\Language.id, to: \Book.languageID)
        .join(\ProgrammingLanguage.id, to: \Book.programmingLanguageID)
        .join(\Author.id, to: \Book.authorID)
        .alsoDecode(Category.self)
        .alsoDecode(Language.self)
        .alsoDecode(ProgrammingLanguage.self)
        .alsoDecode(Author.self)
        .sort(\Book.publicationDate, .descending).all().flatMap(to: [MyTuple].self) { tuples in
            print(tuples)
            var tempTuples: [MyTuple] = []
            for tuple in tuples {
                tempTuples.append(try MyTuple(
                        title: tuple.0.0.0.0.title,
                        publicationDate: tuple.0.0.0.0.publicationDate,
                        authorID: tuple.1.requireID(),
                        authorName: "\(tuple.1.name) \(tuple.1.surname)",
                        categoryID: tuple.0.0.0.1.requireID(),
                        categoryName: tuple.0.0.0.1.name, language: tuple.0.0.1.code ) )
            }

            return Future.map(on: req) {
                tempTuples
            }
        }
}

Answer 1

It is possible with raw query, something like this

func getBooks(_ req: Request) throws -> Future<[MyTuple]> {
    let rawSQL = """
    SELECT
        Book.title,
        Book.publicationDate,
        Book.authorID,
        concat(Author.name, " ", Author.surname) as authorName,
        Book.categoryID,
        Category.name as categoryName,
        Language.code as language
    FROM Book
    JOIN LEFT Category ON Category.id = Book.categoryID
    JOIN LEFT Language ON Language.id = Book.languageID
    JOIN LEFT ProgrammingLanguage ON ProgrammingLanguage.id = Book.programmingLanguageID
    JOIN LEFT Author ON Author.id = Book.authorID
    ORDER BY Book.publicationDate DESC
"""
    return req.requestPooledConnection(to: .mysql).flatMap { conn in
        return conn.raw(rawSQL).all(decoding: MyTuple.self).always {
            try? req.releasePooledConnection(conn, to: .mysql)
        }
    }
}

or with raw query using SwifQL library

func getBooks(_ req: Request) throws -> Future<[MyTuple]> {
    return SwifQL
            .select(\Book.title,
                    \Book.publicationDate,
                    \Book.authorID,
                    Fn.concat(\Author.name, " ", \Author.surname) => "authorName",
                    \Book.categoryID,
                    \Category.name => categoryName,
                    \Language.code => language)
            .from(Book.table)
            .join(.left, Category.table, on: \Category.id == \Book.categoryID)
            .join(.left, Language.table, on: \ Language.id == \Book.languageID)
            .join(.left, ProgrammingLanguage.table, on: \ ProgrammingLanguage.id == \Book.programmingLanguageID)
            .join(.left, Author.table, on: \ Author.id == \Book. Book.authorID)
            .orderBy(.desc(\Book.publicationDate))
            .execute(on: req, as: .mysql)
            .all(decoding: MyTuple.self)
}

the same as above but with select builder

func getBooks(_ req: Request) throws -> Future<[MyTuple]> {
    let query = SwifQLSelectBuilder()
    query.select(\Book.title)
    query.select(\Book.publicationDate)
    query.select(\Book.authorID)
    query.select(Fn.concat(\Author.name, " ", \Author.surname) => "authorName")
    query.select(\Book.categoryID,
    query.select(\Category.name => categoryName)
    query.select(\Language.code => language)
    query.from(Book.table)
    query.join(.left, Category.table, on: \Category.id == \Book.categoryID)
    query.join(.left, Language.table, on: \Language.id == \Book.languageID)
    query.join(.left, ProgrammingLanguage.table, on: \ProgrammingLanguage.id == \Book.programmingLanguageID)
    query.join(.left, Author.table, on: \Author.id == \Book. Book.authorID)
    query.orderBy(.desc(\Book.publicationDate))
    return query.build()
                .execute(on: req, as: .mysql)
                .all(decoding: MyTuple.self)
}