How can i force laravel to do batch(multiple) insert in sync() method?

Question

When i use sync() method laravel do a lot of separate insert queries in my intermediate table like this:

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

I want it to do one multiple insert like this:

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

Is it possible? I'm using MySql. It would be nice to have attach() method that will accept array as detach() method do. Did someone do this?

Answer 1

This is how i solved it:

My models

In my application each user has many tags(many to many realationship). It's called toxi database schema. My user table is called 'users', tags table is called 'tags'. And intermediate table is called 'tag_user' that has 'tag_id' and 'user_id' columns.

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 sync() method

This is how i forced laravel to do sync() method using multiple insert:

//$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 insert instead of many single ones.

Answer 2

Ya, i was working on the same thing few days back,

eloquent does multiple insert in one sql.

but make sure all loops are equal columns and fields, i was trying to remove the one that does not have value the first time, and mysql wont work...

eg:

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

but if you want to update existing records than you need to do 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', '[email protected]', 'bla', 'bla'), ('someone', '[email protected]', 'bla', 'bla'), 

than

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

make sure you use array count to do a check whether is the last array for comma or not

eg:

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

this need to refactored