0

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.

Longi
  • 3,913
  • 2
  • 28
  • 38
  • I didn't address the "performance" aspect because I'm not sure what kind of performance issues you were seeing. – stephencelis May 01 '15 at 15:11
  • I didn't know you can cast a Statement into an Array so I thought you need to iterate to create an array (since you have a loop in your example). Thank you! – Longi May 01 '15 at 15:50

1 Answers1

7

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)
                  .group(defindex)
                  .order(average(price).desc)
let rows = Array(query)
stephencelis
  • 4,954
  • 2
  • 29
  • 22
  • What if I have multiple filters for example size and color, what if for column color I have the following options (all, blue, black & yellow) and in the db the column color only has blue black and yellow. How can I create a dynamic filter? – danielsalare Jul 22 '15 at 23:44
  • @edse You can create and hold onto a reference of a "base" query and apply the appropriate filter when it changes. I'd post sample code but it doesn't format in comments. Feel free to open a new question, though, if you still need help. – stephencelis Jul 23 '15 at 11:30