Synchronizing a one-to-many relationship in Laravel
Solution 1
Unfortunately there is no sync
method for one-to-many relations. It's pretty simple to do it by yourself. At least if you don't have any foreign key referencing links
. Because then you can simple delete the rows and insert them all again.
$links = array(
new Link(),
new Link()
);
$post->links()->delete();
$post->links()->saveMany($links);
If you really need to update existing one (for whatever reason) you need to do exactly what you described in your question.
Solution 2
The problem with deleting and readding the related entities, is that it will break any foreign key constraints you might have on those child entities.
A better solution is to modify Laravel's HasMany
relationship to include a sync
method:
<?php
namespace App\Model\Relations;
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 override Eloquent's Model
class to use HasManySyncable
instead of the standard HasMany
relationship:
<?php
namespace App\Model;
use App\Model\Relations\HasManySyncable;
use Illuminate\Database\Eloquent\Model;
abstract class MyBaseModel extends Model
{
/**
* Overrides the default Eloquent hasMany relationship to return a HasManySyncable.
*
* {@inheritDoc}
* @return \App\Model\Relations\HasManySyncable
*/
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
);
}
Supposing that your Post
model extends MyBaseModel
and has a links()
hasMany
relationship, you can do something like:
$post->links()->sync([
[
'id' => 21,
'name' => "LinkedIn profile"
],
[
'id' => null,
'label' => "Personal website"
]
]);
Any records in this multidimensional array that have an id
that matches the child entity table (links
) will be updated. Records in the table that are not present in this array will be removed. Records in the array that are not present in the table (Have a non-matching id
, or an id
of null) will be considered "new" records and will be inserted into the database.
Solution 3
You can use UPSERT to insert or update on duplicate key, also using relations.
That means that you can comper your old data with your new data, and use an array with the data to be updated with the data to be inserted in the same query.
Also you can delete other ids that are not needed.
Here an example:
$toSave = [
[
'id'=>57,
'link'=>'...',
'input'=>'...',
],[
'id'=>58,
'link'=>'...',
'input'=>'...',
],[
'id'=>null,
'link'=>'...',
'input'=>'...',
],
];
// Id of models you wish to keep
// Keep existing that dont need update
// And existing that will be updated
// The query will remove the rest from the related Post
$toKeep = [56,57,58];
// We skip id 56 cause its equal to existing
// We will insert or update the rest
// Elements in $toSave without Id will be created into the relationship
$this->$relation()->whereNotIn('id',$toKeep)->delete();
$this->$relation()->upsert(
$toSave, // Data to be created or updated
['id'], // Unique Id Column Key
['link','input'] // Columns to be updated in case of duplicate key, insert otherwise
);
That will create the next queries:
delete from
`links`
where
`links`.`post_id` = 247
and `links`.`post_id` is not null
and `id` not in (56, 57, 58)
And:
insert into
`links` (`id`, `link`, `input`)
values
(57, '...', '...'),
(58, '...', '...'),
(null, '...', '...')
on duplicate key update
`link` = values(`link`),
`input` = values(`input`)
This is how you can update all elements of a relationship in just 2 queries. For example if you have 1,000 Posts, and you want to update all the links of all the posts.
Solution 4
Here is an updated answer inspired from @alexw
for laravel 7+ working with composite primary keys as well
In your app/Providers/AppServiceProvider.php
's boot
method add this macro
Illuminate\Database\Eloquent\Relations\HasMany::macro( 'sync', function ( $data, $deleting = true ) {
$changes = [
'created' => [], 'deleted' => [], 'updated' => [],
];
/**
* Cast the given keys to integers if they are numeric and string otherwise.
*
* @param array $keys
*
* @return array
*/
$castKeys = function ( array $keys ) {
return (array)array_map( function ( $v ) {
return is_numeric( $v ) ? (int)$v : (string)$v;
}, $keys );
};
$relatedKeyName = $this->related->getKeyName();
$getCompositeKey = function ( $row ) use ( $relatedKeyName ) {
$keys = [];
foreach ( (array)$relatedKeyName as $k ) {
$keys[] = data_get( $row, $k );
}
return join( '|', $keys );
};
// 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()->get( $relatedKeyName )->map( $getCompositeKey )->toArray();
// Separate the submitted data into "update" and "new"
$updateRows = [];
$newRows = [];
foreach ( $data as $row ) {
$key = $getCompositeKey( $row );
// We determine "updateable" rows as those whose $relatedKeyName (usually 'id') is set, not empty, and
// match a related row in the database.
if ( ! empty( $key ) && in_array( $key, $current ) ) {
$updateRows[$key] = $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 );
if ( $deleting ) {
$deleteIds = [];
foreach ( $current as $currentId ) {
if ( ! in_array( $currentId, $updateIds ) ) {
$deleteIds[$currentId] = array_combine( (array)$relatedKeyName, explode( '|', $currentId ) );
}
}
// Delete any non-matching rows
if ( count( $deleteIds ) > 0 ) {
/**
* @var \Illuminate\Database\Query\Builder $q
*/
$q = $this->newQuery();
$q->where(function ($q) use ( $relatedKeyName, $deleteIds) {
foreach ( $deleteIds as $row ) {
$q->where( function ( $q ) use ( $relatedKeyName, $row ) {
foreach ( (array)$relatedKeyName as $key ) {
$q->where( $key, $row[$key] );
}
}, null, null, 'or' );
}
});
$q->delete();
$changes['deleted'] = $castKeys( array_keys( $deleteIds ) );
}
}
// Update the updatable rows
foreach ( $updateRows as $id => $row ) {
$q = $this->getRelated();
foreach ( (array)$relatedKeyName as $key ) {
$q->where( $key, $row[$key] );
}
$q->update( $row );
}
$changes['updated'] = $castKeys( $updateIds );
// Insert the new rows
$newIds = [];
foreach ( $newRows as $row ) {
$newModel = $this->create( $row );
$newIds[] = $getCompositeKey( $newModel );
}
$changes['created'] = $castKeys( $newIds );
return $changes;
} );
An example of a composite primaryKey model
class PermissionAdmin extends Model {
public $guarded = [];
public $primaryKey = ['user_id', 'permission_id', 'user_type'];
public $incrementing = false;
public $timestamps = false;
}
Then you can use the sync method as you would normally use it with a belongsToMany relationship
$user->roles()->sync([
[
'role_id' => 1
'user_id' => 12
'user_type' => 'admin'
],
[
'role_id' => 2
'user_id' => 12
'user_type' => 'admin'
]
]);
Solution 5
I did like this, and it is optimized for minimal query and minimal updates:
first, put link ids to sync in an array: $linkIds
and the post model in its own variable: $post
Link::where('post_id','=',$post->id)->whereNotIn('id',$linkIds)//only remove unmatching
->update(['post_id'=>null]);
if($linkIds){//If links are empty the second query is useless
Link::whereRaw('(post_id is null OR post_id<>'.$post->id.')')//Don't update already matching, I am using Raw to avoid a nested or, you can use nested OR
->whereIn('id',$linkIds)->update(['post_id'=>$post->id]);
}
Related videos on Youtube
user2834172
Updated on July 09, 2022Comments
-
user2834172 almost 2 years
If I have a many-to-many relationship it's super easy to update the relationship with its
sync
method.But what would I use to synchronize a one-to-many relationship?
- table
posts
:id, name
- table
links
:id, name, post_id
Here, each
Post
can have multipleLink
s.I'd like to synchronize the links associated with a specific post in the database, against an inputted collection of links (for example, from a CRUD form where I can add, remove, and modify links).
Links in the database that aren't present in my input collection should be removed. Links that exist in the database and in my input should be updated to reflect the input, and links that are only present in my input should be added as new records in the database.
To summarize the desired behavior:
- inputArray = true / db = false ---CREATE
- inputArray = false / db = true ---DELETE
- inputArray = true / db = true ----UPDATE
- table
-
Ashish Patel over 6 yearscan it affect laravel's default has-many relationships in further operations?
-
Tarek Adam about 6 yearsDon't do it this way because in the future you may need to store pivot data... or worse - another coder will store pivot data not knowing that the sync is fake.
-
Tarek Adam about 6 yearsSorry, had my head somewhere else. Didn't mean "pivot" data. Still, the point remains.
-
Max13 almost 5 yearsWouldn't it exhaust much faster the primary key capacity, in case of AUTOINCREMENT?
-
Lewis almost 5 yearsUseful if you only have two related models. Unfortunately, in my case I have 3 or more models that depend on the ID of the record - so I can't just delete it and recreate.
-
Carl Kroeger Ihl almost 5 yearsThis looks good! but since it's an old answer, I wonder if it's still viable in newer versions of Laravel. Tested and worked. I will be implementing this in my project.
-
Charles Wood about 4 yearsBear in mind that bulk methods like this won't update timestamps or fire model events.
-
Pratik149 almost 4 years@Ashish No, it won't effect laravel's default has-many relationship operation because you are just adding a new function called sync to laravel's HasMany class, and not changing the default laravel's code/behavior.
-
Ashish Patel almost 4 years@Pratik149 I know that. As I asked this question 2 years ago. thanks anyways.
-
Pratik149 almost 4 years@Ashish Haha cool, tbh I didn't expect you to comment back. I actually dropped that comment for someone who would refer to this answer in future, and have the same doubt as you had, so that at least they won't be left unanswered.
-
Inserve over 3 yearsGreat function, still in Laravel 7
-
Inserve over 3 yearsOnly problem was that it was deleting ALL child records, instead of only the related child records. So I removed the newQuery function on line with $current = $this... and now it works fine. Not sure what that meant to do.
-
Fabio William Conceição over 3 yearsI used this solution now in 2020 and works perfectly and also is easily adaptable to HasOne relationships, great thanks to you shared this with us.
-
Tofandel about 3 yearsJust a note that RelationShips are macroable so there is an easier way to set the sync function using
HasMany::macro( 'sync', syncfunctiongoeshere);
-
Николай about 2 yearsits work and its great