对于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协议和GRDB提供的功能齐全的Record类的支持。下面的代码使用该协议：

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:)构造函数既用于“查询接口”请求Person.filter(...).fetchAll(db)，也用于SQL Person.fetchAll(db, "SELECT ...")。

这就是的意思，当您想要使用原始时，GRDB并不会惩罚您。您的自定义记录类型支持即时查询接口请求和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)
}

由于类别和优先级不是作业的列，所以您可能更喜欢将上面的结构分成两部分：

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)
}