I have a loading script to insert JSON files into a database, this script is executed almost 50 times using AJAX and a loading indicator. I want to use the below structure of insert to get this done as fast as possible to avoid hundreds of single insert statement executed
INSERT INTO TABLE (COLUMN) VALUES ('value1'),('value2'),('value3')
To generate the SQL in Laravel as the example here above, I use this code:
foreach ($album_data['data'] as $album) {
if (isset($album['name'])) {
$array['id'] = $album['id'];
$array['name'] = $album['name'];
$array['type'] = $album['type'];
$arr_albums[] = $array;
unset($array);
}
}
DB::table('ALBUM')->insert($arr_albums);
This works most of the time, but sometimes an error occurs regarding duplicate keys because this script is fired a few times at once for other JSON inputs where the same albums appear, even if I first check for already existing ID's.
This is the process currently:
- get all ID's from the JSON file
- use these ID's in a SELECT to check which are already in the database
- create the INSERT statement for those ID's who are not in the database
The error occurs when 2 or more AJAX requests with same album ID's in the JSON files are checking (number 2 of the process) at the same time, and running (number 3 of the process) insert at the same time.
I try to fix this by adding "ON DUPLICATE KEY UPDATE..." at the end of the generated SQL, so I assumed to do it like this:
$query = DB::table('ALBUM')->insert($arr_albums)->toSql();
$query .= " ON DUPLICATE...";
what causes this error:
Call to a member function toSql() on boolean
I guess the "toSql()" function is not possible on "insert()" ?
I think the easiest way is to use the table object's Grammar:
$table = \DB::table('foo');
$sql = $table->getGrammar()->compileInsert($table, $mapping);
$updateSQL = $table->getGrammar()->compileUpdate($table, $mapping);
\DB::query($sql . ' ON DUPLICATE KEY ' . substr($updateSQL, strpos($updateSQL, ' set ')));
/Illuminate/Database/Query/Grammars/Grammar has functions:
/**
* Compile an insert statement into SQL.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $values
* @return string
*/
public function compileInsert(Builder $query, array $values)
/**
* Compile an update statement into SQL.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $values
* @return string
*/
public function compileUpdate(Builder $query, $values)