ORM shortcoming? removing an object doesn't remove entries in related many_many tables

[schellmax]

not sure wheter this is a bug - or something well thought-out i just don't understand.

for many_many relations, a table is created containing the ids for both sides. 

now, whenever an object on one side of the relation is deleted, the corresponding entries in the many_many table(s) will still exist.

it seems as though framework internal methods are smart enough to skip these entries (they are e.g. ignored when accessing something like SomeClass->SomeRelatedObjects()); nonetheless one has to be very careful when writing custom queries (and the db ends up containing invalid entries).

from my (surely naive) point of view it shouldn't be too hard to 'automagically' remove those entries whenever an object is deleted (i started doing this in my onBeforeDelete methods).

do i miss something here? or could this be an 'enhancement' ticket?

[Uncle Cheese]

I suppose, but I'm not really sure why it matters. Those tables just map the ID of one object to the ID of another. If one gets deleted, the record is orphaned. There's a 0% chance that it would ever populate a new object with its data.

Maybe just for tidiness?

[Nicolaas Thiemen Francken - Sunny Side Up]

On 22 August 2012 13:36, Uncle Cheese <aaronc...@gmail.com> wrote:
>
> I suppose, but I'm not really sure why it matters. Those tables just map the ID of one object to the ID of another. If one gets deleted, the record is orphaned. There's a 0% chance that it would ever populate a new object with its data.

I'd have to agree with Matthias.  Here are some reasons:
- because it allows you to do custom queries on the many_2_many table without getting false results - e.g.
  - how many relationships are there
  - custom queries that you need to speed up large applications
     - e.g. because you may add a rule "only allow deletion of object A if there are no objects linking to object A" (e.g. only allow a product to be deleted if it . In order to speed up this rule you write something like: "DB::query("Select COUNT(ID) from ManyManyTable WHERE Relation.ID = objectAID")->value(); ... This will return false positives if there are relationships between A and deleted objects. 
- faster
- cleaner

In the e-commerce application I had to add a number of hacks to clean-up the many_2_many tables as without the clean-up I was getting false results.

I am curious to hear what other people think about this.

Cheers

Nicolaas

[Simon J Welsh]

If you're doing manual queries, you're most likely not using the ORM correctly. $this->RelationName()->Count() runs a count() on the database, that also includes the join so deleted objects aren't counted.

> --
> You received this message because you are subscribed to the Google Groups "SilverStripe Core Development" group.
> To post to this group, send email to silverst...@googlegroups.com.
> To unsubscribe from this group, send email to silverstripe-d...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/silverstripe-dev?hl=en.
>

---
Simon Welsh
Admin of http://simon.geek.nz/

[Nicolaas Thiemen Francken - Sunny Side Up]

On 22 August 2012 20:48, Simon J Welsh <welsh...@gmail.com> wrote:

If you're doing manual queries, you're most likely not using the ORM correctly. $this->RelationName()->Count() runs a count() on the database, that also includes the join so deleted objects aren't counted.

Yes, that is true, but to optimize large applications you can sometimes save time by running DB::queries. You could improve such a query by including the join (in which case you get more reliable results), but you might not think of it and  I guess my main point is that you would expect "outdated" relationships to be "removed" - what are the reasons for not doing so?

[Ingo Schommer]

I don't think that there's any explicit reason those obsolete relationships are kept,

other than that nobody considered it a high enough priority to implement.

But I agree with Matthias and Nicolaas, the ORM should keep the data internally consistent,

so remove relationship entries from the many-many tables upon object deletion.

Anybody keen to implement that, with thorough unit testing?

[Nicolaas Thiemen Francken - Sunny Side Up]

If someone likes to do it with me then I would be happy to help.

Nicolaas

[Hamish Friedlander]

I agree that we should be keeping the data internally consistent.

I think we can just do it by making the two references in the join table foreign keys with a ON DELETE CASCASE rule can't we? No need for any PHP side handling.

We're not very good with using SQL constraints at the moment. Other situations where this could help: deleting other tables from a MTI structure when deleting the base row, automatically deleting children of a SiteTree element to avoid orphaned nodes in the tree, etc.

Hamish Friedlander 

[Nicolaas Thiemen Francken - Sunny Side Up]

On 23 August 2012 09:08, Hamish Friedlander <ham...@silverstripe.com> wrote:
> I agree that we should be keeping the data internally consistent.
>
> I think we can just do it by making the two references in the join table
> foreign keys with a ON DELETE CASCASE rule can't we? No need for any PHP
> side handling.

By using PHP rather than MySQL we stay more "database neutral"?

The Hierarchy class should delete the "child pages?" or do something
else with them?

[Dan Rye]

I believe all of the DBs support constraints, at least the basic ones like this.