A Laravel service to replace an existing dataset with a new dataset, syncing new, updated, and deleted items.

ModelSyncManager.md

Model Sync Manager

A Laravel service for replacing an existing dataset with a new dataset - updating, inserting & deleting records as efficiently as possible to transform the existing database state into the new desired state. This works for any adhoc set of records, or on a HasMany relationship.

A common scenario with saving or syncing data is the need to replace an existing set of records with an updated set - which may include new or removed items.

Laravel provides a sync method for many-to-many relationships which is a similar concept but only replaces the relationships - requiring you to know the IDs of the records you want to attach to, and not impacting the underlying data. It also only works on the relationships of a single parent at a time, whereas we may want to sync records for a large group of parents in one go.

Laravel also provides an upsert method which is a fast and efficient way to mass update data - inserting or updating as required. However this method still does not remove any existing records that are missing form the new dataset. If using mass updates with this service, it will use upsert underneath the hood, but also remove any records missing in the new dataset. Note: for upsert to work you must have a unique index on the uniqueBy columns.

Why not just delete & insert?

On way this situation is often approached is to just delete all records from the dataset & re-insert. In some situations this may be acceptable. However it often isn't appropriate:

• The records being deleted may have other relationships which will then break given the new inserts will likely have diffrerent IDs. Foreign key relationships may prevent the records from being deleted, or result in unintended cascading deleting or nulling.

• The records being deleted may have additional columns with useful data that is being lost. Creation dates, user stamps, or any other data will be lost.

• Less important, but if this is happening regularly on larger datasets and you are using incrementing IDs you will end up with massive ID's. This isn't a killer, but can make the database a bit messier & harder to view when dealing with large integers.

Examples

Some common examples where this service is useful include:

• Syncing pricing override entries for a page of products in an integration project. If an override disappears it should be removed. Otherwise inserted or updated as required
• A UI form submission where there is a parent records, along with multiple related children records. E.g. an order with its order items. Or a product with its pricing entries. On save, you want to save the parent & then 'sync' the children to ensure the updated dataset reflects what was on the form.

Usage

There are 3 main methods you can use to perform a sync:

massSync

This method takes 2 datasets & essentially performs an upsert - but also identifies data that needs to be removed from the provided existing dataset.

resolve(ModelSyncManager::class)
    ->forModel(Prices::class)
    ->ifChanged('price')
    ->uniqueBy('price_tier_id', 'product_id')
    ->massSync($products->flatMap->prices, $updatedPricesForProducts);

This call says: mass update the prices associated with any of the model instances in $products to instead look like the updated prices (which will often be an array/Collection of array data). It will only update records if the value for the price column has changed, and will use the price_tier_id along with the product_id to identify a unique record.

Because this is using upsert you must have a unique index across the fields supplied to uniqueBy.

sync

This is similar to massSync, but will return the new & updated models. It compares an existing dataset vs a target updated state for that dataset:

$records = resolve(ModelSyncManager::class)
    ->forModel(Prices::class)
    ->uniqueBy(['price_tier_id', 'product_id']) // array or individual parameters
    ->sync($products->flatMap->prices, $updatedPricesForProducts);

It will perform inserts, updates & deletes as necessary, and return the updated collection of models (in the order of the input data - $updatedPricesForProducts in the example above).

This method is useful for a sync across multiple parents where you want the new/updated model instances. It is not as efficient as massSync, but provides you the updated models.

syncRelated

If you would like to replace the records related to a parent with another dataset, you should use this method. This method is quite useful in the UI submission example above or if you are syncing a single record with its children.

$records = resolve(ModelSyncManager::class)
    ->uniqueBy('price_tier_id') // unique within a product
    ->ifChanged('price') // only update if the price has changed - array or individual parameters
    ->syncRelated($product, 'prices', $updatedPricesForProduct);

This says: sync any price entries related to $product using the prices relationship method - replacing them with the provided updated pricing data - but only if the value of price has changed.

Because we are using the relationship:

1. you do not need to specify a model class using forModel
2. your call to uniqueBy does not need to specify the foreign key associated with that relationship - product_id in this case.

Syncing by primary key

Sometimes your updated data contains primary key values and items to insert can be identified as entries with no primary key specified.

In this scenario you do not need to specify uniqueBy columns and the service will simply use the primary key to determine if it should insert, update or delete a record.

An example might be an order form where the form data submits item id's for each existing record:

$records = resolve(ModelSyncManager::class)
    ->syncRelated($order, 'items', $updatedOrderItems);

Configuration method

forModel

Specify the model class the sync is for. Required unless calling syncRelated.

uniqueBy

Required. Set which combination of values will uniquely identify a record.

Receives either an array or multiple colums as separate parameters.

ifChanged

