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

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

jimbizjimbiz Posts: 14Questions: 5Answers: 1

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.

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • jimbizjimbiz Posts: 14Questions: 5Answers: 1
    edited February 2020

    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.

  • jimbizjimbiz Posts: 14Questions: 5Answers: 1

    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.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

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

    Allan

  • jimbizjimbiz Posts: 14Questions: 5Answers: 1

    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.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    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

This discussion has been closed.