Skip to content

Instantly share code, notes, and snippets.

@josh7weaver
Last active April 22, 2023 07:28
Show Gist options
  • Star 8 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save josh7weaver/fd80120810f6b3fc802d to your computer and use it in GitHub Desktop.
Save josh7weaver/fd80120810f6b3fc802d to your computer and use it in GitHub Desktop.
insert on dup key update BULK
<?php namespace [YOUR NAMESPACE HERE];
use Illuminate\Database\Eloquent\Model;
use Log;
use DB;
class MysqlBuilder {
protected $table;
protected $pdoPlaceholderLimit;
protected $columnString;
protected $updateString;
public function __construct($table, $pdoPlaceholderLimit = '60000')
{
$this->table = $table;
$this->pdoPlaceholderLimit = $pdoPlaceholderLimit;
}
public function chunk(array $valueCollection)
{
$valueList = head($valueCollection);
return array_chunk($valueCollection, $this->getMaxChunkSize($valueList), true);
}
/**
* Must call this before calling insertOrUpdate
* @param array $columns
*/
public function setColumns(array $columns)
{
$this->columnString = $this->buildColumnString($columns);
$this->updateString = $this->buildUpdateString($columns);
}
/**
* Execute the INSERT ON DUPLICATE KEY UPDATE mysql statement
* @param array $valueChunk
* @return int
*/
public function insertOrUpdate(array $valueChunk)
{
$valuePlaceholders = $this->buildValuePlaceholderString($valueChunk);
$bindings = array_flatten($valueChunk);
$query = "INSERT INTO {$this->table} ({$this->columnString}) VALUES $valuePlaceholders ON DUPLICATE KEY UPDATE {$this->updateString}";
return DB::affectingStatement($query, $bindings);
}
/**
* @return string
*/
protected function buildColumnString(array $columns)
{
return '`' . implode('`,`', $columns) . '`';
}
/**
* @return string
*/
protected function buildUpdateString(array $columns)
{
$updates = '';
foreach($columns as $column){
$updates .= "`$column`=VALUES(`$column`),";
}
return rtrim($updates, ',');
}
/**
* @param array $valueCollection a chunk of values to build placeholders for
* @return string
*/
protected function buildValuePlaceholderString(array $valueCollection)
{
$placeholder = '';
foreach ($valueCollection as $attributes) {
$placeholder .= '(' . str_repeat("TRIM(?),", count($attributes));
$placeholder = rtrim($placeholder, ',');
$placeholder .= '),';
}
return rtrim($placeholder, ',');
}
protected function getMaxChunkSize(array $valueList)
{
// round the number down by casting to an int
return (int) ($this->pdoPlaceholderLimit / count($valueList));
}
}
@sergeyklay
Copy link

<?php

namespace App\Traits;

use DB;
use Log;
use BadMethodCallException;
use Illuminate\Database\Eloquent\Model;

trait QueryBuilder
{
    protected static $columnString;
    protected static $updateString;

    /**
     * @var Model
     */
    protected static $model;

    /**
     * Execute the INSERT ON DUPLICATE KEY UPDATE mysql statement
     *
     * <code>
     * Model::insertOrUpdate(
     *     [
     *         'field1'     => 1,
     *         'field2'     => 'value',
     *         'created_at' => 'SQL:NOW()',
     *     ],
     *     [
     *         'field2'     => 'value',
     *         'updated_at' => 'SQL:NOW()',
     *     ]
     * );
     * </code>
     *
     * @param array $insert INSERT key => value pairs
     * @param array $update UPDATE key => value pairs
     * @return int Affected rows
     */
    public static function insertOrUpdate(array $insert, array $update)
    {
        if (empty($insert) || empty($update)) {
            return 0;
        }

        self::initModel();

        $insertKeys = [];
        $updateKeys = [];
        $bindings   = [];

        foreach ($insert as $k => $v) {
            if (strpos($v, 'SQL:') === 0) {
                $insertKeys[] = "`{$k}` = " . trim(str_replace('SQL:', '', $v));
            } else {
                $insertKeys[] = "`{$k}` = :i_{$k}";
                $bindings[":i_{$k}"] = $v;
            }
        }

        foreach ($update as $k => $v) {
            if (strpos($v, 'SQL:') === 0) {
                $updateKeys[] = "`{$k}` = " . trim(str_replace('SQL:', '', $v));
            } else {
                $updateKeys[] = "`{$k}` = :u_{$k}";
                $bindings[":u_{$k}"] = $v;
            }
        }

        $sql = strtr(
            /** @lang text */
            'INSERT INTO `:table` SET :insert ON DUPLICATE KEY UPDATE :update',
            [
                ':table'  => self::$model->getTable(),
                ':insert' => implode(', ', $insertKeys),
                ':update' => implode(', ', $updateKeys)
            ]
        );

        Log::debug(DB::raw($sql), $bindings);

        return DB::affectingStatement(DB::raw($sql), $bindings);
    }

    protected static function initModel()
    {
        if (!self::$model) {
            $modelClass = get_called_class();

            /** @var Model $model */
            $model = new $modelClass;

            if (!$model instanceof Model) {
                throw new BadMethodCallException(
                    sprintf(
                        'It is unacceptable to use %s for objects which do not implement %s',
                        __METHOD__,
                        'Illuminate\Database\Eloquent\Model'
                    )
                );
            }

            self::$model = $model;
        }
    }
}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment