How to force laravel to perform batch (multiple) insertion in the sync () method?

Question

When I use the sync() method, larvel makes many separate insertion requests in my staging table as follows:

 INSERT INTO `tag_user` (`user_id`, `tag_id`) VALUES ('59', '60') INSERT INTO `tag_user` (`user_id`, `tag_id`) VALUES ('59', '61') 

I want him to do one multiple insert as follows:

 INSERT INTO `tag_user` (`user_id`, `tag_id`) VALUES ('59', '60'), ('59', '61') 

Is it possible? I am using MySql. It would be nice to have an attach() method that will take an array as a detach() method. Has anyone done this?

Answer 1

Here is how I solved it:

My models

In my application, each user has many tags (from many to many rules). He called toxi . My user table is called "users", the tag table is called "tags". And the staging table is called tag_user, which has the columns tag_id and user_id.

User Model:

 class User extends \Eloquent { public static $timestamps = false; public function tags() { return $this->has_many_and_belongs_to('Models\Tag'); } } 

Tag Model:

 class Tag extends \Eloquent { public static $timestamps = false; } 

How I replaced the sync() method

Here's how I got laravel to make a sync() method using a few plugins:

 //$currentUser is a model loaded from database //Like this: $currentUser = Auth::user(); $newLinks = array(); $idsToSync = array(); foreach ($tags as $tag) { array_push($idsToSync, $tag->id); } //$currentUser->tags()->sync($idsToSync); $currentIds = $currentUser->tags()->pivot()->lists('tag_id'); $idsToAttach = array_diff($idsToSync, $currentIds); foreach ($idsToAttach as $value) { $newLink = array( 'user_id' => $currentUser->id, 'tag_id' => $value ); $newLinks[] = $newLink; } if (count($newLinks) > 0) { \DB::table('tag_user')->insert($newLinks); } $idsToDetach = array_diff($currentIds, $idsToSync); if (count($idsToDetach) > 0) { $currentUser->tags()->detach($idsToDetach); } 

This code does one multiple insertion instead of many separate ones.

Answer 2

I, I worked on the same thing a few days ago,

eloquent makes several attachments in one sql.

but make sure that all loops are equal to columns and fields, I tried to delete the one that doesn't matter the first time, and mysql doesn't work ...

eg:

 array( array('name' => 'blah'), array('name' => 'blah') ) User::insert($data) 

but if you want to update existing records, you need to execute a raw query.

eg:

 $keyString = '("'; $valString = '("'; foreach ($blah as $k => $v) { $keyString .= $k . '", ' } 

The goal is to return something like this

 $keyString // (name, email, bla, bla) $valString // ('john doe', ' [e-mail] ', 'bla', 'bla'), ('someone', ' [e-mail] ', 'bla', 'bla'), 

than

 DB::query( 'replace into users' . $keyString . ' values ' . $valString ); 

make sure you use array counting to check if the last array is a comma or not

eg:

 (++counter === count ? '),' : ')' ; 

this need to reorganize

Answer 3

A workaround would be to use Laravel DB::transaction() and enable synchronization in it as a close.