fill deleting records on second order relation

[tuseroni]

i am calling the following code on my product object:

$data = App\products::findOrFail($id);
$inputs=request()->all();
$parts=array_values($inputs["parts"]);
$data->fill(["parts"=>$parts]);

the part being filled looks like this:

Array
(
    [parts] => Array
        (
            [0] => Array
                (
                    [id] => 1
                    [product_id] => 1
                    [display_name] => body
                    [formula_name] => body
                    [part_export_formula] => 
                    [precision_ID] => 12
                    [attributes] => Array
                        (
                            [1] => Array
                                (
                                    [id] => 1
                                    [parts_id] => 1
                                    [attribute_name] => portion
                                    [output_page_width] => 
                                    [attribute_formula] => 0.6666
                                )

                        )

                )

            [1] => Array
                (
                    [id] => 2
                    [product_id] => 1
                    [display_name] => formula
                    [formula_name] => formula
                    [part_export_formula] => 
                    [precision_ID] => 7
                    [attributes] => Array
                        (
                            [2] => Array
                                (
                                    [id] => 2
                                    [parts_id] => 2
                                    [attribute_name] => formulation
                                    [output_page_width] => 
                                    [attribute_formula] => assembly2.variable*cabinet_width*test.density
                                )

                        )

                )

            [2] => Array
                (
                    [id] => 
                    [product_id] => 1
                    [display_name] => foo
                    [formula_name] => foo
                    [part_export_formula] => 
                    [precision_ID] => 1
                    [attributes] => Array
                        (
                            [1566399634] => Array
                                (
                                    [id] => 
                                    [parts_id] => 
                                    [attribute_name] => bar
                                    [output_page_width] => 
                                    [attribute_formula] => 10
                                )

                        )

                )

        )

)

the two relevant tables before the fill look like this:

partsAttributes:
+----+----------------+-------------------+-----------------------------------------------+--------+-----------------------+----------+---------------------+------------+------------+
| id | attribute_name | output_page_width | attribute_formula                             | is_qty | should_hide_on_output | parts_id | created_at          | updated_at | deleted_at |
+----+----------------+-------------------+-----------------------------------------------+--------+-----------------------+----------+---------------------+------------+------------+
|  1 | portion        |              NULL | 0.6666                                        |      0 |                     0 |        1 | 2019-07-17 18:11:19 | NULL       | NULL       |
|  2 | formulation    |              NULL | assembly2.variable*cabinet_width*test.density |      0 |                     0 |        2 | 2019-07-17 18:11:19 | NULL       | NULL       |
+----+----------------+-------------------+-----------------------------------------------+--------+-----------------------+----------+---------------------+------------+------------+
parts:
+----+--------------+--------------+---------------------+--------------+---------------------+---------------------+------------+------------+
| id | display_name | formula_name | part_export_formula | precision_ID | created_at          | updated_at          | deleted_at | product_id |
+----+--------------+--------------+---------------------+--------------+---------------------+---------------------+------------+------------+
|  1 | body         | body         | NULL                |           12 | 2019-08-21 16:24:08 | 2019-08-21 16:24:08 | NULL       |          1 |
|  2 | formula      | formula      | NULL                |            7 | 2019-08-21 16:24:08 | 2019-08-21 16:24:08 | NULL       |          1 |
+----+--------------+--------------+---------------------+--------------+---------------------+---------------------+------------+------------+

and after like this:

