1

I'm attempting a query to get the latest N messages in a particular conversation from a table of messages. I think this is the correct sql:

select * from
(select * from messages where convoId = to order by timestamp DESC limit 10)
order by timestamp ASC;

I have attempted this in sqlite.swift:

static let table = Table("messages")
let query = (table.filter(convoId == to).order(timestamp.desc).limit(10)).select(table[*]).order(timestamp.asc)

which is not working once the amount of messages goes past the limit. Is there any way to see what sql is produced by the sqlite.swift query? Any suggestions?

EDIT: I have also attempted the raw SQL query but now I'm not sure how to extract the result. I feel like this should be a last resort:

    let toQuoted = "'" + to + "'"
    let subQueryStr: String = [
        "(SELECT * FROM",
        MessageDataHelper.TABLE_NAME,
        "WHERE",
        MessageDataHelper.CONVO_ID, "=", toQuoted, "ORDER BY", MessageDataHelper.TIMESTAMP, "DESC LIMIT", String(5), ")"
    ].joined(separator: " ")

    let queryStr: String = [
        "SELECT * FROM",
        subQueryStr,
        ["ORDER BY", MessageDataHelper.TIMESTAMP, "ASC;"].joined(separator: " ")
    ].joined(separator: "\n")

    let stmt = try db.prepare(queryStr)
    for row in stmt {
        // ? how can this be used to create model structure
        for (index, name) in stmt.columnNames.enumerate() {
            print ("\(name)=\(row[index]!)")
        }
    }

row[index] is of type Binding, so I'm unsure how to retrieve the value there. Help please!

Thanks

rclark
  • 301
  • 3
  • 13

1 Answers1

1

Okay, so looks like sub query might be too complex to express in sqllite.swift. I ended up going with the raw sql query. You can retrieve the result by casting the binding as mentioned here:

Getting results from arbitrary SQL statements with correct binding in SQLite.swift

rclark
  • 301
  • 3
  • 13
  • If you ever dream of a Swift SQLite library that provides identical row consumption, whenever your use the SQL builder or raw SQL queries, check out [GRDB](http://github.com/groue/GRDB.swift). – Gwendal Roué Sep 06 '17 at 05:33