GRDBProblem.md

I started with this:

extension Book {
    static func all(byAuthorWithId authorId: Int64) -> QueryInterfaceRequest<Book> {
        return Book.filter(Book.authorId == authorId && Book.isPublished)
    }
}

And have been using it all over the app, refining it when necessary. It allows me to keep things DRY:

Book.all(byAuthorWithId: favoriteAuthorId)
    .filter(Book.year > 2000)
    .order(Book.title)

Now, I need to change the implementation of all(byAuthorWithId:) to include an INNER JOIN on another table, to filter out results:

extension Book {
    static func all(byAuthorWithId authorId: Int64) -> AnyTypedRequest<Book> {
        let sql = """
            SELECT DISTINCT books.*
            FROM books
            INNER JOIN chapters ON chapters.bookId = books.id
            WHERE books.authorId = :authorId AND books.isPublished = 1
            """
        return SQLRequest(sql, arguments: ["authorId": authorId]).asRequest(of: Book.self)
    }
}

But now I'm returning a AnyTypedRequest<Book> and all query refinements all over the app don't work on it.

Hello David!

First of all, I fully understand your problem. It has occurred to me twice.

Now, QueryInterfaceRequest knows its internal structure, and that's why it is able to be refined (.filter().select().distinct().etc().), to be counted efficiently (and without error), and to provide efficient support for transaction observers.

This means that QueryInterfaceRequest is not gonna support custom SQL snippets at critical places anytime soon. That would ruin too many useful features.

When you opt in for raw SQL, you have to refine yourself, you have to efficiently count yourself, and transaction observers will provide correct but minimum service (you can't provide any useful optimization).

We can only hope that QueryInterfaceRequest eventually can join (I really want to succeed). And I'm not talking about recursive queries, that some users want, too: they have exactly the same issue.

---

OK. So what are your current options?

I don't know if one can build a whole new kind of requests that's like QueryInterfaceRequest but with extra features. I have not audited public APIs of GRDB to know if it's even possible.

So I'm afraid you're stuck with raw SQL.

---

So how can you make it DRY again?

My own technique is to write a private SQL builder, and expose a few public/internal ad-hoc requests. I don't try to emulate request derivation, because it's too wide a topic. I just expose the requests needed by the app.

Here is my most complex setup so far:

/// High-level request
static func filter(projectId: Int64) -> AnyTypedRequest<Simulation> {
    return makeRequest(forProjectId: projectId)
}

/// High-level request
static func filter(propertyId: Int64) -> QueryInterfaceRequest<Simulation> {
    return filter(Columns.propertyId == propertyId)
}

/// High-level request
static func fetchLastUpdated(_ db: Database, forProjectId projectId: Int64) throws -> Simulation? {
    return try makeRequest(
        forProjectId: projectId,
        orderSQL: "simulations.lastUpdateDate DESC",
        limit: 1)
        .fetchOne(db)
}

/// Private low-level SQL request
private static func makeRequest(
    forProjectId projectId: Int64,
    whereSQL: String? = nil,
    orderSQL: String? = nil,
    limit: Int? = nil,
    arguments extraArguments: StatementArguments? = nil)
    -> AnyTypedRequest<Simulation>
{
    var whereChunks = ["properties.id IS NOT NULL OR repurchases.project_id IS NOT NULL"]
    if let whereSQL = whereSQL {
        whereChunks.append(whereSQL)
    }
    let queryWhere = "WHERE " + whereChunks.map { "(\($0))" }.joined(separator: " AND ")
    
    let queryOrder: String
    if let orderSQL = orderSQL {
        queryOrder = "ORDER BY \(orderSQL)"
    } else {
        queryOrder = ""
    }
    
    let queryLimit: String
    if let limit = limit {
        queryLimit = "LIMIT \(limit)"
    } else {
        queryLimit = ""
    }

    var arguments: StatementArguments = [projectId, projectId]
    if let extraArguments = extraArguments {
        arguments += extraArguments
    }
    
    return SQLRequest("""
        SELECT simulations.*
        FROM simulations
        LEFT JOIN properties ON properties.id = simulations.property_id AND properties.project_id = ?
        LEFT JOIN repurchases ON repurchases.project_id = simulations.repurchase_project_id AND repurchases.project_id = ?
        \(queryWhere) \(queryOrder) \(queryLimit)
        """,
        arguments: arguments).asRequest(of: self)
}

You'll notice that StatementArguments can be extended, and that is may help.

Meanwhile, I really do plan to ship support for joins in GRDB 3.0 :-)