How to alias selected columns in swift 2 ?

[huynhphan89]

On branch swift_2.0, alias func was removed in Expression.swift. I dont see any document about the new way to alias columns.

Thanks.

[stephencelis]

Aliasing functionality has been limited to tables during the rewrite, where the functionality is required for joins to work. Aliasing column/expressions doesn't seem to help in our expression layer, although I may have missed a use case. Would you please provide your use case?

[shnhrrsn]

My use case: I'm selecting all columns in table a, joining table b and selecting just one column from table b that happens to share the name of a column in table a.

If I want to access the that column in table b, it means all of my existing models/queries that reference the column of the same name in table a needs to be updated to support namespacing for just this one scenario.

If I were able to select the column in table b aliased to a different name, all of my existing code would work without modification. Essentially I'd like the ability to build this query: SELECT a.*, b.name as otherName FROM a JOIN b ...

[stephencelis]

My experimentation showed that these aliases are actually meaningless outside of the SQLite console. You should be able do do this, though:

let a = Table("a")
let b = Table("b")

for row in db.prepare(a.join(b, on: condition)) {
    let aName = row[a[name]]
    let bName = row[b[name]]
}

It doesn't limit table b to just select the name, but that appears to be a bug that needs to be fixed (#249).

[shnhrrsn]

So what I'm actually doing is a little closer to this:

class Model {
   let name: String

   init(row: Row) {
      self.name = row[name]
   }
}

let a = Table("a")
let b = Table("b")

for row in db.prepare(a.join(b, on: condition)) {
   let model = Model(row)
   // Some how use b[name]
}

I initially tried to just update model to do self.name = row[table[name]] however queries that weren't joining were failing saying the column didn't exist:

fatal error: no such column '"a"."name"' in columns: [""name"", ... ]

If this is supposed to work, than I suppose that solution would be ok, but it would be easier if I could do something along the lines of:

let a = Table("a")
let b = Table("b")
let otherName = b[name].alias("otherName")

for row in db.prepare(a.select(a[*], otherName).join(b, on: condition)) {
    let model = Model(row)
    let bName = row[otherName]
}

This would allow me to isolate this specific bit of code to where I need it, and allow everything else to remain as it is for when namespacing isn't necessary.

[mySelfSystem]

My use case is providing query results to other iOS Apps via an External API. There is a person table and a measurement table. There are many measurements for any one person, and the column person_oid joins them.

For one particular query to the external Apps, I need to provide for each person the quantity of measurements associated with that person (along with the other columns of a person record). Since this is an external API and some external App that I did not write is getting the results, I want to provide that count with a nice easily understood alias column name rather some default column name including the function.

My SQL statement that works quite well on Android SQLite and a server-based mySQL: SELECT person.*,COUNT(measurement.person_oid) AS usage_measurements_qty FROM person LEFT JOIN measurement ON (person.person_oid = measurement.person_oid) GROUP BY person.person_oid

I can just about build the equivalent query in SQLite.swift, except for the alias.

let personTbl = Table("person")
let measureTbl = Table("measurement")
let PERSON_OID = Expression<Int64>("person_oid")
let query = personTbl.select(personTbl[*],measureTbl[PERSON_OID].count)
    .join(.LeftOuter, measureTbl, on:personTbl[PERSON_OID] == measureTbl[PERSON_OID])
     .group(personTbl[PERSON_OID])

Of course what I would like is the ability to do something like: measureTbl[PERSON_OID].count.alias("usage_measurements_qty")

Looking at the Expression.swift file, in extension ExpressionType, it seems like a simple addition to the source code accomplishes this:

extension ExpressionType {

    public var expression: Expression<Void> {
        return Expression(template, bindings)
    }

    public var asc: Expressible {
        return " ".join([self, Expression<Void>(literal: "ASC")])
    }

    public var desc: Expressible {
        return " ".join([self, Expression<Void>(literal: "DESC")])
    }

    // NOTE: MODIFICATION TO BASELINE:  added support for column aliasing, e.g.: "SELECT column AS alias ... FROM ..."
    public func alias(name:String) -> Expressible {
        return " ".join([self, Expression<Void>(literal: "AS \(name)")])
    }
}

And indeed in a fork of the SQLite.swift source code that I'm using in developing my iOS App, I added said 4 lines of code, and it works properly.

let personTbl = Table("person")
let measureTbl = Table("measurement")
let PERSON_OID = Expression<Int64>("person_oid")
let query = personTbl.select(personTbl[*],measureTbl[PERSON_OID].count.alias("usage_measurements_qty"))
    .join(.LeftOuter, measureTbl, on:personTbl[PERSON_OID] == measureTbl[PERSON_OID])
     .group(personTbl[PERSON_OID])
print(query.asSQL())

resulted in: SELECT \"person\".*, count(\"measurement\".\"person_hash_oid\") AS usage_qty_measurements FROM \"person\" LEFT OUTER JOIN \"measurement\" ON (\"person\".\"person_hash_oid\" = \"measurement\".\"person_hash_oid\") GROUP BY \"person\".\"person_hash_oid\

[gverdouw]

Another place this is useful is when trying to use Codable for the result -- trying to get a CodingKeys case to satisfy the generated Table.Column name is very painful (maybe impossible?). I ended up just creating the objects manually instead

[Devangbhatt93]

_extension ExpressionType {

public var expression: Expression<Void> {
    return Expression(template, bindings)
}

public var asc: Expressible {
    return " ".join([self, Expression<Void>(literal: "ASC")])
}

public var desc: Expressible {
    return " ".join([self, Expression<Void>(literal: "DESC")])
}

// NOTE: MODIFICATION TO BASELINE:  added support for column aliasing, e.g.: "SELECT column AS alias ... FROM ..."
public func alias(name:String) -> Expressible {
    return " ".join([self, Expression<Void>(literal: "AS \(name)")])
}

}_

This is not working for me.

Error: join' is inaccessible due to 'internal' protection level

[adamwulf]

any movement on this? I have the same issue as https://github.com/stephencelis/SQLite.swift/issues/225#issuecomment-241376027 and would like to provide specific column names to aggregate columns