swift - 使用 GRDB，使用原始 sql 但以方便的形式使用结果？

Question

这是一个 SQL 查询示例，您无法使用 GRDB 中的便利构建器构建它...

let q = "job.id, job.name, job.city, 

ifnull(jobcategory.value, 'no category'),
ifnull(jobpriority.value, 'no priority'),

from job  

left join jobcategory on job.category = jobcategory.id
left join jobpriority on job.priority = jobpriority.id

where job.user = 13"

(事实上，我举了一个例子，你不能在任何旧的、支持较少的 iOS Swift SQL 库中构建)

然后是类似的东西

for ..
 let id = ..
 let name = ..
 let city = ..
 let category = ..
 let priority = ..

我听说使用 GRDB，您实际上可以使用原始 SQL(即，实际上什至不使用 GRDB 的查询构建器)，但是，结果仍然使用便利消费*，注意类型等。

如果是这样，实际上你是如何在示例中做到这一点的？

Answer 1

GRDB 提供了一个查询构建器:

let persons = try Person.filter(emailColumn != nil).fetchAll(db) // [Person]

它也理解 SQL:

let persons = try Person.fetchAll(db, "SELECT * FROM persons WHERE email IS NOT NULL")

上面的两个代码片段都将数据库行转换为 Person 实例。 RowConvertible 协议(protocol)和 GRDB 提供的功能齐全的 Record 类支持这种转换。下面的代码使用协议(protocol):

struct Person {
    let email: String
    let name: String
}

extension Person : RowConvertible {
    init(row: Row) {
        email = row.value(named: "email")
        name = row.value(named: "name")
    }
}

init(row:) 构造函数用于“查询接口(interface)”请求 Person.filter(...).fetchAll(db)，以及SQL 请求 Person.fetchAll(db, "SELECT ...")。

这就是当您想使用原始 SQL 时 GRDB 不会惩罚您 的意思。您的自定义记录类型支持开箱即用的查询接口(interface)请求和 SQL 请求。使用这两种技术获取记录同样容易:

// Two one-liners:
let persons = try Person.filter(emailColumn != nil).fetchAll(db)
let persons = try Person.fetchAll(db, "SELECT * FROM persons WHERE email IS NOT NULL")

现在，您的示例可以写成:

struct Job {
    let id: Int64
    let name: String
    let city: String
    let category: String
    let priority: String
}

extension Job : RowConvertible {
    init(row: Row) {
        id = row.value(named: "id")
        name = row.value(named: "name")
        city = row.value(named: "city")
        category = row.value(named: "category")
        priority = row.value(named: "priority")
    }
}

try dbQueue.inDatabase { db in
    let q = "SELECT job.id, job.name, job.city, " +
        " IFNULL(jobcategory.value, 'no category') AS category, " +
        " IFNULL(jobpriority.value, 'no priority') AS priority " +
        "FROM job " +
        "LEFT JOIN jobcategory ON job.category = jobcategory.id " +
        "LEFT JOIN jobpriority ON job.priority = jobpriority.id " +
        "WHERE job.user = 13"

    let jobs = try Job.fetchAll(db, q)
}

由于 category 和 priority 不是作业的列，您可能更愿意将上述结构一分为二:

struct Job {
    let id: Int64
    let name: String
    let city: String
}

struct ExtendedJob {
    let job: Job
    let category: String
    let priority: String
}

extension Job : RowConvertible {
    init(row: Row) {
        id = row.value(named: "id")
        name = row.value(named: "name")
        city = row.value(named: "city")
    }
}

extension ExtendedJob : RowConvertible {
    init(row: Row) {
        job = Job(row: row)
        category = row.value(named: "category")
        priority = row.value(named: "priority")
    }
}

try dbQueue.inDatabase { db in
    let q = "SELECT job.id, job.name, job.city, " +
        " IFNULL(jobcategory.value, 'no category') AS category, " +
        " IFNULL(jobpriority.value, 'no priority') AS priority " +
        "FROM job " +
        "LEFT JOIN jobcategory ON job.category = jobcategory.id " +
        "LEFT JOIN jobpriority ON job.priority = jobpriority.id " +
        "WHERE job.user = 13"

    let jobs = try ExtendedJob.fetchAll(db, q)
}

您最终可以将自定义 SQL 查询封装在“自定义请求”中:

extension ExtendedJob {
    static func filter(userId: Int64) -> AnyTypedRequest<ExtendedJob> {
        let request = SQLRequest(
            "SELECT job.id, job.name, job.city, " +
            " IFNULL(jobcategory.value, 'no category') AS category, " +
            " IFNULL(jobpriority.value, 'no priority') AS priority " +
            "FROM job " +
            "LEFT JOIN jobcategory ON job.category = jobcategory.id " +
            "LEFT JOIN jobpriority ON job.priority = jobpriority.id " +
            "WHERE job.user = ?",
            arguments: [userId])
        return request.asRequest(of: ExtendedJob.self)
    }
}

// No SQL in sight:
let jobs = try dbQueue.inDatabase { db in
    try ExtendedJob.filter(userId: 13).fetchAll(db)
}