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

Question

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.

I didn't address the "performance" aspect because I'm not sure what kind of performance issues you were seeing. — stephencelis
– stephencelis, Commented May 1, 2015 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
– Longi, Commented May 1, 2015 at 15:50

stephencelis · Accepted Answer · 2015-05-01 15:09:31Z

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)

edited May 1, 2015 at 15:09

answered May 1, 2015 at 15:04

stephencelis

4,9642 gold badges31 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

danielsalare Over a year ago

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?

stephencelis Over a year ago

@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.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related