Syncing one-to-many relationships

@paulofreitas - I'm not sure how I should answer this except to say, setup a relationship in your Eloquent model, refer to the documentation/laracasts, and have at it.

To make your life easier and reduce potential spaghetti, I would suggest you have the user create a question before you allow the user to create and associate options for it.

Hi @wells, thanks for your response!

Sorry for the lack of information on the question.

I already have my Eloquent models set up with relationships, scopes, etc. I'm also using all the goodies I learned here on Laracasts, like the Repository pattern and form validation services.

The main issue is about syncing (editing) the related values in an one-to-many relationship, in this case saving poll options changes when editing a given poll question. I believe I'm not doing it the simplest way, which (I think) ends up creating spaghetti code.

I wish it were as simple as it's in many-to-many relationships, where we've sync(), attach() and detach() methods. :P

My dilemma: since I can delete/update/create new options when editing a given poll, currently at the first I'm doing some array diffs to get all created, updated and deleted options ids I've sent through the form. Then I ran one single query to remove deleted options, one query at once to update modified options and one query at once to insert new options.

I guess this is clearly bad and I'm welcome for hints to help me think outside the box. Hope I cleared things up. :)

If all the data about your poll options is sent through the edit form, you could just delete all the original options from the poll and then create the options again. In other words, instead of editing, you just delete and create again. Does that make any sense?

@paulofreitas I also use that approach and would like to have something different, however I never found anything better and the sync method from Laravel's BelongToMany class uses this same approach (calculate diff, delete what isn't there, insert new). If you could post your code I could try suggesting optimizations, eg: you can't run a update for multiple records at the same time, but you can for insert.

@kyrpas it depends on how the table is used, if the id is used in another table, it will break the fk.

@kyrpas That would be perfect if poll options ids aren't used in another table. As @henrique mentioned, that trick would break foreign keys. I created a simple diagram to illustrate those tables relations: database diagram (the users table was intentionally not detailed)

@henrique Fair point. I also never found any better approach than that. I'm a bit worried because these sort of things doesn't happen only once here, so I'm trying to reuse things to make it simple (I mean SOLID). I was unaware of that trick to bulk insert things, thanks for mentioning about! I'll try to optimize things first and then put the code here to see where else I can simplify the code, thanks for now! :)

So I ended up with the following implementation:

PollQuestion model

<?php

namespace App\Models;

use App\Support\Arrays;

class PollQuestion extends AbstractModel
{
    public function syncOptions(array $options, $column = 'option')
    {
        $new_options = array_filter($options);
        $old_options = $this->options->lists($column, 'id');

        // Delete removed options, if any
        if ($deleted = Arrays::keysDeleted($new_options, $old_options)) {
            $this->options()->whereIn('id', $deleted)->delete();
        }

        // Create new options, if any
        if ($created = Arrays::keysCreated($new_options, $old_options)) {
            foreach ($created as $id) {
                $new[] = $this->options()->getModel()->newInstance([
                    $column => $new_options[$id],
                ]);
            }

            $this->options()->saveMany($new);
        }

        // Update changed options, if any
        if ($updated = Arrays::keysUpdated($new_options, $old_options)) {
            foreach ($updated as $id) {
                $this->options()->find($id)->update([
                    $column => $new_options[$id],
                ]);
            }
        }
    }
}

Arrays support class

<?php

namespace App\Support;

use Underscore\Types\Arrays as _Arrays;

class Arrays extends _Arrays
{
    public static function keysCreated($new, $old)
    {
        return array_keys(array_diff_key($new, $old));
    }

    public static function keysDeleted($new, $old)
    {
        return array_keys(array_diff_key($old, $new));
    }

    public static function keysUpdated($new, $old)
    {
        return array_diff(
            array_keys(array_diff_assoc($new, $old)),
            static::keysCreated($new, $old)
        );
    }
}

It became much simpler than it was before.

The code still belongs to this model only, so to achieve that same functionality in another model, I still have to duplicate the code. So I further extended the implementation and moved it to AbstractModel, in a new sync($relationship, $column, $values) method:

AbstractModel

<?php

namespace App\Models;

use App\Support\Arrays;
use Illuminate\Database\Eloquent\Model;

abstract class AbstractModel extends Model implements \JsonSerializable
{
    public function sync($relationship, $column, array $values)
    {
        $new_values = array_filter($values);
        $old_values = $this->$relationship->lists($column, 'id');

        // Delete removed values, if any
        if ($deleted = Arrays::keysDeleted($new_values, $old_values)) {
            $this->$relationship()->whereIn('id', $deleted)->delete();
        }

        // Create new values, if any
        if ($created = Arrays::keysCreated($new_values, $old_values)) {
            foreach ($created as $id) {
                $new[] = $this->$relationship()->getModel()->newInstance([
                    $column => $new_values[$id],
                ]);
            }

            $this->$relationship()->saveMany($new);
        }

        // Update changed values, if any
        if ($updated = Arrays::keysUpdated($new_values, $old_values)) {
            foreach ($updated as $id) {
                $this->$relationship()->find($id)->update([
                    $column => $new_values[$id],
                ]);
            }
        }
    }
}

