sync many-to-many relation with duplicate ids

so i have a table that looks like this:

CREATE TABLE `product_parts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`prod_id` int(11) DEFAULT NULL,
`part_id` int(11) DEFAULT NULL,
`is_root` tinyint(1) DEFAULT NULL,
`name` text,
`xFormula` text,
`yFormula` text,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `prod_id` (`prod_id`),
KEY `part_id` (`part_id`),
CONSTRAINT `product_parts_ibfk_1` FOREIGN KEY (`prod_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
CONSTRAINT `product_parts_ibfk_2` FOREIGN KEY (`part_id`) REFERENCES `parts` (`id`) ON DELETE CASCADE

as you can see this is a pivot between parts and products, a product can have multiple of the same part, but the sync takes an associative array keyed on the part id in order to sync with the pivot information, this means each part has to have its own unique id.

so an example of what i'm trying to put in:

[{"part_id":52,"is_root":true,"name":"left","xFormula":"","yFormula":""},{"part_id":54,"is_root":false,"name":"top","xFormula":"","yFormula":""},{"part_id":54,"is_root":false,"name":"bottom","xFormula":"","yFormula":""},{"part_id":52,"is_root":false,"name":"first","xFormula":"","yFormula":""},{"part_id":52,"is_root":false,"name":"second","xFormula":"","yFormula":""},{"part_id":52,"is_root":false,"name":"third","xFormula":"","yFormula":""},{"part_id":52,"is_root":false,"name":"back","xFormula":"","yFormula":""}]

this would be doable if the sync accepted an array for the key, so instead of [52=>["is_root"=>true,...]] i would have [52=>[["is_root"=>true,..],["is_root"=>false,...]],54=>...]

is there any way to accomplish this (aside from making a many-to-one table with the product id, part id, and other pivot information)

tuseroni

6 years ago

Level 1

well i found a workaround, but i'd like a less...hackish way:

$prod_parts=[];
$num=0;
foreach($productInput["product_parts"] as $key=>$val)
{
$prod_parts[$val["part_id"].str_repeat(" ",$num)]=["is_root"=>$val["is_root"],"name"=>$val["name"],"xFormula"=>$val["xFormula"],"yFormula"=>$val["yFormula"]];
$num++;
}

this is based on the fact that mysql doesn't care about a space in an id, laravel doesn't check the id corresponds to something that exists beyond whether the server complains, and that an associative array does care about spaces. so "54" is different enough from "54 " that php is happy, mysql feels "54" is "54 " so mysql is happy, and laravel isn't being complained at by the server so laravel is happy.

but...yeah...its hackish as crap.

Please or to participate in this conversation.