Created
October 19, 2016 14:16
-
-
Save VinceG/0fb570925748ab35bc53f2a798cb517c to your computer and use it in GitHub Desktop.
Laravel support for replace into / insert ignore / insert on duplicate key update
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
namespace App\Library\Database\Query; | |
use Illuminate\Database\Query\Builder as QueryBuilder; | |
class Builder extends QueryBuilder | |
{ | |
/** | |
* Insert a new record into the database. | |
* | |
* @param array $values | |
* @return bool | |
*/ | |
public function replace(array $values) | |
{ | |
if (empty($values)) { | |
return true; | |
} | |
// Since every insert gets treated like a batch insert, we will make sure the | |
// bindings are structured in a way that is convenient for building these | |
// inserts statements by verifying the elements are actually an array. | |
if (! is_array(reset($values))) { | |
$values = [$values]; | |
} | |
// Since every insert gets treated like a batch insert, we will make sure the | |
// bindings are structured in a way that is convenient for building these | |
// inserts statements by verifying the elements are actually an array. | |
else { | |
foreach ($values as $key => $value) { | |
ksort($value); | |
$values[$key] = $value; | |
} | |
} | |
// We'll treat every insert like a batch insert so we can easily insert each | |
// of the records into the database consistently. This will make it much | |
// easier on the grammars to just handle one type of record insertion. | |
$bindings = []; | |
foreach ($values as $record) { | |
foreach ($record as $value) { | |
$bindings[] = $value; | |
} | |
} | |
$sql = $this->grammar->compileReplace($this, $values); | |
// Once we have compiled the insert statement's SQL we can execute it on the | |
// connection and return a result as a boolean success indicator as that | |
// is the same type of result returned by the raw connection instance. | |
$bindings = $this->cleanBindings($bindings); | |
return $this->connection->insert($sql, $bindings); | |
} | |
/** | |
* Insert a new record into the database. | |
* | |
* @param array $values | |
* @return bool | |
*/ | |
public function insertUpdate(array $values) | |
{ | |
if (empty($values)) { | |
return true; | |
} | |
// Since every insert gets treated like a batch insert, we will make sure the | |
// bindings are structured in a way that is convenient for building these | |
// inserts statements by verifying the elements are actually an array. | |
if (! is_array(reset($values))) { | |
$values = [$values]; | |
} | |
// Since every insert gets treated like a batch insert, we will make sure the | |
// bindings are structured in a way that is convenient for building these | |
// inserts statements by verifying the elements are actually an array. | |
else { | |
foreach ($values as $key => $value) { | |
ksort($value); | |
$values[$key] = $value; | |
} | |
} | |
// We'll treat every insert like a batch insert so we can easily insert each | |
// of the records into the database consistently. This will make it much | |
// easier on the grammars to just handle one type of record insertion. | |
$bindings = []; | |
foreach ($values as $record) { | |
foreach ($record as $value) { | |
$bindings[] = $value; | |
} | |
} | |
$sql = $this->grammar->compileInsertUpdate($this, $values); | |
// Once we have compiled the insert statement's SQL we can execute it on the | |
// connection and return a result as a boolean success indicator as that | |
// is the same type of result returned by the raw connection instance. | |
$bindings = $this->cleanBindings($bindings); | |
return $this->connection->insert($sql, array_merge($bindings, $bindings)); | |
} | |
/** | |
* Insert a new record into the database. | |
* | |
* @param array $values | |
* @return bool | |
*/ | |
public function insertIgnore(array $values) | |
{ | |
if (empty($values)) { | |
return true; | |
} | |
// Since every insert gets treated like a batch insert, we will make sure the | |
// bindings are structured in a way that is convenient for building these | |
// inserts statements by verifying the elements are actually an array. | |
if (! is_array(reset($values))) { | |
$values = [$values]; | |
} | |
// Since every insert gets treated like a batch insert, we will make sure the | |
// bindings are structured in a way that is convenient for building these | |
// inserts statements by verifying the elements are actually an array. | |
else { | |
foreach ($values as $key => $value) { | |
ksort($value); | |
$values[$key] = $value; | |
} | |
} | |
// We'll treat every insert like a batch insert so we can easily insert each | |
// of the records into the database consistently. This will make it much | |
// easier on the grammars to just handle one type of record insertion. | |
$bindings = []; | |
foreach ($values as $record) { | |
foreach ($record as $value) { | |
$bindings[] = $value; | |
} | |
} | |
$sql = $this->grammar->compileInsertIgnore($this, $values); | |
// Once we have compiled the insert statement's SQL we can execute it on the | |
// connection and return a result as a boolean success indicator as that | |
// is the same type of result returned by the raw connection instance. | |
$bindings = $this->cleanBindings($bindings); | |
return $this->connection->insert($sql, $bindings); | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
namespace App\Library\Database; | |
use Illuminate\Database\Connection as BaseConnection; | |
use App\Library\Database\Query\Builder; | |
class Connection extends BaseConnection | |
{ | |
/** | |
* Get a new query builder instance. | |
* | |
* @return \Illuminate\Database\Query\Builder | |
*/ | |
public function query() | |
{ | |
return new Builder( | |
$this, $this->getQueryGrammar(), $this->getPostProcessor() | |
); | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
DB::table('table')->replace([ | |
'dashboard_id' => $this->model->id, | |
'date' => Carbon::now()->format('Y-m-d'), | |
'views' => DB::raw('views + 1') | |
]); | |
DB::table('table')->insertIgnore([ | |
'dashboard_id' => $this->model->id, | |
'date' => Carbon::now()->format('Y-m-d'), | |
'views' => DB::raw('views + 1') | |
]); | |
DB::table('table')->insertUpdate([ | |
'dashboard_id' => $this->model->id, | |
'date' => Carbon::now()->format('Y-m-d'), | |
'views' => DB::raw('views + 1') | |
]); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
namespace App\Library\Database; | |
use Illuminate\Database\Query\Processors\MySqlProcessor; | |
use App\Library\Database\Query\Grammars\MySqlGrammar as QueryGrammar; | |
use Illuminate\Database\MySqlConnection as Connection; | |
class MySqlConnection extends Connection | |
{ | |
/** | |
* Get the default query grammar instance. | |
* | |
* @return \Illuminate\Database\Query\Grammars\MySqlGrammar | |
*/ | |
protected function getDefaultQueryGrammar() | |
{ | |
return $this->withTablePrefix(new QueryGrammar); | |
} | |
/** | |
* Get the default post processor instance. | |
* | |
* @return \Illuminate\Database\Query\Processors\MySqlProcessor | |
*/ | |
protected function getDefaultPostProcessor() | |
{ | |
return new MySqlProcessor; | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
namespace App\Library\Database\Query\Grammars; | |
use Illuminate\Database\Query\Grammars\MySqlGrammar as Grammar; | |
use App\Library\Database\Query\Builder; | |
class MySqlGrammar extends Grammar | |
{ | |
/** | |
* Compile an replace into statement into SQL. | |
* | |
* @param \Illuminate\Database\Query\Builder $query | |
* @param array $values | |
* @return string | |
*/ | |
public function compileReplace(Builder $query, array $values) | |
{ | |
// Essentially we will force every insert to be treated as a batch insert which | |
// simply makes creating the SQL easier for us since we can utilize the same | |
// basic routine regardless of an amount of records given to us to insert. | |
$table = $this->wrapTable($query->from); | |
if (! is_array(reset($values))) { | |
$values = [$values]; | |
} | |
$columns = $this->columnize(array_keys(reset($values))); | |
// We need to build a list of parameter place-holders of values that are bound | |
// to the query. Each insert should have the exact same amount of parameter | |
// bindings so we will loop through the record and parameterize them all. | |
$parameters = []; | |
foreach ($values as $record) { | |
$parameters[] = '('.$this->parameterize($record).')'; | |
} | |
$parameters = implode(', ', $parameters); | |
return "replace into $table ($columns) values $parameters"; | |
} | |
/** | |
* Compile an insert ignore statement into SQL. | |
* | |
* @param \Illuminate\Database\Query\Builder $query | |
* @param array $values | |
* @return string | |
*/ | |
public function compileInsertIgnore(Builder $query, array $values) | |
{ | |
// Essentially we will force every insert to be treated as a batch insert which | |
// simply makes creating the SQL easier for us since we can utilize the same | |
// basic routine regardless of an amount of records given to us to insert. | |
$table = $this->wrapTable($query->from); | |
if (! is_array(reset($values))) { | |
$values = [$values]; | |
} | |
$columns = $this->columnize(array_keys(reset($values))); | |
// We need to build a list of parameter place-holders of values that are bound | |
// to the query. Each insert should have the exact same amount of parameter | |
// bindings so we will loop through the record and parameterize them all. | |
$parameters = []; | |
foreach ($values as $record) { | |
$parameters[] = '('.$this->parameterize($record).')'; | |
} | |
$parameters = implode(', ', $parameters); | |
return "insert ignore into $table ($columns) values $parameters"; | |
} | |
/** | |
* Compile an insert ignore statement into SQL. | |
* | |
* @param \Illuminate\Database\Query\Builder $query | |
* @param array $values | |
* @return string | |
*/ | |
public function compileInsertUpdate(Builder $query, array $values) | |
{ | |
// Essentially we will force every insert to be treated as a batch insert which | |
// simply makes creating the SQL easier for us since we can utilize the same | |
// basic routine regardless of an amount of records given to us to insert. | |
$table = $this->wrapTable($query->from); | |
// Each one of the columns in the update statements needs to be wrapped in the | |
// keyword identifiers, also a place-holder needs to be created for each of | |
// the values in the list of bindings so we can make the sets statements. | |
$columns = []; | |
$values = reset($values); | |
foreach ($values as $key => $value) { | |
$columns[] = $this->wrap($key).' = '.$this->parameter($value); | |
} | |
$columns = implode(', ', $columns); | |
return "insert into $table set $columns ON DUPLICATE KEY UPDATE $columns"; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
@IllyaMoskvin thx for your comment! I followed your way but it still use
Illuminate\Database\Query\Builder
anyway.My laravel version is 5.2, and
App\Library\Database\
is 100% same as your project [0].I tried these way:
@IllyaMoskvin It is my mistake, i am using lumen 5.4 not laravel 5.2. I should register provider in
bootstrap/app.php
:Now it working!