Optional - specify 1 or more columns (either as an array or separate parameters) to check to determine if a record has changed. If existing record has the same values for these columns as the new data, the record will be ignored.

force

By default any deleted records will be soft deleted if the model uses it. By specifying ->force() you can override this to force the record to completely delete from the database.

ModelSyncManager.php

<?php

namespace App\Services;

use Closure;
use ArrayAccess;
use Illuminate\Support\Collection;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\Relation;

/**
 * Replaces an existing dataset with a new dataset, syncing new, updated, and deleted items
 *
 * Can be used to sync related records, or a standalone dataset.
 *
 * @usage
 * // mass sync prices for a page of products
 * $records = resolve(ModelSyncManager::class)
 *     ->forModel(Prices::class)
 *     ->uniqueBy(['price_tier_id', 'product_id']) // array or individual parameters
 *     ->sync($products->flatMap->prices, $updatedPricesForProducts);
 *
 * // sync prices for a single product, only updating records if the price column has changed
 * $records = resolve(ModelSyncManager::class)
 *     ->uniqueBy('price_tier_id') // unique within a product
 *     ->ifChanged('price') // only update if the price has changed - array or individual parameters
 *     ->syncRelated($product, 'prices', $updatedPricesForProduct);
 *
 * // no need to specify unique columns if just using the primary key
 * $records = resolve(ModelSyncManager::class)
 *     ->syncRelated($order, 'items', $updatedOrderItems);
 *
 * // force delete items that would otherwise soft delete
 * $records = resolve(ModelSyncManager::class)
 *     ->force()
 *     ->uniqueBy('price_tier_id')
 *     ->syncRelated($product, 'prices', $updatedPricesForProduct);
 *
 * // if potentially inserting a large amount of records, and you don't care about
 * // receiving new recordset back as model instances, you can use the `massSync`
 * // method which is more efficient for large inserts - using `insert($allInserts)`
 * // to insert all records with a single SQL query.
 * resolve(ModelSyncManager::class)
 *   ->forModel(Prices::class)
 *   ->ifChanged('price')
 *   ->uniqueBy('price_tier_id', 'product_id')
 *   ->massSync($products->flatMap->prices, $updatedPricesForProducts);
 */
class ModelSyncManager
{
    /**
     * Used for unique key generation
     */
    protected static int $keyCounter = 0;

    /**
     * Model class to sync to
     */
    protected string $modelClass;

    /**
     * Columns to check for changes before updating
     */
    protected array $ifChanged;

    /**
     * Columns to use to uniquely identify a record
     */
    protected array $uniqueBy;

    /**
     * Should we force delete records?
     */
    protected bool $force = false;

    /**
     * Cached primary key for this model
     */
    protected string $primaryKey;

    /**
     * Options model class are we syncing
     */
    public function forModel(string $modelClass): static
    {
        $this->modelClass = $modelClass;
        return $this;
    }

    /**
     * Set the columns to check for changes before updating
     *
     * @param string|array $ifChanged,... columns to check for changes before updating
     * @return $this
     */
    public function ifChanged(string|array ...$ifChanged): static
    {
        $this->ifChanged = collect($ifChanged)->flatten()->all();
        return $this;
    }

    /**
     * Set the uniqueBy columns.
     *
     * @param string|array $uniqueBy,... columns to use to uniquely identify a record
     * @return $this
     */
    public function uniqueBy(array|string ...$uniqueBy): static
    {
        $this->uniqueBy = collect($uniqueBy)->flatten()->all();
        return $this;
    }

    /**
     * Force delete records that would otherwise be soft deleted
     */
    public function force(): static
    {
        $this->force = true;
        return $this;
    }

    /**
     * Sync a new set of items comparing against an existing dataset and:
     *   1. inserting new items for new items that don't exist in the existing dataset
     *   2. updating existing items that have changed
     *   3. deleting items that no longer exist in the new dataset
     *
     * To identify a match the `uniqueBy` columns are compared in the existing
     * dataset vs the new dataset.
     *
     * Assumes each record has an `id` as a primary key
     *
     * @param iterable $existing containing model instances of the existing items
     * @param iterable $new containing array data of the new items to sync
     * @return Collection the updated dataset as model instances
     */
    public function sync(iterable $existing, iterable $new): Collection
    {
        $this->modelClass(collect($existing)->first());

        return $this->execute($existing, $new);
    }

    /**
     * Sync related items - identifying new, updated, and deleted items
     *
     * @param Model $parent The parent record
     * @param string $relation The relation to sync
     * @param iterable $items The updated item data to sync
     * @return Collection the updated dataset as model instances
     */
    public function syncRelated(Model $parent, string $relation, iterable $new): Collection
    {
        $existing = $parent->$relation;

        $this->modelClass($parent->$relation()->getModel());

        return $this->execute($existing, $new, relationship: $parent->$relation());
    }

