Access column names by Expression name in query results about sqlite.swift HOT 6 CLOSED

Sorry, this is hopefully just lack of documentation (which I'm working on), so here goes:

If you write SQL by hand, you need to access its data by hand. If you want to use Expression objects, you must use Query objects to build the queries.

Join queries add complexity, especially when a table's column name collides with a column of the table it's being joined to. This means when when you join a table to itself, every column you reference is ambiguous. In the end, you need to disambiguate columns. You can do this by:

1. Namespacing the column expressions, using query[column] (which becomes query.column).
2. For tables that join themselves, alias the query using query.alias("alias_name"), which is effectively query AS alias_name.

E.g., with your example as a guide:

let id = Expression<Int>("id")
let categoryid = Expression<Int>("categoryid")
let name = Expression<String>("name")
// etc.

let PDFs = db["pdf"]
let categories = db["categories"]
// all the above is usually defined once and only once, elsewhere

let query = PDFs.
    select(PDFs[*], categories[id], categories[name], categories[thumb], /* etc. */).
    join(categories, on: categories[id] == PDFs[categoryid]).
    filter(PDFs[languageid] == language.id)

for row in query {
    println("category name: \(row[categories[name]])")
}

There could be bugs lurking above, especially using table[*] and then referencing its columns. Please report back if you have issues.

from sqlite.swift.

Great.
Thanks again Stephen

from sqlite.swift.

I'm writing some test cases for join row access right now. I'll try to patch up any bugs I find soon.

from sqlite.swift.

Row access is currently broken. You can access fields using their column name only (row[name] instead of row[categories[name]]), which means that duplicate column names will overwrite the previous result.

Unfortunately, iOS doesn't compile SQLite with SQLITE_ENABLE_COLUMN_METADATA, which makes it a bit more difficult to disambiguate column names during statement execution:

https://www.sqlite.org/compile.html#enable_column_metadata

I'll look into tracking Query column names better internally, but for now consider this a bug.

from sqlite.swift.

Fixed by 7fa9a57

from sqlite.swift.

1. If a Query calls select with specific column names, honor the namespacing or lack thereof explicitly.
2. If a Query calls select with a namespaced *, honor the namespacing while expanding the columns.
3. If a Query joins another table and the select is the default, *, namespace all expanded columns.
4. If a Query does not join another table, do not namespace expanded columns.

In other words, when you access row data during a join, either use select to document exactly how you'll access that data, or always namespace row column during retrieval:

row[categories[name]] // not row[name]

from sqlite.swift.