sqlite.swift如何执行子查询

Question

我正在尝试一个查询，以从消息表中获取特定会话中的最新N条消息。我认为这是正确的sql：

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

我在sqlite.swift中尝试过：

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

一旦消息量超过限制，它就不起作用了。有没有办法查看sqlite.swift查询生成了什么sql？有什么建议吗？

编辑:我也尝试了原始SQL查询，但现在我不确定如何提取结果。我觉得这应该是最后的办法：

    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]!)")
        }
    }

rowindex的类型是Binding，所以我不确定如何在那里检索值。请帮帮我！

谢谢

Answer 1

好的，看起来子查询可能太复杂了，无法用sqllite.swift表达。我最终选择了原始的sql查询。您可以通过转换绑定来检索结果，如下所述：

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