    /**
     * Sync a large amount of records efficiently
     *
     * This method is more efficient for large inserts, using `insert($allInserts)`
     * to insert all records with a single SQL query.
     *
     * @param iterable $existing containing model instances of the existing items
     * @param iterable $new containing array data of the new items to sync
     * @param array|null $updateColumns (optional) columns to update when upserting
     *     if the record already exists.
     * @return void
     */
    public function massSync(iterable $existing, iterable $new, array $updateColumns = null): void
    {
        $new = $this->keyByUnique($new);
        $existing = $this->keyByUnique($existing);

        $this->delete(
            $existing->filter(fn ($item, $key) => !$new->has($key))
        );

        if (!empty($this->ifChanged)) {
            // remove any items that exist but haven't changed
            $new = $new->filter(
                fn ($item, $key) => !$existing->has($key) || $this->hasChanged($existing[$key], $item)
            );
        }

        $model = $this->modelClass($existing->first());
        $model::upsert($new->values()->all(), $this->uniqueBy, $updateColumns);
    }

    /**
     * Execute the syncronization - inserting, updating & deleting as required
     *
     * @param iterable $existing containing model instances of the existing items
     * @param iterable $new containing array data of the new items to sync
     * @param Relationship|null $relationship (default null) if set will insert new
     *     records against the given relationship
     * @return Collection|null model instances for the updated data (or null if
     *     operation doesn't expect a return value)
     */
    protected function execute(
        iterable $existing,
        iterable $new,
        ?Relation $relationship = null,
    ): ?Collection {
        // key new & existing by a unique key for each
        $new = $this->keyByUnique($new);
        $existing = $this->keyByUnique($existing);

        $operations = $existing->groupBy(
            fn ($item, $key) => $new->has($key) ? 'update' : 'delete',
            preserveKeys: true
        );

        [$updated, $inserts] = $this->update($operations->get('update'), $new);
        $inserted = $this->insert($inserts, $relationship);

        $this->delete($operations->get('delete'));

        // merge the (keyed) update & inserted records over the 'new' dataset so
        // we get them in the correct order
        return $new->merge($updated)
            ->merge($inserted)
            ->values();
    }

    /**
     * Update existing records
     *
     * @param Collection|null $existing The existing data to update keyed by its unique key
     * @param Collection $new The new data to update keyed by its unique key
     * @return array tuple containing keyed collections of updated model instances, and a
     *     keyed collection of new data that didn't pre-exist and therefore should be inserted
     */
    protected function update(?Collection $existing, Collection $new): array
    {
        $updates = collect($existing);
        $new = $new->collect(); // don't modify the original

        foreach ($updates as $key => $item) {
            $changes = collect($new->pull($key))->toArray();

            if ($this->hasChanged($item, $changes)) {
                $item->update($changes);
            }
        }

        return [$updates, $new];
    }

    /**
     * Insert new records
     *
     * @param Collection|null $inserts The new data to insert
     * @param Relationship|null $relationship an optional relationship to insert against
     * @return Collection model instances for the inserted data
     */
    protected function insert(?Collection $inserts, ?Relation $relationship): ?Collection
    {
        // normalise the incoming inserts to a collection of arrays
        $inserts = collect(collect($inserts)->toArray());

        if ($inserts->isEmpty()) {
            return collect();
        }

        if ($relationship) {
            return $inserts->map(fn ($insert) => $relationship->create($insert));
        }

        $model = $this->modelClass();

        // insert each record & return the resulting model instances
        return $inserts->map(fn ($insert) => $model::create($insert));
    }

    /**
     * Delete records
     *
     * @param Collection|null $deletes The records to delete
     * @return void
     */
    protected function delete(?Collection $deletes): void
    {
        $deletes = collect($deletes);

        if ($deletes->isNotEmpty()) {
            $model = $this->modelClass($deletes->first());

            $ids = $deletes->map->getKey()->all();

            if ($this->force) {
                $model::whereIn('id', $ids)->forceDelete();
            } else {
                $model::destroy($deletes->map->getKey()->all());
            }
        }
    }

    /**
     * Has the item changed? If no columns are specified, we assume it has.
     *
     * @param Model $item The existing item
     * @param array $changes The new data to compare
     * @return bool
     */
    protected function hasChanged(Model $item, array $changes): bool
    {
        if (empty($this->ifChanged)) {
            return true;
        }

        return collect($this->ifChanged)
            ->contains(fn ($column) => $item->{$column} != $changes[$column]);
    }