So I cleaned up my PollQuestion model and now I can use $poll->sync('options', 'option', $options) to sync new options with existing ones. Do you think it looks good? Can it be further improved? I'm welcome to suggestions. :)

It looks pretty good and well organized.

I use something a bit different (not actual code, I don't have the code in any project on this computer, so I just put this up to demonstrate how I do it):

Let's say, for example purposes, that the input is an array of the prices of products with id => price, for a given store (where different stores have different prices and may or may not have the same product).

<?php
$storeId = Input::get('store_id');
$input = Input::get('prices');
/*
array(
    '10' => '99',
    '15' => '211',
);
*/

$prices = Price::findByStoreAndProducts($store_id, array_keys($input)); // scope: where store, whereIn products

$delete = array();
// Update
foreach ($prices as $price) {
    if (isSet($input[$price->product_id])) {
        $price->value = $input[$price->product_id];
        $price->save();
        unset($input[$price->product_id]);
    } else {
        $delete[] = $price->product_id;
    }
}
// Insert
foreach ($input as $product_id => $value) {
    Price::create(array(
        'product_id' => $product_id,
        'value' => $value,
        'store_id' => $storeId,
    ));
    // Or create an array and bulk insert later
}
// Delete
// can use the same scope as in the beginning and then ->delete();
Price::deleteByStoreAndProducts($storeId, $products);

It probably should use Store->products() instead of Price direct, but you get the idea :)

(For more information on bulk insert, check here: https://laracasts.com/forum/?p=649-bulk-insert-update)

Hi @paulofreitas,

Thanks for sharing! I'm looking for something exactly like this. For someone who doesn't (yet!) really understand how it works, could you share a bit more info on how to use it? I assume that $relationship is meant to be the name of the function I have written in the 'one' model that returns the 'many' models. Does $column mean this code only works to update one column (in your case, poll_options.option), and that $value is therefore an unkeyed array of simple values? In my case I'd like to sync a list of playlists to a user. For each playlist there are two columns to sync: playlistId and title. (These are playlist names—the individual songs that comprise each playlist come next :) )

OK, I think I got this working. The main change I made was to make it look up based on playlistId rather than id. To do that, I removed the hard-coded 'id' and added an extra parameter to the sync function to specify the lookup column.

I also had to change the way the $new variable (containing all the new models to be saved) is built up, to stop it nesting the array listing the attributes inside another array: $new[] = $this->$relationship()->getModel()->newInstance($new_values[$id]);

Here's where I landed up with AbstractModel:

<?php

namespace App\Models;

use App\Support\Arrays;
use Illuminate\Database\Eloquent\Model;

abstract class AbstractModel extends Model implements \JsonSerializable
{
    public function sync($relationship, $idcolumn, $column, array $values)
    {
        $new_values = array_filter($values);
        $old_values = $this->$relationship->lists($column, $idcolumn);

        // Delete removed values, if any
        if ($deleted = Arrays::keysDeleted($new_values, $old_values)) {
            $this->$relationship()->whereIn($idcolumn, $deleted)->delete();
        }

        // Create new values, if any
        if ($created = Arrays::keysCreated($new_values, $old_values)) {
            foreach ($created as $id) {
                $new[] = $this->$relationship()->getModel()->newInstance($new_values[$id]);
            }
            
            $this->$relationship()->saveMany($new);
        }

        // Update changed values, if any
        if ($updated = Arrays::keysUpdated($new_values, $old_values)) {
            foreach ($updated as $id) {
                $this->$relationship()->where($idcolumn, $id)->update($new_values[$id]);
            }
        }
    }
}

Old topic, but ok…

Instead of extending Model I think it would be better to extend the relationship class. These types of methods are usually contained in the relationship classes. This would encapsulate thing better too, and only the classes needing to sync would actually have that functionality available.

Solution 1:

• delete all related items
• create every item all over again with saveMany or something

Pros: Really simple. Cons: IDs on existing items are changed.

Solution 2:

• use relation->updateOrCreate() to either update or create existing items
• then delete orphans with delete where key not in {existing keys}

Pros: A lot easier than the code above. Still really simple. Maintains IDs on existing items.

Also: Wrap this in a transaction :)

I've devised a HasManySyncable class that does just this:

use Illuminate\Database\Eloquent\Relations\HasMany;

/**
 * @link https://github.com/laravel/framework/blob/5.4/src/Illuminate/Database/Eloquent/Relations/HasMany.php
 */
