We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

How can I delete records in a many-to-many relationship?

First of all, I suspect this issue is closely related to the issue I describe below.

I want to delete records in a many-to-many relationship but I'm not able to. Here's the edit action of my article controller:

$article = Article::findFirst($id);
$article->title = $this->request->getPost('title');
$article->content = $this->request->getPost('content');
$article->sticky = $this->request->getPost('sticky') ?: 0;
$article->type = $this->request->getPost('type');

$tags = array();

foreach (preg_split('/\s*,\s*/', $this->request->getPost('tag_names')) as $tag_name) {
    $tag = Tag::findFirstByName($tag_name);

    if (!$tag) {
        $tag = new Tag();
        $tag->name = $tag_name;
    }

    $tags[] = $tag;
}

$article->tags = $tags;

$success = $article->save();

The problem is that it appends to the tags collection instead of overwriting it. For example, say I had the following article in the database:

+----+--------------+---------+------------+-------------------+--------+--------+
| id | title        | user_id | timestamp  | content           | sticky | type   |
+----+--------------+---------+------------+-------------------+--------+--------+
|  1 | Hello World  |       1 | 1419790166 | Hello world!      |      1 | news   |
+----+--------------+---------+------------+-------------------+--------+--------+

And the tags:

+----+---------+
| id | name    |
+----+---------+
|  1 | Hello   |
|  2 | World   |
+----+---------+

And the article_tags:

+----+------------+--------+
| id | article_id | tag_id |
+----+------------+--------+
|  1 |          1 |      1 |
|  2 |          1 |      2 |
+----+------------+--------+

If the post value for tag_names is "Test, Testing", it should create two new tags, resulting in the tags:

+----+---------+
| id | name    |
+----+---------+
|  1 | Hello   |
|  2 | World   |
|  3 | Test    |
|  4 | Testing |
+----+---------+

And the article_tags:

+----+------------+--------+
| id | article_id | tag_id |
+----+------------+--------+
|  3 |          1 |      3 |
|  4 |          1 |      4 |
+----+------------+--------+

In other words, it should delete the previous records from the join table and create two new article_tags.

The first part works great - the new tags are created - but the second part does not. It creates two new article_tags, but it doesn't delete the previous records. So I end up with the article_tags:

+----+------------+--------+
| id | article_id | tag_id |
+----+------------+--------+
|  1 |          1 |      1 |
|  2 |          1 |      2 |
|  3 |          1 |      3 |
|  4 |          1 |      4 |
+----+------------+--------+

After reading other posts regarding this problem, as well as the GitHub issue referenced above, I tried to delete the tags before the save:

$article->tags->delete();
$article->tags = $tags;

$success = $article->save();

