UpsertUsing feature proposal

[3rgo]

I was faced today with a situation where I had to upsert a very large amount of data coming from another table.
On my first attempt it failed because of the number of binding being over the maximum (SQLSTATE[HY000]: General error: 7 number of parameters must be between 0 and 65535)
I could get rid of this warning by chunking the inserted data, but since it is coming from another table, instead of fetching to insert it directly, I looked into insertUsing and insertOrIgnoreUsing. In my case, ignoring was not an option, I wanted an update...
So I built this macro for PostgreSQL :

Builder::macro('upsertUsing', function (Builder $query, $uniqueBy, $update = null) {
    /** @var \Illuminate\Database\Query\Builder $this */

    $this->applyBeforeQueryCallbacks();

    $columns = $query->columns;

    [$sql, $bindings] = $this->createSub($query);

    $insertSql = $this->grammar->compileInsertOrIgnoreUsing($this, $columns, $sql);

    $uniqueColumns = (new Collection((array) $uniqueBy))->map(function ($column) {
        return $this->grammar->wrap($column);
    })->implode(', ');

    $updateColumns = (new Collection($update))->map(function ($value, $key) {
        return is_numeric($key)
            ? $this->grammar->wrap($value) . ' = ' . $this->grammar->wrap('excluded') . '.' . $this->grammar->wrap($value)
            : $this->grammar->wrap($key) . ' = ' . $this->grammar->parameter($value);
    })->implode(', ');

    $upsertSql = $insertSql . ' on conflict (' . $uniqueColumns . ') do update set ' . $updateColumns;

    return $this->connection->affectingStatement($upsertSql, $this->cleanBindings($bindings));
});

I looked into it and replicating the behaviour for MySQL shouldn't be an issue, so if people are interested and the core team is OK with it, I'm willing to submit a PR (with cleaner code, and -of course- tests).

[shaedrich]

Raw SQL shouldn't be part of the query builder, which is an abstraction, but rather of the respective database engines' grammars.

[3rgo]

Yes I am aware of that, but for a quick Macro on my project combined with a PostgreSQL specificity, I did not see the point in creating a macro for the Grammar. The idea is to provide the code here in case someone is in the same situation as me.

I'd -of course- separate concerns in a pull request since that would require a different syntax for each DBMS.

[shaedrich]

Ah, okay. Thanks for the explanation. That makes sense. Sorry for the confusion.