class HasManySyncable extends HasMany
{
    public function sync($data, $deleting = true)
    {
        $changes = [
            'created' => [], 'deleted' => [], 'updated' => [],
        ];

        $relatedKeyName = $this->related->getKeyName();

        // First we need to attach any of the associated models that are not currently
        // in the child entity table. We'll spin through the given IDs, checking to see
        // if they exist in the array of current ones, and if not we will insert.
        $current = $this->newQuery()->pluck(
            $relatedKeyName
        )->all();
    
        // Separate the submitted data into "update" and "new"
        $updateRows = [];
        $newRows = [];
        foreach ($data as $row) {
            // We determine "updateable" rows as those whose $relatedKeyName (usually 'id') is set, not empty, and
            // match a related row in the database.
            if (isset($row[$relatedKeyName]) && !empty($row[$relatedKeyName]) && in_array($row[$relatedKeyName], $current)) {
                $id = $row[$relatedKeyName];
                $updateRows[$id] = $row;
            } else {
                $newRows[] = $row;
            }
        }

        // Next, we'll determine the rows in the database that aren't in the "update" list.
        // These rows will be scheduled for deletion.  Again, we determine based on the relatedKeyName (typically 'id').
        $updateIds = array_keys($updateRows);
        $deleteIds = [];
        foreach ($current as $currentId) {
            if (!in_array($currentId, $updateIds)) {
                $deleteIds[] = $currentId;
            }
        }

        // Delete any non-matching rows
        if ($deleting && count($deleteIds) > 0) {
            $this->getRelated()->destroy($deleteIds);

            $changes['deleted'] = $this->castKeys($deleteIds);
        }

        // Update the updatable rows
        foreach ($updateRows as $id => $row) {
            $this->getRelated()->where($relatedKeyName, $id)
                 ->update($row);
        }
        
        $changes['updated'] = $this->castKeys($updateIds);

        // Insert the new rows
        $newIds = [];
        foreach ($newRows as $row) {
            $newModel = $this->create($row);
            $newIds[] = $newModel->$relatedKeyName;
        }

        $changes['created'][] = $this->castKeys($newIds);

        return $changes;
    }


    /**
     * Cast the given keys to integers if they are numeric and string otherwise.
     *
     * @param  array  $keys
     * @return array
     */
    protected function castKeys(array $keys)
    {
        return (array) array_map(function ($v) {
            return $this->castKey($v);
        }, $keys);
    }
    
    /**
     * Cast the given key to an integer if it is numeric.
     *
     * @param  mixed  $key
     * @return mixed
     */
    protected function castKey($key)
    {
        return is_numeric($key) ? (int) $key : (string) $key;
    }
}

You can then override Eloquent's hasMany method in your model class:

    /**
     * Overrides the default Eloquent hasMany relationship to return a HasManySyncable.
     *
     * {@inheritDoc}
     */
    public function hasMany($related, $foreignKey = null, $localKey = null)
    {
        $instance = $this->newRelatedInstance($related);

        $foreignKey = $foreignKey ?: $this->getForeignKey();

        $localKey = $localKey ?: $this->getKeyName();

        return new HasManySyncable(
            $instance->newQuery(), $this, $instance->getTable().'.'.$foreignKey, $localKey
        );
    }

    /**
     * Get all of a user's phone numbers.
     */
    public function phones()
    {
        return $this->hasMany('App\Phone');
    }

A sync method will now be available to any hasMany relationships you have on this model:

$user->phones()->sync([
    [
    'id' => 21,
        'label' => "primary",
        'number' => "5555551212"
    ],
    [
    'id' => null,
        'label' => "mobile",
        'number' => "1112223333"
    ]
]);

Any records in this multidimensional array that don't have an id that matches the child entity table (or have an id of null), will be considered "new" records. Those that do match will be updated. Records in the table that are not present in this array will be removed.

Guys, what about such solution?

// first delete related items
$model->related()->delete();
// save items (each "item" should be a related Model instance)
$model->related()->saveMany($items);

@scofield I'm fairly sure this would cause you to lose timestamp data. Even if that's not relevant, I'd at least wrap it in a transaction.

@scofield What you do when something goes wrong and you delete all related items and you will did not create new version?

Pretty old thread but yet another take on it. I have an Event model (for a Calendar app), that is assigned multiple Dates. Stuck this in my Event model for now. It returns a result similiar to the many-to-many sync().

    /**
     * Get dates assigned to event.
     *
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function dates()
    {
        return $this->hasMany(EventDate::class, 'event_id')->orderBy('date', 'asc');
    }

    /**
     * Sync dates.
     */
    public function datesSync($newValues)
    {
        $oldValues = $this->dates()->get()->keyBy('id');
        $results = [
            'attached' => [],
            'detached' => [],
            'updated' => []
        ];

        foreach($newValues as $value){
            if($value['id'] && $date = $oldValues->get($value['id'])){

                // Update existing.
                $date->fill($value);
                $date->save();
                if($date->getChanges()){
                    $results['updated'][] = $date;
                }
                $oldValues->forget($value['id']);

            } else {

                // Create (attach) new.
                $results['attached'][] = $this->dates()->create($value);

            }
        }

        // Delete (detach) remainingg values in $oldValues.
        foreach($oldValues as $deleted){
            $results['detached'][] = $deleted->delete();
        }

        return $results;
    }