Insert and delete multiple related records

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 resourcecategorylist

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

This is what I came up with: ```php $resource = Resource::findFirst($resource_id);

$categoryselected = arraymap(function ($item) { return $item['resourcecategorylist_id']; }, $resource->getCategory()->toArray())?:[];

if($this->request->isPost()) { $categoryselectedpost = $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.



84.1k

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



6.4k

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;


6.4k

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;
}