partsAttributes:
+----+----------------+-------------------+-------------------+--------+-----------------------+----------+---------------------+---------------------+------------+
| id | attribute_name | output_page_width | attribute_formula | is_qty | should_hide_on_output | parts_id | created_at          | updated_at          | deleted_at |
+----+----------------+-------------------+-------------------+--------+-----------------------+----------+---------------------+---------------------+------------+
|  7 | bar            |              NULL | 10                |      0 |                     0 |        7 | 2019-08-21 16:37:19 | 2019-08-21 16:37:19 | NULL       |
+----+----------------+-------------------+-------------------+--------+-----------------------+----------+---------------------+---------------------+------------+
parts:
+----+--------------+--------------+---------------------+--------------+---------------------+---------------------+------------+------------+
| id | display_name | formula_name | part_export_formula | precision_ID | created_at          | updated_at          | deleted_at | product_id |
+----+--------------+--------------+---------------------+--------------+---------------------+---------------------+------------+------------+
|  1 | body         | body         | NULL                |           12 | 2019-08-21 16:37:19 | 2019-08-21 16:37:19 | NULL       |          1 |
|  2 | formula      | formula      | NULL                |            7 | 2019-08-21 16:37:19 | 2019-08-21 16:37:19 | NULL       |          1 |
|  7 | foo          | foo          | NULL                |            1 | 2019-08-21 16:37:19 | 2019-08-21 16:37:19 | NULL       |          1 |
+----+--------------+--------------+---------------------+--------------+---------------------+---------------------+------------+------------+

it deleted my existing attributes, i thought i might be the on delete cascade from the foreign key (maybe you were deleting the attribute, adding it back in, then deleting the part, adding it back in and the attribute was being deleted due to the cascade, but i removed the foreign key and this still happened. only the new attribute is left in the table)

i'm not sure what is happening here, but i'm pretty sure it is not the correct behaviour.

some more info from the sql log:

144 Prepare   delete from `parts` where `parts`.`product_id` = ? and `parts`.`product_id` is not null
144 Execute   delete from `parts` where `parts`.`product_id` = 1 and `parts`.`product_id` is not null
144 Close stmt
144 Prepare   insert into `parts` (`id`, `product_id`, `display_name`, `formula_name`, `part_export_formula`, `precision_ID`, `updated_at`, `created_at`) values (?, ?, ?, ?, ?, ?, ?, ?)
144 Execute   insert into `parts` (`id`, `product_id`, `display_name`, `formula_name`, `part_export_formula`, `precision_ID`, `updated_at`, `created_at`) values ('1', 1, 'body', 'body', NULL, '12', '2019-08-21 18:07:14', '2019-08-21 18:07:14')
144 Close stmt
144 Prepare   delete from `partsattributes` where `partsattributes`.`parts_id` = ? and `partsattributes`.`parts_id` is not null
144 Execute   delete from `partsattributes` where `partsattributes`.`parts_id` = 1 and `partsattributes`.`parts_id` is not null
144 Close stmt
144 Prepare   update `partsattributes` set `id` = ?, `parts_id` = ?, `attribute_name` = ?, `output_page_width` = ?, `attribute_formula` = ?, `partsattributes`.`updated_at` = ? where `id` = ?
144 Execute   update `partsattributes` set `id` = '1', `parts_id` = 1, `attribute_name` = 'portion', `output_page_width` = NULL, `attribute_formula` = '0.6666', `partsattributes`.`updated_at` = '2019-08-21 18:07:14' where `id` = 1
144 Close stmt

whatever it is you are doing the sql is deleting the attributes and then trying to update the deleted attributes instead of inserting a new one.

you can see the statement it makes for parts is different:

144 Prepare   delete from `parts` where `parts`.`product_id` = ? and `parts`.`product_id` is not null
144 Execute   delete from `parts` where `parts`.`product_id` = 1 and `parts`.`product_id` is not null
144 Close stmt
144 Prepare   insert into `parts` (`id`, `product_id`, `display_name`, `formula_name`, `part_export_formula`, `precision_ID`, `updated_at`, `created_at`) values (?, ?, ?, ?, ?, ?, ?, ?)
144 Execute   insert into `parts` (`id`, `product_id`, `display_name`, `formula_name`, `part_export_formula`, `precision_ID`, `updated_at`, `created_at`) values ('1', 1, 'body', 'body', NULL, '12', '2019-08-21 18:07:14', '2019-08-21 18:07:14')
144 Close stmt

