Laravel Query Builder Upsert Macro for MySQL

UpsertBuilder.php

<?php

namespace App\Database;

use Illuminate\Database\Query\Builder;
use Illuminate\Support\Collection;

class UpsertBuilder
{
    /** @var Builder */
    protected $builder;

    public function __construct(Builder $builder)
    {
        $this->builder = $builder;
    }

    /**
     * @param Collection $values
     *
     * @return Collection
     */
    protected function getWrappedColumns(Collection $values): Collection
    {
        return collect($values->first())->keys()->map([$this->builder->grammar, 'wrap']);
    }

    /**
     * @param Collection $values
     *
     * @return Collection
     */
    protected function getQuotedValues(Collection $values): Collection
    {
        return $values->map(function ($row) {
            return collect($row)->map(function ($value) {
                if (is_null($value)) {
                    return 'NULL';
                }

                return $this->builder->connection->getPdo()->quote($value);
            });
        });
    }

    /**
     * @param Collection $values
     *
     * @return string
     */
    protected function generateInsertValues(Collection $values): string
    {
        return $values->map(function (Collection $row) {
            return '(' . $row->implode(',') . ')';
        })->implode(',');
    }

    /**
     * @param Collection $columns
     *
     * @return string
     */
    protected function generateUpdateValues(Collection $columns): string
    {
        return $columns->map(function ($column) {
            return sprintf(
                '%s=%s',
                $column,
                "VALUES({$column})"
            );
        })->implode(',');
    }

    /**
     * @param array $values
     *
     * @return string
     */
    public function getQuery(array $values): string
    {
        $values         = collect($values);
        $wrappedColumns = $this->getWrappedColumns($values);

        return sprintf(
            'INSERT INTO %s (%s) VALUES %s ON DUPLICATE KEY UPDATE %s',
            $this->builder->grammar->wrapTable($this->builder->from),
            $wrappedColumns->implode(','),
            $this->generateInsertValues($this->getQuotedValues($values)),
            $this->generateUpdateValues($wrappedColumns)
        );
    }
}

UpsertServiceProvider.php

<?php

namespace App\Providers;

use App\Database\UpsertBuilder;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\ServiceProvider;

class UpsertServiceProvider extends ServiceProvider
{
    public function boot()
    {
        //
    }

    public function register()
    {
        Builder::macro('upsert', function (array $values) {
            /** @var Builder $this */
            $builder = new UpsertBuilder($this);

            return $this->connection->insert($builder->getQuery($values));
        });
    }
}

Usage:

DB::table('posts')->upsert([
    ['title' =>'foo', 'category_id' => 1]
]);

How does it differ from updateOrCreate builder method? 🤔

@mpdx there is no updateOrCreate method for Laravel Query Builder, it exists in Eloquent and also that method is not as atomic as ON DUPLICATE KEY UPDATE