How can I show quantity of child records without deleting child records when parent is deleted?

jimbiz

Reference: https://editor.datatables.net/examples/advanced/parentChild.html

Is there a way to set up the same example without the child records being deleted when I delete a Location?

I want to show the number of users in each location but a user should be able to exist if it has no location. Then I can edit the user on another page and assign it a location.

allan

That's a database construct. If you have on delete cascade for your database tables, then deleting a parent row will cause the linked child row to be deleted. That's not something that Editor specifically is doing.

What is your database schema?

Allan

jimbiz

Ok, good to know, thank you.

That's what I thought initially but the child table has a foreign key to the parent table with ON UPDATE CASCADE ON DELETE SET NULL.

I removed then added back my foreign key, tested again and it set to NULL this time on Delete.

jimbiz

Actually, after I added back the code to show the count of child records, my child records were deleted when I deleted a parent row.

Would it have anything to do with this paragraph from this page:
Additionally, please be aware that when Editor does an update for one-to-many linked data, it will delete all of the old references before inserting the new ones required by the edited data. This is another reason a link table is the preferred approach for the SQL data structure.

allan

Only if you are using an Mjoin - are you? Or are you using the more simple leftJoin?

Allan

jimbiz

Yes, I'm using an Mjoin. I've looked through every example and I don't see any leftJoin example that can be used for a one-to-many to get the count of all rows related to the parent table.

According to this page a leftJoin is only for one-to-one.

Any ideas of how I can show the count of rows of the child table? I don't want to edit the child table.

allan

I don't see any leftJoin example that can be used for a one-to-many to get the count of all rows related to the parent table.

It can't - leftJoin is 1:1.

Any ideas of how I can show the count of rows of the child table? I don't want to edit the child table.

Add ->set(false) to your Mjoin instance to stop any edits happening inside it.

Allan