Michael Bayer <[EMAIL PROTECTED]> writes: > On Jun 13, 2007, at 6:13 AM, David Bolen wrote: > >> * I want to remove the association between "File Common" and "Job 1" >> but without affecting "Job 2". >> >> If I session.delete() the fc instance directly, SA purges the file >> completely, including links to both jobs. I can understand SA >> thinking I want the file completely gone in this scenario. >> >> But if I remove the fc instance from the relation list (files) from >> either job, SA also fully purges fc, including the link to the other >> job. This includes the case of deleting one of the jobs if I have >> the cascade on the files relation including "delete". This would >> seem to prevent me from using a delete cascade, since then deleting >> any job would remove files it contains from all other jobs also >> containing those files which sort of defeats the purpose (at least >> for me) of the many to many relationship. > > If I add this code to the bottom: > > del j1.files[0] > s.flush()
(...) > the second flush is: > > BEGIN > DELETE FROM jobs_files WHERE jobs_files.job_id = ? AND > jobs_files.file_id = ? > [1, 1] > COMMIT > > association is deleted only. Argh - my bad. I should have left a cascade option commented in the example, since it needs to change slightly to show different behaviors. With the default cascade (as in the example as posted, just save-update), I also see the individual removal from the parent object only affects the association. But nothing other than the association is ever touched, and if you remove it from all parents, you end up with an orphaned file (no jobs). E.g., in your case above, file 1 was only associated with job 1, and is now orphaned. If the cascade becomes "save-update, delete-orphan", then I get the behavior where deleting from a single parent job flushes all associations for the file. Although its true you never get an orphan that way, files get orphaned "before their time". So, adding the cascade, and then changing your line to del j1.files[1] (to delete the common file), I get on the second flush: BEGIN DELETE FROM jobs_files WHERE jobs_files.job_id = ? AND jobs_files.file_id = ? [[1, 2], [2, 2]] DELETE FROM files WHERE files.id = ? [2] COMMIT which is removing the common file association with both jobs, and then removing the file itself. It's true that after doing the former, it's an orphan which can be deleted, but I don't know why it's including the second job in the association pruning. So I was either getting orphans, or completely removed files when trying to remove a single association. I can't seem to get the two desired behaviors together? On the second point, the complexity of the full cascade recursion with orphan detection makes sense. I suppose I'm interested in any input from anyone else as to how they are handling these sorts of operations in many-to-many cases with changing associations. -- David --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
