Create Decodable objects after LEFT OUTER JOIN

[p-nicolaou]

I am wondering if there is a way to init my decodable object after I do left outer join two (or more) Tables. The problem I am facing is that when I join two tables, the column names automatically get prefixed with Table's name. Thus, when I do Customer(from: row.decoder()) this fails as my CodingKeys do not include my Table's name upfront. Is there any way to achieve this without renaming them (CodingKeys)?

[nalexander50]

I'm facing kind of the opposite issue. I want the CodingKeys to be prefixed with their table_name. But, at decode time, the decoder claims that it can't find the right key.

class A {

    let pokemonID: Int

    enum CodingKeys: String, CodingKey {
        case pokemonID = "pokemon.id"
    }

    init(decoder aDecoder: Decoder) throws {
        let values = try! aDecoder.container(keyedBy: CodingKeys.self)
        self.pokemonID = try! values.decode(Int.self, forKey: .pokemonID)
    }
}

func fetch() -> [A] {
    let tablePokemon = Table("pokemon")
    let tableEncounters = Table("encounters")
    let colPokemonID = Expression<Int>("pokemon_id")
    let colID = Expression<Int>("id")
        
    let query =
        tablePokemon.join(tableEncounters,
                          on: tablePokemon[colID] == tableEncounters[colPokemonID])
                    .filter(tablePokemon[colID] == 1)
            
    return try! self.connection.prepare(query).map { row in
        return try! row.decode()
    }
}

Error:

Fatal error: 'try!' expression unexpectedly raised an error: No such column `"pokemon.id"` in columns ["\"encounters\".\"encounter_slot_id\"", "\"encounters\".\"id\"", "\"encounters\".\"location_area_id\"", "\"encounters\".\"max_level\"", "\"encounters\".\"min_level\"", "\"encounters\".\"pokemon_id\"", "\"encounters\".\"version_id\"", "\"pokemon\".\"base_experience\"", "\"pokemon\".\"height\"", "\"pokemon\".\"id\"", "\"pokemon\".\"identifier\"", "\"pokemon\".\"is_default\"", "\"pokemon\".\"order\"", "\"pokemon\".\"species_id\"", "\"pokemon\".\"weight\""]

Aside from the garbled mess of escape quotes, pokemon.id plainly appears in the list of columns in the error. I have no idea why this doesn't work. If I reduce the CodingKey to just id, naturally it fails because id is ambiguous (both pokemon and encounter have id). If I change the CodingKey to pokemon_id, it works because it finds pokemon_id on encounter -- even though I didn't namespace it.

I must have some kind of fundamental misunderstand of how this decoding happens and what values should appear in my CodingKeys. Right now, I'm at an impasse because values like id, identifier, and pokemon_id are used over and over in nearly every table. As it stands right now, I have no idea how to decode those columns after a join.

[groue]

You may want to check https://github.com/groue/GRDB.swift and its associations: https://github.com/groue/GRDB.swift/blob/master/Documentation/AssociationsBasics.md

They make decoding joined tables a breeze

[nathanfallet]

Please ask on StackOverflow for this kind of questions (we are tracking bugs on GitHub)

[justinmeiners]

@nathanfallet I think this is potential shortcoming of the library, not a usage clarification. Row allows us to access namespaced columns by unprefixed name, provided there is no ambiguity. However, the SQLiteDecoder appears to define its set of available keys by only the namespaced version. This means Codable cannot find namespaced keys. If you cannot use Codable with any kind of join, it's probably worthless.

This is also described in #225