Framework: sync() Executing multiple INSERT queries

[john052]

When syncing an array of ids, "attach" is called for each new record being inserted.
Therefore, an INSERT query is executed for each id being synced.
Would it be better to build up all the records to be inserted and then INSERT them in one query (as attach already does when given an array of ids):

_example: BelongsToMany.php -> attachNew()_

foreach ($records as $id => $attributes)
{
    if ( ! in_array($id, $current))
    {
                // current behavior
        $this->attach($id, $attributes, $touch); 

        // proposed
        $new_attach[$id] = [$attributes];

        $changes['attached'][] = (int) $id;
    }
    elseif (count($attributes) > 0)
    {
        if ($this->updateExistingPivot($id, $attributes, $touch))
        {
            $changes['updated'][] = (int) $id;
        }
    }
}

// attach all new records in one INSERT     
if ( ! empty($new_attach) )
{
    $this->attach($new_attach, $touch);
}

[taylorotwell]

I think this would work if all records have the same "extra" attributes but would fail if they didn't.

[john052]

Ahh... I thought the sync and attach method handled attributes the same way.
i.e For sync you can specify attributes on a per id basis. Would it then be advantageous to have attach behave in the same way?

$user->roles()->attach([1=>['expire'=>true], 2, 3=>['expire'=>false]]);

Or since attach accepts attributes as the second param, something like:

$user->roles()->attach([1,2,3], [ 1=>['expire'=>true], 3=>['expire'=>false] ]);

[john052]

Guess that would simply mean that attach would also have to run multiple queries.

Would it however be worth it for sync to group the synced ids with the same attributes so those groups can be inserted in a single INSERT query?
So something like

  $user->roles()->sync([1=>['expire'=>true], 2, 3=>['expire'=>false]]);

can be done in 2 insert queries, one for id 1 and 3 (with same attributes), one for id 2

This could really help when syncing larger arrays of ids especially if you're not setting attributes since doing something like this:

   $user->roles()->sync([1,2,3,4,5,6,7,8,9,10]);

would potentially execute 10 insert queries when you really only need to execute one.

Even if you're specifying different attributes, you could potentially cut down on a lot of INSERT queries by grouping them.

Umm, last one...
When only updating attributes on already synced ids, it executes an UPDATE query for each record being updated, even though the attribute value might not have changed.
Would it be worth it to read all the records whose attributes are being updated, compare them to the new values, and only execute an UPDATE query for the ones whose values actually change.

[aonurdemir]

Lets ask in another way. If I need a one bulk update or insert, how do I do it in laravel way?