Update relations via set of ids or something like that

[mrakolice]

I need to update HasMany and ManyToMany relations via set of ids, instead of loading entities that exists in database.

For example:

Child table

Id	Parent id	field
1	1	1
2	1	2
3	null	5

$parent->children->sync([1,3]);
$parent->save();

Child table

Id	Parent id	field
1	1	1
2	null	2
3	1	5

ORM should generate 2 update queries for nullable hasMany relations and 1 update and 1 delete query for not nullable hasMany relations and ManyToMany relations.

UPDATE child SET parent_id = null
WHERE id IN (SELECT unnest(?) EXCEPT
SELECT child.id FROM child WHERE child.parent_id=?);

DELETE FROM child 
WHERE id IN (SELECT unnest(?) EXCEPT
SELECT child.id FROM child WHERE child.parent_id=?);

UPDATE child SET parent_id = ?
WHERE child.id IN ?;

[wolfy-j]

This is doable since the relation controls chain of persist commands, need to think about an API to avoid fetching... Let me think about it.

[alexndr-novikov]

@mrakolice technically it's possible when using Cycle, but are you sure it's a good idea to use Data mapper orm like this? It will give too much control over persistence layer to you application layer if you modify database relations via ids It's more usual to operate with relations as collection of objects (or inner object for HasOne case)

[mrakolice]

$parent = [
    'field1' => 'value'
    'children' => 
    [
         ['id' => 1],
         ['id' => 2],
         ['id' => 3],
    ]

];

All child entities exists in database, but child entity with id=3 not in this particular parent.

So, when I going to do smth like this:

$parent = $orm->getRepository(Parent::class)->load('children');

I can use only entities with [1,2] id; And this is what I can do with collection of objects:

$childIds = $parent->children->map(); // or load entities only with id via load method above

$entitiesToAdd = diff($ids, $childIds);

foreach ($orm->getRepository(Child::class)->where('id', 'in', $entitiesToAdd) as $child){
    $parent->children->add($child);
}

$transaction->persist($parent);
$transaction->run();

This approach a bit sophisticated for me and I need to fetch entities to add from database, but this is just diff operation between two arrays, no matter - in php layer or sql layer.

If I do something like this:

$entities = $orm->getRepository(Child::class)->where('id', 'in', $entitiesToAdd);
$transaction = new Transaction();

foreach ($entities as $child){
    $child->parent = $parent;
    $transaction->persist($child);
}
$transaction->run();

This will be produce many update queries on sql layer.

I can use UpdateQueryBuilder (and I've solved this problem exactly via QueryBuilder), but I think, that this problem very viable for many developers, that using Cycle ORM or any another ORM. =)