-
-
Save VinceG/0fb570925748ab35bc53f2a798cb517c to your computer and use it in GitHub Desktop.
<?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); | |
} | |
} |
<?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() | |
); | |
} | |
} |
<?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') | |
]); |
<?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; | |
} | |
} |
<?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"; | |
} | |
} |
Hi @plante-david55,
Assuming you know how to create the above files in the correct directory the missing part of the puzzle is to create a service provider for to override the default MySqlConnection.
Something like this:
<?php namespace App\Providers;
use App\Library\Database\MySqlConnection;
use Illuminate\Support\ServiceProvider;
class DatabaseServiceProvider extends ServiceProvider
{
/**
* Register the modified database connection
*
* @return void
*/
public function register()
{
$this->app->singleton('db.connection.mysql', function ($app, $parameters) {
// get the parameters
list($connection, $database, $prefix, $config) = $parameters;
// now we create an instance of our mysql connection
return new MySqlConnection($connection, $database, $prefix, $config);
});
}
}
Then register the provider in config/app.php.
'providers' =>[
...
App\Providers\DatabaseServiceProvider::class,
],
And thanks @VinceG!
This looks great! Perfect for bulk imports. I followed @ianchadwick's instructions, but I'm stuck at this error:
In Builder.php line 2526:
Method Illuminate\Database\Query\Builder::replace does not exist.
I've taken a look at the following articles:
https://stidges.com/extending-the-connection-class-in-laravel
https://medium.com/@justin.park001/laravel-techniques-extending-the-query-builder-2f4aca7956a2
...but I just can't figure out a clean way to get it to use the new Builder
class.
Could someone prod me in the right direction?
Spoke too soon, just got it working! I followed @SETIexplorer's advice in the comments of the first link.
First, the custom DatabaseServiceProvider
must be loaded before Laravel's DatabaseServiceProvider
:
'providers' => [
App\Providers\DatabaseServiceProvider::class,
...
Illuminate\Database\DatabaseServiceProvider::class,
],
Here's my DatabaseServiceProvider
class:
<?php
namespace App\Providers;
use App\Library\Database\MySqlConnection;
use Illuminate\Database\Connection;
use Illuminate\Support\ServiceProvider;
class DatabaseServiceProvider extends ServiceProvider
{
/**
* Override the default connection for MySQL. This allows us to use `replace` etc.
*
* @link https://stidges.com/extending-the-connection-class-in-laravel
* @link https://gist.github.com/VinceG/0fb570925748ab35bc53f2a798cb517c
*
* @return void
*/
public function boot()
{
Connection::resolverFor('mysql', function($connection, $database, $prefix, $config) {
return new MySqlConnection($connection, $database, $prefix, $config);
});
}
}
I then removed Connection
, moved its query
method to MySqlConnection
, and slimmed it down a bit:
<?php
namespace App\Library\Database;
use App\Library\Database\Query\Builder;
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 \App\Library\Database\Query\Grammars\MySqlGrammar
*/
protected function getDefaultQueryGrammar()
{
return $this->withTablePrefix(new QueryGrammar);
}
/**
* Get a new query builder instance.
*
* @return \App\Library\Database\Query\Builder
*/
public function query()
{
return new Builder(
$this, $this->getQueryGrammar(), $this->getPostProcessor()
);
}
}
Seems to be working great! I guess semantically, Builder
should be renamed to MySqlBuilder
, since that's what it is now.
Is there ever a situation where the new Builder
methods would be used outside MySQL context? Do any other database engines implement the same methods? I'm not sure if my adjustments are the way to go in that case.
I don't know if this is the best solution, so please do let me know if there is a cleaner way.
The insertUpdate
command here is malformed. I fixed it for our project. See commit for details:
art-institute-of-chicago/data-service-images@0802b89
Requires a couple minor changes in Builder::insertUpdate()
, and a rewrite of MySqlGrammar::compileInsertUpdate()
.
Resources:
@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:
<?php
namespace App\Providers;
use App\Library\Database\MySqlConnection;
use Illuminate\Database\Connection;
use Illuminate\Support\ServiceProvider;
class DatabaseServiceProvider extends ServiceProvider
{
/**
* Override the default connection for MySQL. This allows us to use `replace` etc.
*
* @link https://stidges.com/extending-the-connection-class-in-laravel
* @link https://gist.github.com/VinceG/0fb570925748ab35bc53f2a798cb517c
*
* @return void
*/
public function boot()
{
Connection::resolverFor('mysql', function ($connection, $database, $prefix, $config) {
return new MySqlConnection($connection, $database, $prefix, $config);
});
}
/**
* Register the service provider.
*
* @return void
*/
public function register()
{
// Or this way
// $this->app->singleton('db.connection.mysql', function ($app, $parameters) {
// list($connection, $database, $prefix, $config) = $parameters;
// return new MySqlConnection($connection, $database, $prefix, $config);
// });
// Or this way
// $this->app->bind('db.connection.mysql', \App\Library\Database\MySqlConnection::class);
}
}
'providers' => [
App\Providers\DatabaseServiceProvider::class,
...
Illuminate\Database\DatabaseServiceProvider::class,
],
// or
// 'providers' => [
// Illuminate\Database\DatabaseServiceProvider::class,
// ...
// App\Providers\DatabaseServiceProvider::class,
// ],
@IllyaMoskvin It is my mistake, i am using lumen 5.4 not laravel 5.2. I should register provider in bootstrap/app.php
:
<?php
...
$app->register(App\Providers\DatabaseServiceProvider::class);
return $app;
Now it working!
Hey !
Seems really nice. I don't know if i'm dumb or i simply lack experience but i have no idea how to implement your code into laravel framework.
Could you help me ? I REALLY need this feature ! :)