SQLite.swift performing a left join and using the data

Question

I am trying to perform a left join

SELECT TProgram.ProgramName, TProgram.ProgramPath From TProgram
LEFT JOIN TIcons ON TIcons.ProgramName = TProgram.ProgramName
WHERE TIcons.ProgramName IS NULL
GROUP BY TProgram.ProgramName

with swift. So i did the following.

let program = db["TProgram"]
let icons = db["TIcons"]
let programNameDB = Expression<String>("ProgramName")
let programNameDBI = Expression<String>("ProgramName")
let programPathDB = Expression<String>("ProgramPath")
let all = program.select(programNameDB, programPathDB).join(Query.JoinType.LeftOuter, icons, on: programNameDB == icons[programNameDBI]).filter(programNameDBI == "").group(programNameDB)

i cannot check if it is working because the following results in an fatal error: ambiguous column name:

let item = all.first!
let arr = Array(all)

If I try println(all) I can see "TProgram" in the debug console.

How can i access the data?

UPDATE I tried the following. Error is gone but no values seem to be in there.

func readTProgramNames() -> (Query){
    let program = db["TProgram"]
    let icons = db["TIcons"]
    let programNameDB = Expression<String>("ProgramName")
    let programNameDBI = Expression<String>("ProgramName")
    let programPathDB = Expression<String>("ProgramPath")
    let all = program
        .select(program[programNameDB], program[programPathDB])
        .join(.LeftOuter, icons, on: program[programNameDB] == icons[programNameDBI])
        .filter(icons[programNameDBI] == "")
        .group(program[programNameDB])

    let item = all.first!

    println(item[program[programNameDB]])

    return all
}

score 3 · Accepted Answer · answered Jul 24 '15 at 14:49

Got it. The String? wer important and the .leftouter

func readTProgramNames(){
    let program = db["TProgram"]
    let icons = db["TIcons"]
    let programNameDB = Expression<String?>("ProgramName")
    let programNameDBI = Expression<String?>("ProgramName")
    let programPathDB = Expression<String?>("ProgramPath")
    //let all = table.select(programNameDB, programPathDB).group(programNameDB)
    //let all = program.join("LEFT OUTER", icons, on: programNameDB == icons[programNameDBI])
    let all = program
        .select(program[programNameDB], program[programPathDB])
        .join(.LeftOuter, icons, on: program[programNameDB] == icons[programNameDBI])
        .filter(icons[programNameDBI] == nil)
        .group(program[programNameDB])

    for item in all{
        println("programName: \(item[program[programNameDB]]!)")
    }
}

SQLite.swift performing a left join and using the data

1 Answers1