Update a many-to-many relationship in Doctrine
I have an article-category relationship and I'd like to update the relationship when necessary. That means, adding or removing the desired categories from an article. I use a php (ZF) setup with Doctrine 1.2. In YAML, the config looks (simplified) like this:
Article:
columns:
id: bigint(10)
Category:
columns:
id: bigint (10)
relations:
articles:
foreignAlias: categories
class: Article
refClass: CategoryArticle
CategoryArticle:
columns:
category_id: bigint (10)
article_id: bigint (10)
relations:
category:
class: Category
foreignAlias: categoryArticle
article:
class: Article
foreignAlias: categoryArticle
I have a persisted $article where all old categories are开发者_StackOverflow中文版 available. With a POST request I get a list of category ids which should be the new ones. I have this so far:
$oldCategories = array();
foreach ($article->categories as $cat) {
$oldCategories[] = $cat->id;
}
$newCategories = $form->getValue('categories');
$diffRemove = array_diff($oldCategories, $newCategories);
$diffAdd = array_diff($newCategories, $oldCategories);
foreach ($diffRemove as $id) {
// Remove all the categories $id from article [1]
}
foreach ($diffAdd as $id) {
// Add all the categories $id to article [2]
}
My question is about [1] and [2]. What is the best performance to add and remove a many:many relationship?
Deletion
The most efficient way of deleting en masse in SQL is using single sql statement with some conditions set, something like
delete from Products where id>3
It make use of indexes, partitioning, etc.
There is a way to achieve that value of performance using Doctrine 1.2 - using DQL DELETE statements. As shown in documentation, the following query transforms into the above SQL:
$q = Doctrine_Query::create()
->delete('Products p')
->where('p.id > 3');
In your case, you could optimize deletion with something like
$q = Doctrine_Query::create()
->delete('CategoryArticle ca')
->where('ca.article_id=?', $article_id)
->andWhereIn('ca.category_id', $diffRemove);
This code should generate something like:
delete from CategoryArticle
where article_id = $article_id and category_id in ($diffremove)
Insertion
With insertion you could use CategoryArticle
entity. The most effective way to do that with Doctrine is to build up a collection of Entities and than save that collecion:
$collection = new Doctrine_Collection('CategoryArticle');
foreach ($diffAdd as $id){
$ca = new CategoryArticle();
$ca->category = $id;
$ca->article = $article_id;
$collection[] = $ca;
}
$collection->save();
精彩评论