Insert and delete multiple related records

Question

Insert and delete multiple related records

Lewishop Apr '13

Created Apr '13	Last Reply Apr '13	Replies 1	Views 3624	Votes 3

Lewishop 10.2k

Apr '13

I'm trying to insert/delete related categories to a resource. The view generates a list with checkboxes all named 'category[]', the value/title are from a domain table resource_category_list

Models: Resource has many ResourceCategory (alias category) ResourceCategory belongsto Resource and ResourceCategoryList ResourceCategoryList has many ResourceCategory

This is what I came up with:

$resource = Resource::findFirst($resource_id);

$category_selected = array_map(function ($item) { return $item['resource_category_list_id']; }, $resource->getCategory()->toArray())?:[];

if($this->request->isPost())
{
    $category_selected_post = $this->request->getPost('category')?:[];

    $category_add = array_diff($category_selected_post, $category_selected);
    $category_remove = array_diff($category_selected, $category_selected_post);
    foreach ($category_add as $resource_category_list_id) {
        $cat = new ResourceCategory();
        $cat->resource_id = $resource->id;
        $cat->resource_category_list_id = $resource_category_list_id;
        $cat->save();
    }
    $resource->category->delete(function($cat) use($category_remove) { return in_array($cat->resource_category_list_id, $category_remove); });

    $category_selected = $category_selected_post;
}

I'm wondering if there is a better way to achieve te same result? Without completely deleting and reassinging categories to the resource.

3

Phalcon · Answer 1 · 2013-04-07T17:06:11-07:00

Phalcon
98.9k

Apr '13

Could you please post the tables or a gist with the complete code?

Lewishop · Answer 2 · 2013-04-09T01:32:19-07:00

simplyfied db schema and sample content

CREATE TABLE IF NOT EXISTS `resource` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(1024) DEFAULT NULL,  
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `resource_category` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `resource_id` bigint(20) unsigned NOT NULL,
  `resource_category_list_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `resource_id_idx` (`resource_id`),
  KEY `resource_category_list_id_idx` (`resource_category_list_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `resource_category_list` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(150) NOT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO `resource` (`id`, `title`) VALUES
(1, 'sample');

INSERT INTO `resource_category` (`id`, `resource_id`, `resource_category_list_id`) VALUES
(1, 1, 1),
(2, 1, 2);

INSERT INTO `resource_category_list` (`id`, `title`) VALUES
(1, 'category a'),
(2, 'category b');

ALTER TABLE `resource_category`
  ADD CONSTRAINT `resource_category_ibfk_1` FOREIGN KEY (`resource_id`) REFERENCES `resource` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  ADD CONSTRAINT `resource_category_ibfk_2` FOREIGN KEY (`resource_category_list_id`) REFERENCES `resource_category_list` (`id`) ON UPDATE NO ACTION;

Lewishop · Answer 3 · 2013-04-09T01:45:54-07:00

An alternative way would be assigning the category array completely.. thus removing all items and adding them again if nothing or only a single item has changed

$resource = Resource::findFirst($resource_id);

$category_selected = array_map(function ($item) { return $item['resource_category_list_id']; }, $resource->getCategory()->toArray())?:[];

if($this->request->isPost())
{
  $category_selected_post = $this->request->getPost('category')?:[];

  $category = [];
  foreach ($category_selected_post as $resource_category_list_id) {
    $cat = new ResourceCategory();
    $cat->resource_id = $resource->id;
    $cat->resource_category_list_id = $resource_category_list_id;    
    array_push($category, $cat);
  }
  $resource->category = $category;

  $category_selected = $category_selected_post;
}