But this throws a foreign key constaint error:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`phalconsite`.`article_tag`, CONSTRAINT `article_tag_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`id`))' in /var/www/phalconsite/app/controllers/ArticleController.php...

Apparently tags->delete() deletes the tags themselves and not the records from the join table. This seems broken to me.

One user had something like this, which I tried next:

$article->articleTags->delete();
$article->tags = $tags;

$success = $article->save();

The problem with this is I don't have an articleTags property in that class, nor do I think I should. (Correct me if I'm wrong.)

What am I supposed to do?

(Anyone interested in the models and/or schema can look at my previous post.)

I believe you are going to get stuck trying to use the ORM if you have not created the articleTags model or property in a class, since that will establish the mapping to a table. The path of deleting the articleTag before tag insert is correct, however you simply might have to do an sql statement of delete from articletags where article_id=x



39.2k
Accepted
answer
edited Jan '15

Hi @Pajamaman. I recently solved a similar task.

I have some entities:

  • Attribute Group
  • Attribute
  • Product

My workaround (I omitted some code for brevity)::

class AttributeController extends DashboardController
{
    public function createAction()
    {
        $model = new Attribute;
        $form = new AttributeForm($model);

        if ($this->request->isPost()) {
            $data = [
                'name'        => $this->request->getPost('name'),
                'type'        => $this->request->getPost('type'),
                'description' => $this->request->getPost('description')
            ];

            $groupsToSave = [];
            if ($selectedGroups = $this->request->getPost('groups')) {
                foreach ($selectedGroups as $i => $groupId) {
                    if ($group = Attrgroup::findFirst($groupId)) {
                        $groupsToSave[$i] = $group;
                    }
                }
            }
            $model->assign($data);

            if (!empty($groupsToSave)) {
                $model->groups = $groupsToSave;
            }

            if ($model->save()) {
                $this->flash->success(sprintf('Attrribute %s  created', $model->getName()));

               // redirect to index
            } else {
                $this->flash->error($model->getMessages(null, true));
            }
        } else {
            $form->setEntity($model);
        }

        $this->view->setVars([
            'form' => $form
        ]);
    }

    public function updateAction($id)
    {
        $model = Attribute::findFirst($id);
        $form = new AttributeForm($model, ['edit' => true]);

        if ($this->request->isPost()) {
            $data = [
                'name'        => $this->request->getPost('name'),
                'type'        => $this->request->getPost('type'),
                'description' => $this->request->getPost('description')
            ];

            $model->assign($data);

            $model->attrgroups->delete();
            $groupsToSave = [];
            $selectedGroups = $this->request->getPost('groups');

            if (is_array($selectedGroups) && !empty($selectedGroups)) {

                foreach (Attrgroup::findIn($selectedGroups) as $newGroup) {
                    $attrgroupRelation = new AttrgroupsAttributes;
                    $attrgroupRelation->setAttributeId($model->getId());
                    $attrgroupRelation->setGroupId($newGroup->getId());
                    if ($attrgroupRelation->save()) {
                        $groupsToSave[] = $attrgroupRelation;
                    }
                }
            }

            if (!empty($groupsToSave)) {
                $model->attrgroups = $groupsToSave;
            }

            if ($model->save()) {
                $this->flash->success(sprintf('Attribute %s updated', $model->getName()));

                // redirect to index
            } else {
                $this->flash->error($model->getMessages(null, true));
            }
        } else {
            $form->setEntity($model);
        }

        $this->view->setVars([
            'form'      => $form,
            'attribute' => $model
        ]);
    }
}

Schema:


-- omitted product table here

CREATE TABLE `attrgroups` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(128) NOT NULL,
  `position` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
  `description` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `attrgroup_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `attributes` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `type` ENUM('string','int','double','bool') NOT NULL DEFAULT 'string',
  `description` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `attribute_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `attrgroups_attributes` (
  `attribute_id` INT(11) UNSIGNED NOT NULL,
  `group_id` INT(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`attribute_id`,`group_id`),
  KEY `attrgroups_attribute_attribute` (`attribute_id`),
  KEY `attrgroups_attribute_group` (`group_id`),
  FOREIGN KEY (`attribute_id`) REFERENCES `attributes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`group_id`) REFERENCES `attrgroups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `attrvalues` (
  `product_id` BIGINT(20) UNSIGNED NOT NULL,
  `attribute_id` INT(11) UNSIGNED NOT NULL,
  `value` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`product_id`,`attribute_id`),
  KEY `attrvalue_product` (`product_id`),
  KEY `attrvalue_attribute` (`attribute_id`),
  FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`attribute_id`) REFERENCES `attributes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

@Pajamaman

Some code specific for my task and it will not work for you, if you just copy it. But I hope that the solution is clear to you.



5.6k

@sergeyklay can you post your models? That would be most helpful.

@sergeyklay can you post your models? That would be most helpful.

that refers to relations looks so:

<?php

namespace Esig\Models;

class Attrgroup extends BaseModel
{
    public function initialize()
    {
        $this->hasManyToMany(
            'id',
            __NAMESPACE__ . '\AttrgroupsAttributes',
            'group_id', 'attribute_id',
            __NAMESPACE__ . '\Attribute',
            'id',
            ['alias' => 'attributes']
        );
    }
}

class AttrgroupsAttributes extends BaseModel
{

    public function initialize()
    {
        $this->belongsTo(
            'attribute_id',
            __NAMESPACE__ . '\Attribute',
            'id',
            ['alias' => 'attribute']
        );
        $this->belongsTo(
            'group_id',
            __NAMESPACE__ . '\Attrgroup',
            'id',
            ['alias' => 'group']
        );
    }
}

class Attribute extends BaseModel
{
    public function initialize()
    {
        $this->hasMany(
            'id',
            __NAMESPACE__ . '\AttrgroupsAttributes', 'attribute_id',
            ['alias' => 'attrgroups']
        );

        $this->hasManyToMany(
            'id',
            __NAMESPACE__ . '\AttrgroupsAttributes',
            'attribute_id', 'group_id',
            __NAMESPACE__ . '\Attrgroup',
            'id',
            ['alias' => 'groups']
        );
    }
}


5.6k
edited Jan '15

@sergeyklay you have both the hasMany and hasManyToMany mappings in your Attribute class? That seems weird, but I'll give it a shot.

@sergeyklay you have both the hasMany and hasManyToMany mappings in your Attribute class? That seems weird, but I'll give it a shot.

It's not so strange, as far as it might seem at first glance.

Attribute hasMany AttrgroupsAttributes called attrgroups and hasManyToMany Attrgroup called groups

Maybe it's not perfect, but it's an extremely accurately reflects the required relation. Here is missing relation with the products, which are in turn are related with categories, but that's another story.

@Pajamaman So, issue is solved?



5.6k

@Pajamaman So, issue is solved?

Yes, thank you.

Is it just me or is this behavior not documented very well?

Also I still think it should be considered a bug :/