    /**
     * Create a composite key from an array of columns.
     *
     * @param mixed $item item to create a key from, retrieving values from the unique columns to form a key
     * @return string
     */
    protected function key(mixed $item): string
    {
        $prefix = 'key:';

        // if no unique columns are set, use the model primary key - defaulting to a unique key value if none is set (as
        // this must be an insert)
        if (empty($this->uniqueBy)) {
            return $prefix . (
                $this->value($item, $this->primaryKey()) ?? 'new:' . static::$keyCounter++
            );
        }

        return $prefix . collect($this->uniqueBy)
            ->map(fn ($column) => $this->value($item, $column))
            ->implode('-');
    }

    /**
     * Key an array of items by a unique key
     */
    protected function keyByUnique(iterable $items): Collection
    {
        return collect($items)->keyBy(fn ($item) => $this->key($item));
    }

    /**
     * Retrieve a value from an item (which may be an object or an array)
     */
    protected function value(mixed $item, string $key): mixed
    {
        if (is_array($item) || $item instanceof ArrayAccess) {
            return $item[$key] ?? null;
        }

        if (is_object($item)) {
            return $item->{$key} ?? null;
        }

        throw new \Exception("Unable to retrieve $key for item. It must be an array or object");
    }

    /**
     * Retrieve the primary key for the model
     */
    protected function primaryKey(): string
    {
        if (!empty($this->primaryKey)) {
            return $this->primaryKey;
        }

        $model = $this->modelClass();
        return $this->primaryKey = (new $model())->getKeyName();
    }

    /**
     * Retrieve the model class we are operating on. If not set, set it from
     * a provided example model instance.
     *
     * @param Model|null $example An example model instance to set the model class from
     * @return string
     * @throws \Exception if no model class is set
     */
    protected function modelClass(?Model $example = null): string
    {
        if (!empty($this->modelClass)) {
            return $this->modelClass;
        }

        if ($example) {
            return $this->modelClass = $example::class;
        }

        throw new \Exception('No model class set for sync');
    }
}

ModelSyncManagerTest.php

<?php

use App\Services\ModelSyncManager;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Database\Eloquent\Factories\Factory;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Database\Eloquent\Factories\HasFactory;

/**
 * We will set up a fake temporary database tables for this test along with associated
 * models & factories
 */
// Define the models
class SyncManagerParent extends Model {
    use HasFactory;

    protected $table = 'sync_manager_parent';
    protected $fillable = ['name'];
    public $timestamps = false;

    public function children(): HasMany
    {
        return $this->hasMany(SyncManagerChild::class, 'parent_id');
    }
}

class SyncManagerChild extends Model {
    use HasFactory;

    protected $table = 'sync_manager_child';
    protected $fillable = ['parent_id', 'name', 'size'];
    public $timestamps = false;
}

// Define the factories
class SyncManagerParentFactory extends Factory {
    protected $model = SyncManagerParent::class;
    static int $id = 1;

    public function definition()
    {
        return [
            'id' => static::$id++,
            'name' => $this->faker->name,
        ];
    }

    public function withChildren(array $children)
    {
        return $this->afterCreating(function (SyncManagerParent $parent) use ($children) {
            foreach ($children as $child) {
                $child['parent_id'] = $parent->id;
                (new SyncManagerChildFactory())->create($child);
            }
        });
    }
}

class SyncManagerChildFactory extends Factory {
    protected $model = SyncManagerChild::class;
    static int $id = 1;

    public function definition()
    {
        return [
            'id' => static::$id++,
            'parent_id' => null,
            'name' => $this->faker->name,
            'size' => $this->faker->numberBetween(1, 100),
        ];
    }
}