in that case it inserts instead of updating, but for attributes it tries to update the existing ones it just deleted. i'll keep looking over the code to see why it's updating and not inserting (the delete and recreate is fine as long as it does parts then attributes which it seems to be doing, and insert is more correct here since the attribute it's trying to delete doesn't exist as it was deleted in the cascade, but it doesn't know that so it makes a kind of sense) i can tell where it does the delete, it stands out well enough, the update though...not so sure

ok, so after some work i have found if you change the line:

    $related->exists = $related->getKey() != null;

to

$related->exists = $related->wasRecentlyCreated;

the problem goes away and it works fine.

what i think is happening is the line:

$related = $relation->getRelated()->newInstance($related);

is calling fill on the object, which then calls fillHasOneOrManyRelation on the child object, which then saves and creates the parent object in the process, the parent object needs to have exists set to true after that or it will try to insert again and get a pk violation, which i'm thinking is what you were doing here, however you were using the existence of a key as an indicator that the record exists, but the extant attributes also had a key, however you had deleted them in an earlier line so they weren't extant at that point.

so what i did was check if the record had recently been added (since you had previously deleted it the only way it can be in the database is if it had been recently inserted)

i'll clone down your repository, make the changes and put in a pull request

[troelskn]

Hi,

Thanks for your analysis. I'll have a look at the PR and approve it once I understand the implications.

[hugeps]

Lads, has this PR been merged ?
I'm observing following behaviour on a similar sample:
$document->fill( ["items" => $items] );

items has both "id" and "document_id" fields set, so technically should be possible to "update", but instead we're getting "delete" followed by "insert".

Where some other table may have a foreign key to that items "id" this will become a problem.

`
delete from details where details.document_id = 21082 and details.document_id is not null;

insert into details (id, document_id, position, material, description, quantity, units, updated_at, created_at) values (13046, 21082, '0010', 'BLAH', 'ALOHAMA', '10.00', 'EA', '2019-12-25 12:44:22', '2019-12-25 12:44:22');

`

[mirkos93]

@hugeps Have you found a solution?
i've the same problem, models are deleted and recreated every time, even by entering the id..
Thank you

[hugeps]

I've created my own repo, merged the PR above and "bobs yer uncle" it worked... Now, I'm behind tracking this repo...
But old code still works (as in customer not compaining)...

[mirkos93]

@hugeps mainly thanks for your immediate reply. It would appear that the PR has been merged on this repository but I've still some problems.
I try to explain, maybe you can help me..

These are my models and their structure:

• Course
  • HasMany -> Surveys
• Survey
  • HasMany -> Questions
• Question
  • HasMany -> Answers
• Answers

This is the array that I try to pass to the fill function of the Course model

[
	'id' => 1,
	'course_title' => 'My Course',
	'surveys' => [
		[
			'id' => 1,
			'survey_title' => 'My Survey',
			'questions' => [
				[
					'id' => 1,
					'question_title' => 'What's the color of Napoleon's white horse?',
					'answers' => [
						[
							'id' => 1,
							'answer_title' => 'Red',
							'correct' => false
						],
						[
							'id' => 2,
							'answer_title' => 'White',
							'correct' => true
						],
						[
							'id' => 3,
							'answer_title' => 'Blue',
							'correct' => false
						]				
					]
				]
			]
		]
	]
]

On saving, however, the informations of the Course model is correctly updated, while the surveys are instead deleted and re-entered, while i'm sending, as you can see, the ID to ensure that it is updated..

Do you have any idea why this is happening?

Thank you very much

[hugeps]

How about you try make the parent id field fillable inside a child model ? just in case i.e. survey_id inside questions and so on...

[mirkos93]

I've setted course_id as fillable on CourseSurvey model, and course_survey_id as fillable on CourseSurveyQuestion and finally course_survey_question_id on CourseSurveyQuestionAnswer model, but nothing, it continues to delete and re-insert the values. I also tried to insert id as fillable in CourseSurvey but rightly the response from the server was:

Integrity constraint violation: 1062 Duplicate entry '43' for key 'course_surveys.PRIMARY'

[mirkos93]

@hugeps Removing the SoftDeletes seems to be the only solution. In this way the system will always delete and re-create, but will "overwrite" the ids thus keeping the references unchanged. It's a dirty and limiting solution, but perhaps the only one for the moment (?)