How to make a dynamic, calculated column?

[Wolfaks]

Hello. How i can to make a dynamic, calculated column? Which is calculated using other columns and not stored in the database. Which can be used for data output and sorting.

Example: SELECT a+b as c from test ORDER BY c ASC;

Thanks.

[juanjoseluisgarcia]

Hi @Wolfaks this project seems to be completely abandoned and the author is ignoring absolutely all comments. He is active in a course and abandoned this repo. My suggestion to you is that you use another library like GRDB.Swift that is fully maintained.

[newtimber]

You just extract the data from your sql database into your local table, calculating the data for extra column as you go along. This is not something that SQLite will do for you.

SQLite is not being properly maintained but it works well if all you want is an efficient bridge between Swift and SQL.

I looked at GRDB but it seems to be designed around applications where usually an entire database record is wanted to be looked at at a time and not for for example reports where you only need to examine a small number of fields in each record.

[groue]

@newtimber, GRDB indeed makes it handy to extract entire records, since it is such a common use case. But you can export a small number of fields as well. Since @Wolfaks wants a single column, this can give:

// SELECT * FROM player
let players = try Player.fetchAll(db) // [Player]

// SELECT score + bonus AS total FROM player ORDER BY total
let totals = Player // [Int]
    .select((scoreColumn + bonusColumn).forKey("total"), as: Int.self)
    .order(Column("total"))
    .fetchAll(db)

See GRDB Requests for more information.

[juanjoseluisgarcia]

@newtimber, GRDB indeed makes it handy to extract entire records, since it is such a common use case. But you can export a small number of fields as well. Since @Wolfaks wants a single column, this can give:

// SELECT * FROM player
let players = try Player.fetchAll(db) // [Player]

// SELECT score + bonus AS total FROM player ORDER BY total
let totals = Player // [Int]
    .select((scoreColumn + bonusColumn).forKey("total"), as: Int.self)
    .order(Column("total"))
    .fetchAll(db)

See GRDB Requests for more information.

And on another unpopular opinion, it also offers a pure SQL interface where you can specify your SQL query which I personally prefer.

[newtimber]

@newtimber, GRDB indeed makes it handy to extract entire records, since it is such a common use case. But you can export a small number of fields as well. Since @Wolfaks wants a single column, this can give:

// SELECT * FROM player
let players = try Player.fetchAll(db) // [Player]

// SELECT score + bonus AS total FROM player ORDER BY total
let totals = Player // [Int]
    .select((scoreColumn + bonusColumn).forKey("total"), as: Int.self)
    .order(Column("total"))
    .fetchAll(db)

SQLite has compile-time type checking which I don't think GRDB has. In your example above, is there a check that the scoreColumn and bonusColumn are actually Ints? If bonusColumn has actually a boolean, saying whether the player ought to be given a bonus and you should be using bonusAmount for example, will your code run OK but produce the wrong result?

[groue]

Yes, @newtimber, it will run OK, and produce the results that were asked (summing the 0 and the 1 ~booleans~ integers stored in the bonus column to the values stored in the score column). This may sound horrible to you. But this is the difference between a library that pretends that "Boolean columns" exist (hint: Boolean columns do not exist, and see also how Double columns can be funny), or that booleans must never be interpreted as integers (hint: SQL life can be more funny than that), and a library that exposes the full power of SQLite to demanding users. IMHO, the fact that advanced used cases are possible does not really hinder the life of users who want to stay in the safe path: the bugs you are foreseeing just do not happen in practice. That's just my opinion as a library designer, of course. Typed columns are not wrong, but this is not quite where the biggest safety advantages lie.

[nathanfallet]

I'm not sure this is possible for now. It could be a great enhancement.

[nathanfallet]

Try with something like this, it could work:

let c = a + b // Where a and b are `Expression<Int>`
test.select(c).order(c)

[nathanfallet]

Closing for now, please reopen if you have any problem with this.