`join` with `delete` queries

[richwolf]

It seems that join cannot be added to a delete query. For example, this works in my desktop MySQL client (Querious) but cannot be coded:

DELETE choices FROM choices
JOIN elections ON choices.election_uuid = elections.uuid 
WHERE
  choices.uuid = UUID_TO_BIN('B337E365F02F11EDB0B50A07A8932707')
  AND elections.author = "richwolf";

into something like this:

let queryBuilder = sqlDatabase.delete(from: choicesTable)
  .join(
    electionsTable,
    on: electionUUIDColumn, .equal, choiceElectionUUIDColumn
  )
  .where(choiceUUIDColumn, .equal, SQLBind(id))
  .where(electionAuthorColumn, .equal, SQLBind(author))

Of course, an obvious workaround is simply to code a raw query, but I was hoping to hack something together. In my reading of the sources, I kind of gather that joins work for select queries because SQLSelectBuilder conforms to the SQLSubqueryClauseBuilder protocol. I tried to replicate that in SQLDeleteBuilder ... and my join() does get called ... but it doesn't seem to write to the joins array that I hacked into the SQLDelete query struct. join does find the predicates in my code ... but it doesn't write anything to the self.joins in the query object and is (therefore) not serialized (I was also careful to amend the serialize method in the SQLDelete query struct to look for a non-empty joins array and write out the join in a way similar to the way the SQLSelect serialize method does things).

I feel like I'm kinda close, but not quite there. I guess what I'm looking for here (assuming I explained it anywhere near clearly 😄) is a "you're on the right track" or "no, you wanna look at this" or even, "not ever gonna be a thing if you P/R, just go with the raw query."

[0xTim]

This should probably be supported at least in SQLKit if Fluent can't support it @gwynne ? Though I seem to remember this coming up in Fluent

[gwynne]

This is partially a limitation of SQL in general (not to mention Fluent 4's hamfisted support for MongoDB); join-enabled DELETE syntax is a MySQL-specific feature largely obsoleted in MySQL 8.0 by the introduction of support for Common Table Expressions (WITH syntax). However, it is not difficult to tack support for this functionality onto SQLKit. Here's how I do it in production code (the lack of portability of the syntax, not to mention it being obsoleted, is why I haven't bothered to upstream this):

import SQLKit

/// A trivial variant of ``SQLKit/SQLDelete`` which provides MySQL's `DELETE IGNORE...` syntax,
/// as well as partial support for multi-table syntax (joined tables are always lookup-only).
/// The `RETURNING` clause is, of course, not supported.
///
/// > Warning: Aliasing the primary table will not work correctly if joins are specified.
public struct SQLMySQLDelete: SQLExpression {
    public var table: SQLExpression
    public var ignoreErrors: Bool
    public var predicate: SQLExpression?
    public var joins: [SQLExpression]

    /// Creates a new `SQLDelete`.
    public init(table: SQLExpression) {
        self.table = table
        self.ignoreErrors = false
        self.joins = []
    }

    public func serialize(to serializer: inout SQLSerializer) {
        serializer.statement {
            $0.append("DELETE")
            if self.ignoreErrors {
                $0.append("IGNORE")
            }
            $0.append("FROM", self.table)
            if !self.joins.isEmpty {
                $0.append("USING", self.table)
                $0.append(SQLList(self.joins, separator: SQLRaw(" ")))
            }
            if let predicate = self.predicate {
                $0.append("WHERE", predicate)
            }
        }
    }
}

/// Variant of ``SQLKit/SQLDeleteBuilder`` for ``SQLMySQLDelete``.
public final class SQLMySQLDeleteBuilder: SQLQueryBuilder, SQLPredicateBuilder, SQLJoinBuilder {
    public var delete: SQLMySQLDelete

    /// See ``SQLKit/SQLQueryBuilder``.
    public var query: SQLExpression { self.delete }

    /// See ``SQLKit/SQLQueryBuilder``.
    public var database: SQLDatabase

    /// See ``SQLKit/SQLJoinBuilder``.
    public var joins: [SQLExpression] {
        get { self.delete.joins }
        set { self.delete.joins = newValue }
    }

    /// See ``SQLKit/SQLPredicateBuilder``.
    public var predicate: SQLExpression? {
        get { self.delete.predicate }
        set { self.delete.predicate = newValue }
    }

    public init(_ delete: SQLMySQLDelete, on database: SQLDatabase) {
        self.delete = delete
        self.database = database
    }

    public func ignoringErrors() -> Self {
        self.delete.ignoreErrors = true
        return self
    }
}

extension SQLDatabase {
    public func mysql_delete(from table: String) -> SQLMySQLDeleteBuilder {
        return self.mysql_delete(from: SQLIdentifier(table))
    }

    public func mysql_delete(from table: SQLExpression) -> SQLMySQLDeleteBuilder {
        return .init(.init(table: table), on: self)
    }
}

With this code included in your project, just replace sqlDatabase.delete(from: choicesTable) with sqlDatabase.mysql_delete(from: choicesTable) and your code will otherwise work as written.