beforeEach(function () {
    DB::statement('
        CREATE TEMPORARY TABLE IF NOT EXISTS sync_manager_parent (
            id INTEGER PRIMARY KEY,
            name TEXT
        )
    ');

    DB::statement('
        CREATE TEMPORARY TABLE IF NOT EXISTS sync_manager_child (
            id INTEGER PRIMARY KEY,
            parent_id INTEGER,
            name TEXT,
            size INTEGER,
            color TEXT DEFAULT NULL,
            material TEXT DEFAULT NULL,
            created_at timestamp NULL DEFAULT NULL,
            updated_at timestamp NULL DEFAULT NULL,
            FOREIGN KEY (parent_id) REFERENCES sync_manager_parent(id)
        )
    ');

    DB::statement('CREATE UNIQUE INDEX IF NOT EXISTS idx_unique_parent_name ON sync_manager_child (parent_id, name)');

    DB::statement('DELETE FROM sync_manager_parent');
    DB::statement('DELETE FROM sync_manager_child');

    $this->syncManager= new ModelSyncManager();
});

describe('syncRelated', function () {
    it('inserts new items', function () {
        $parent = (new SyncManagerParentFactory())->create();

        $items = [
            ['name' => 'foo', 'size' => 10],
            ['name' => 'bar', 'size' => 20],
        ];

        $this->syncManager->uniqueBy('name')->syncRelated($parent, 'children', $items);

        $parent->refresh();
        expect($parent->children->count())->toBe(2);

        expect($parent->children->firstWhere('name', 'foo'))->toBeInstanceOf(SyncManagerChild::class);
        expect($parent->children->firstWhere('name', 'bar'))->toBeInstanceOf(SyncManagerChild::class);
    });

    it('updates existing items', function () {
        $parent = (new SyncManagerParentFactory())
            ->withChildren([
                ['name' => 'foo', 'size' => 10],
                ['name' => 'bar', 'size' => 20],
            ])
            ->create();

        $newItems = [
            ['name' => 'foo', 'size' => 100],
            ['name' => 'bar', 'size' => 200],
        ];

        $ids = $parent->children->pluck('id', 'name');

        $this->syncManager->uniqueBy(['name'])->syncRelated($parent, 'children', $newItems);

        $parent->refresh();
        expect($parent->children->count())->toBe(2);

        $item1 = $parent->children->firstWhere('name', 'foo');
        expect($item1->id)->toBe($ids['foo']); // id should not change
        expect($item1->size)->toBe(100); // size should be updated

        $item2 = $parent->children->firstWhere('name', 'bar');
        expect($item2->id)->toBe($ids['bar']);
        expect($item2->size)->toBe(200);
    });

    it('only updates records where specified columns have changed', function () {
        $parent = (new SyncManagerParentFactory())
            ->withChildren([
                ['name' => 'foo', 'size' => 10, 'color' => 'red', 'material' => 'wood'],
                ['name' => 'bar', 'size' => 20, 'color' => 'green', 'material' => 'wood'],
                ['name' => 'baz', 'size' => 30, 'color' => 'blue', 'material' => 'wood'],
            ])
            ->create();

        $newItems = [
            ['name' => 'foo', 'size' => 100, 'color' => 'red', 'material' => 'wood'],
            ['name' => 'bar', 'size' => 200, 'color' => 'purple', 'material' => 'wood'],
            ['name' => 'baz', 'size' => 300, 'color' => 'blue', 'material' => 'metal'],
        ];

        $ids = $parent->children->pluck('id', 'name');

        $this->syncManager
            ->uniqueBy('name')
            ->ifChanged('color', 'material')
            ->syncRelated($parent, 'children', $newItems);

        $parent->refresh();
        expect($parent->children->count())->toBe(3);

        // item 1 should not have updated as neither color or material changed
        $item1 = $parent->children->firstWhere('name', 'foo');
        expect($item1->size)->toBe(10);

        // item 2 should have updated as color changed
        $item2 = $parent->children->firstWhere('name', 'bar');
        expect($item2->id)->toBe($ids['bar']);
        expect($item2->size)->toBe(200);
        expect($item2->color)->toBe('purple');
        expect($item2->material)->toBe('wood');

        // item 3 should have updated as material changed
        $item3 = $parent->children->firstWhere('name', 'baz');
        expect($item3->id)->toBe($ids['baz']);
        expect($item3->size)->toBe(300);
        expect($item3->color)->toBe('blue');
        expect($item3->material)->toBe('metal');
    });

    it('deletes missing items', function () {
        $parent = (new SyncManagerParentFactory())
            ->withChildren([
                ['name' => 'foo', 'size' => 10],
                ['name' => 'bar', 'size' => 20],
            ])
            ->create();

        $newItems = [
            ['name' => 'foo', 'size' => 100],
        ];

        $this->syncManager->uniqueBy(['name'])->syncRelated($parent, 'children', $newItems);

        $parent->refresh();
        expect($parent->children->count())->toBe(1);

        expect($parent->children->first()->name)->toBe('foo');
    });

    it('inserts, updates, and deletes items correctly with no unique columns', function () {
        $parent = (new SyncManagerParentFactory())
            ->withChildren([
                ['name' => 'foo', 'size' => 10],
                ['name' => 'bar', 'size' => 20],
            ])
            ->create();

        $ids = $parent->children->pluck('id', 'name');

        $newItems = $parent->children->sortBy('name')->toArray();
        array_shift($newItems);
        $newItems[0]['size'] = 100;
        $newItems[] = ['parent_id' => $parent->id, 'name' => 'baz', 'size' => 300];  // insert

        $result = $this->syncManager->syncRelated($parent, 'children', $newItems);

        // ensure the returned results look correct
        expect($result)->toBeInstanceOf(Collection::class);
        expect($result->count())->toBe(2);
        expect($result->first()->id)->toBe($ids['foo']);
        expect($result->first()->name)->toBe('foo');
        expect($result->first()->size)->toBe(100);
        expect($result->last()->name)->toBe('baz');
        expect($result->last()->size)->toBe(300);

        // ensure the database entries are correct
        $parent->refresh();
        expect($parent->children->count())->toBe(2);

        $item1 = $parent->children->firstWhere('name', 'foo');
        expect($item1->id)->toBe($ids['foo']); // id should not change
        expect($item1->size)->toBe(100); // size should be updated

        $item2 = $parent->children->firstWhere('name', 'baz');
        expect($item2->id)->not->toBeNull();
        expect($item2->size)->toBe(300);
    });

    it('inserts, updates, and deletes items correctly with unique columns', function () {
        $parent = (new SyncManagerParentFactory())
            ->withChildren([
                ['name' => 'foo', 'size' => 10],
                ['name' => 'bar', 'size' => 20],
            ])
            ->create();
        $ids = $parent->children->pluck('id', 'name');

        // these should not impact the existing items with a different parent_id part of the unique id
        $irrelevant = (new SyncManagerParentFactory())
            ->withChildren([
                ['name' => 'foo', 'size' => 50],
                ['name' => 'bar', 'size' => 60],
                ['name' => 'baz', 'size' => 70],
            ])
            ->create();
        $irrelevantIds = $irrelevant->children->pluck('id', 'name');

        $newItems = [
            ['parent_id' => $parent->id, 'name' => 'foo', 'size' => 100], // update
            ['parent_id' => $parent->id, 'name' => 'baz', 'size' => 30],  // insert
        ];

        $this->syncManager->uniqueBy('parent_id', 'name')
            ->syncRelated($parent, 'children', $newItems);

        $parent->refresh();
        expect($parent->children->count())->toBe(2);

        $item1 = $parent->children->firstWhere('name', 'foo');
        expect($item1->id)->toBe($ids['foo']); // id should not change
        expect($item1->size)->toBe(100); // size should be updated

        $item2 = $parent->children->firstWhere('name', 'baz');
        expect($item2->id)->not->toBe($irrelevantIds['baz']); // it should be a new item - not the existing item from another parent
        expect($item2->size)->toBe(30);

        // check the irrelevant items are not impacted
        $irrelevant->refresh();
        expect($irrelevant->children->count())->toBe(3);

        $item1 = $irrelevant->children->firstWhere('name', 'foo');
        expect($item1->id)->toBe($irrelevantIds['foo']); // id should not change
        expect($item1->size)->toBe(50); // size should not change

        $item2 = $irrelevant->children->firstWhere('name', 'bar');
        expect($item2->id)->toBe($irrelevantIds['bar']); // id should not change
        expect($item2->size)->toBe(60); // size should not change

        $item3 = $irrelevant->children->firstWhere('name', 'baz');
        expect($item3->id)->toBe($irrelevantIds['baz']); // id should not change
        expect($item3->size)->toBe(70); // size should not change
    });

    it('returns new models in correct order', function () {
        $parent = (new SyncManagerParentFactory())
            ->withChildren([
                ['name' => 'foo', 'size' => 10],
                ['name' => 'bar', 'size' => 20],
            ])
            ->create();

        $newItems = [
            ['parent_id' => $parent->id, 'name' => 'baz', 'size' => 30],  // insert
            ['parent_id' => $parent->id, 'name' => 'foo', 'size' => 100], // update
        ];

        $models = $this->syncManager->uniqueBy('name')
            ->syncRelated($parent, 'children', $newItems);

        expect($models)->toHaveCount(2);

        expect($models->pluck('name')->all())->toBe(['baz', 'foo']);
    });
});

describe('sync', function () {
    it('inserts, updates, and deletes items correctly', function () {
        $parent1 = (new SyncManagerParentFactory())
            ->withChildren([
                ['name' => 'foo', 'size' => 10],
                ['name' => 'bar', 'size' => 20],
            ])
            ->create();
        $ids1 = $parent1->children->pluck('id', 'name');

        // these should not impact the existing items with a different parent_id part of the unique id
        $parent2 = (new SyncManagerParentFactory())
            ->withChildren([
                ['name' => 'foo', 'size' => 50],
                ['name' => 'bar', 'size' => 60],
                ['name' => 'baz', 'size' => 70],
            ])
            ->create();
        $ids2 = $parent2->children->pluck('id', 'name');

        $existingItems = [
            ...$parent1->children,
            ...$parent2->children,
        ];

        // prepare updates, deleting parent1.bar, and parent2.foo
        $newItems = [
            ['parent_id' => $parent1->id, 'name' => 'foo', 'size' => 100], // update
            ['parent_id' => $parent1->id, 'name' => 'baz', 'size' => 300],  // insert
            ['parent_id' => $parent2->id, 'name' => 'bar', 'size' => 600],  // update
            ['parent_id' => $parent2->id, 'name' => 'baz', 'size' => 700],  // update
            ['parent_id' => $parent2->id, 'name' => 'qux', 'size' => 800],  // insert
        ];

        $this->syncManager->forModel(SyncManagerChild::class)
            ->uniqueBy(['parent_id', 'name'])
            ->sync($existingItems, $newItems);


        // parent1 looks correct
        $parent1->refresh();
        expect($parent1->children->count())->toBe(2);
        $this->assertDatabaseHas('sync_manager_child', ['id' => $ids1['foo'], 'parent_id' => $parent1->id, 'name' => 'foo', 'size' => 100]);
        $this->assertDatabaseMissing('sync_manager_child', ['id' => $ids1['bar']]);
        $this->assertDatabaseHas('sync_manager_child', ['parent_id' => $parent1->id, 'name' => 'baz', 'size' => 300]);

        // parent2 looks correct
        $parent2->refresh();
        expect($parent2->children->count())->toBe(3);
        $this->assertDatabaseMissing('sync_manager_child', ['id' => $ids2['foo']]);
        $this->assertDatabaseHas('sync_manager_child', ['id' => $ids2['bar'], 'parent_id' => $parent2->id, 'name' => 'bar', 'size' => 600]);
        $this->assertDatabaseHas('sync_manager_child', ['id' => $ids2['baz'], 'parent_id' => $parent2->id, 'name' => 'baz', 'size' => 700]);
        $this->assertDatabaseHas('sync_manager_child', ['parent_id' => $parent2->id, 'name' => 'qux', 'size' => 800]);
    });
});

describe('mass sync', function () {
    it('inserts, updates, and deletes items correctly', function () {
        $parent1 = (new SyncManagerParentFactory())
            ->withChildren([
                ['name' => 'foo', 'size' => 10],
                ['name' => 'bar', 'size' => 20],
            ])
            ->create();
        $ids1 = $parent1->children->pluck('id', 'name');

        // these should not impact the existing items with a different parent_id part of the unique id
        $parent2 = (new SyncManagerParentFactory())
            ->withChildren([
                ['name' => 'foo', 'size' => 50],
                ['name' => 'bar', 'size' => 60],
                ['name' => 'baz', 'size' => 70],
            ])
            ->create();
        $ids2 = $parent2->children->pluck('id', 'name');

        $existingItems = [
            ...$parent1->children,
            ...$parent2->children,
        ];

        // prepare updates, deleting parent1.bar, and parent2.foo
        $newItems = [
            ['parent_id' => $parent1->id, 'name' => 'foo', 'size' => 100], // update
            ['parent_id' => $parent1->id, 'name' => 'baz', 'size' => 300],  // insert
            ['parent_id' => $parent2->id, 'name' => 'bar', 'size' => 600],  // update
            ['parent_id' => $parent2->id, 'name' => 'baz', 'size' => 700],  // update
            ['parent_id' => $parent2->id, 'name' => 'qux', 'size' => 800],  // insert
        ];

        $this->syncManager->forModel(SyncManagerChild::class)
            ->uniqueBy(['parent_id', 'name'])
            ->massSync($existingItems, $newItems);

        // parent1 looks correct
        $parent1->refresh();
        expect($parent1->children->count())->toBe(2);
        $this->assertDatabaseHas('sync_manager_child', ['id' => $ids1['foo'], 'parent_id' => $parent1->id, 'name' => 'foo', 'size' => 100]);
        $this->assertDatabaseMissing('sync_manager_child', ['id' => $ids1['bar']]);
        $this->assertDatabaseHas('sync_manager_child', ['parent_id' => $parent1->id, 'name' => 'baz', 'size' => 300]);

        // parent2 looks correct
        $parent2->refresh();
        expect($parent2->children->count())->toBe(3);
        $this->assertDatabaseMissing('sync_manager_child', ['id' => $ids2['foo']]);
        $this->assertDatabaseHas('sync_manager_child', ['id' => $ids2['bar'], 'parent_id' => $parent2->id, 'name' => 'bar', 'size' => 600]);
        $this->assertDatabaseHas('sync_manager_child', ['id' => $ids2['baz'], 'parent_id' => $parent2->id, 'name' => 'baz', 'size' => 700]);
        $this->assertDatabaseHas('sync_manager_child', ['parent_id' => $parent2->id, 'name' => 'qux', 'size' => 800]);
    });

    it('only updates specified columns if set', function () {
        $parent1 = (new SyncManagerParentFactory())
            ->withChildren([
                ['name' => 'foo', 'size' => 10, 'color' => 'red'],
                ['name' => 'bar', 'size' => 20, 'color' => 'green'],
            ])
            ->create();

        $ids1 = $parent1->children->pluck('id', 'name');

        $newItems = [
            ['parent_id' => $parent1->id, 'name' => 'foo', 'size' => 100, 'color' => 'red'], // color not changed - don't update
            ['parent_id' => $parent1->id, 'name' => 'bar', 'size' => 200, 'color' => 'blue'], // color changed - updated
            ['parent_id' => $parent1->id, 'name' => 'baz', 'size' => 300, 'color' => 'green'],  // insert
        ];

        $this->syncManager->forModel(SyncManagerChild::class)
            ->uniqueBy(['parent_id', 'name'])
            ->massSync($parent1->children, $newItems, ['color']);

        $parent1->refresh();
        expect($parent1->children->count())->toBe(3);
        $this->assertDatabaseHas('sync_manager_child', ['id' => $ids1['foo'], 'parent_id' => $parent1->id, 'name' => 'foo', 'size' => 10, 'color' => 'red']); // no changes as color not updated
        $this->assertDatabaseHas('sync_manager_child', ['id' => $ids1['bar'], 'parent_id' => $parent1->id, 'name' => 'bar', 'size' => 20, 'color' => 'blue']); // color only updated
        $this->assertDatabaseHas('sync_manager_child', ['parent_id' => $parent1->id, 'name' => 'baz', 'size' => 300, 'color' => 'green']); // inserted so all columns set
    });

    it('only updates relevant records if using ifChanged', function () {
        $parent1 = (new SyncManagerParentFactory())
            ->withChildren([
                ['name' => 'foo', 'size' => 10, 'color' => 'red', 'updated_at' => '2020-01-01 00:00:00'],
                ['name' => 'bar', 'size' => 20, 'color' => 'green'],
            ])
            ->create();

        $ids1 = $parent1->children->pluck('id', 'name');

        $newItems = [
            ['parent_id' => $parent1->id, 'name' => 'foo', 'size' => 100, 'color' => 'red'], // color not changed - don't update
            ['parent_id' => $parent1->id, 'name' => 'bar', 'size' => 200, 'color' => 'blue'], // color changed - updated
        ];

        $this->syncManager->forModel(SyncManagerChild::class)
            ->uniqueBy('parent_id', 'name')
            ->ifChanged('color')
            ->massSync($parent1->children, $newItems);

        $parent1->refresh();
        expect($parent1->children->count())->toBe(2);
        $this->assertDatabaseHas('sync_manager_child', ['id' => $ids1['foo'], 'parent_id' => $parent1->id, 'name' => 'foo', 'size' => 10, 'color' => 'red', 'updated_at' => '2020-01-01 00:00:00']); // not updated as color didn't change
        $this->assertDatabaseHas('sync_manager_child', ['id' => $ids1['bar'], 'parent_id' => $parent1->id, 'name' => 'bar', 'size' => 200, 'color' => 'blue']); // updated as color changed
    });
});

describe('force delete', function () {
    class SyncManagerSoftChild extends SyncManagerChild {
        use HasFactory;
        use SoftDeletes;

        protected $table = 'sync_manager_soft_child';
    }

    class SyncManagerSoftChildFactory extends SyncManagerChildFactory {
        protected $model = SyncManagerSoftChild::class;
    }

    beforeEach(function () {
        DB::statement('
            CREATE TEMPORARY TABLE IF NOT EXISTS sync_manager_soft_child (
                id INTEGER PRIMARY KEY,
                parent_id INTEGER,
                name TEXT,
                size INTEGER,
                deleted_at TIMESTAMP DEFAULT NULl,
                FOREIGN KEY (parent_id) REFERENCES sync_manager_parent(id)
            )
        ');

        DB::statement('DELETE FROM sync_manager_soft_child');
    });

    it('should soft delete records by default', function () {
        $parent = (new SyncManagerParentFactory())->create();

        $existing1 = ['parent_id' => $parent->id, 'name' => 'foo', 'size' => 10];
        $child1 = (new SyncManagerSoftChildFactory())->create($existing1);

        $this->syncManager
            ->forModel(SyncManagerSoftChild::class)
            ->uniqueBy(['parent_id', 'name'])
            ->sync([$child1], []);

        $child = SyncManagerSoftChild::withTrashed()->find($child1->id);
        expect($child)->toBeInstanceOf(SyncManagerSoftChild::class);
        expect($child->deleted_at)->not->toBeNull();
    });

    it('should force delete records when requested', function () {
        $parent = (new SyncManagerParentFactory())->create();

        $existing1 = ['parent_id' => $parent->id, 'name' => 'foo', 'size' => 10];
        $child1 = (new SyncManagerSoftChildFactory())->create($existing1);

        // it should be there to start
        expect(SyncManagerSoftChild::withTrashed()->find($child1->id))
            ->toBeInstanceOf(SyncManagerSoftChild::class);

        $this->syncManager
            ->forModel(SyncManagerSoftChild::class)
            ->uniqueBy(['parent_id', 'name'])
            ->force()
            ->sync([$child1], []);

        // but gone after
        $child = SyncManagerSoftChild::withTrashed()->find($child1->id);
        expect($child)->toBeNull();
    });
});