Longi Longi - 2 months ago 9x
Swift Question

How to execute a complex sql statement and get the results in an array?

I would like to execute a fairly complex SQL statement using SQLite.swift and get the result preferably in an array to use as a data source for a tableview. The statement looks like this:

SELECT defindex, AVG(price) FROM prices WHERE quality = 5 AND price_index != 0 GROUP BY defindex ORDER BY AVG(price) DESC

I was studying the SQLite.swift documentation to ind out how to do it properly, but I couldn't find a way. I could call prepare on the database and iterate through the Statement object, but that wouldn't be optimal performance wise.

Any help would be appreciated.


Most sequences in Swift can be unpacked into an array by simply wrapping the sequence itself in an array:

let stmt = db.prepare(
    "SELECT defindex, AVG(price) FROM prices " +
    "WHERE quality = 5 AND price_index != 0 " +
    "GROUP BY defindex " +
    "ORDER BY AVG(price) DESC"
let rows = Array(stmt)

Building a data source from this should be relatively straightforward at this point.

If you use the type-safe API, it would look like this:

let query = prices.select(defindex, average(price))
                  .filter(quality == 5 && price_index != 0)
let rows = Array(query)