Aldjemy many to many modeling

[Ryan Hiebert]

I maintain Aldjemy, a library that converts Django models into SQLAlchemy models. I'm having some trouble figuring out how I should resolve an issue I'm working on, and I could use your help. Here's the link to the issue I'm working on, though hopefully the context I give below will be sufficient.

https://github.com/aldjemy/aldjemy/issues/159

Django models many to many relationships with a ManyToManyField on one side of the relationship, and an automatically added reverse relationship on the other side. Depending on how you configure it, it will either automatically create the intermediate table, or you can manually specify it. Either way, the "through" model, representing the intermediate table, is a fully-functional model, with the appropriate foreign key relationships.

Aldjemy models this currently by creating a relationship for each of the foreign key relationships on the secondary (through) table, as well as a relationship (notably missing the backref, though fixing that isn't my priority right now) that includes the `secondary` argument to make it model the many-to-many relationship.

Starting with SQLAlchemy 1.4, this gives warnings that these relationships conflict, and suggesting that these conflicting relationships either need to have one be read-only, or use the "overlaps" parameter to specify that we know about this, and it's what we intend. However, I think this is a strong indication that this is *not* how we should be modeling this.

Mark over in IRC suggested that he'd model it either by dropping the relationship with the secondary table, or by replacing that relationship with an association proxy. If the either is present, it should give access to model instances, so I wasn't immediately sure if an association proxy would be able to do that, but it does seem like it's possible. It seems from some experiments he did that an association proxy would not be able to be used to do join.

How would you recommend I model this in Aldjemy? I figure whatever I choose is technically going to be a breaking change, so I'd like to choose the wisest option, that most cleanly fits into the patterns that SQLAlchemy users will most readily understand.

Thank you,

Ryan

[Mike Bayer]

On Wed, May 19, 2021, at 7:34 PM, Ryan Hiebert wrote:

I maintain Aldjemy, a library that converts Django models into SQLAlchemy models. I'm having some trouble figuring out how I should resolve an issue I'm working on, and I could use your help. Here's the link to the issue I'm working on, though hopefully the context I give below will be sufficient.

https://github.com/aldjemy/aldjemy/issues/159

there's a description of what this warning means at https://docs.sqlalchemy.org/en/14/errors.html#relationship-x-will-copy-column-q-to-column-p-which-conflicts-with-relationship-s-y

Django models many to many relationships with a ManyToManyField on one side of the relationship, and an automatically added reverse relationship on the other side. Depending on how you configure it, it will either automatically create the intermediate table, or you can manually specify it. Either way, the "through" model, representing the intermediate table, is a fully-functional model, with the appropriate foreign key relationships.

Aldjemy models this currently by creating a relationship for each of the foreign key relationships on the secondary (through) table, as well as a relationship (notably missing the backref, though fixing that isn't my priority right now) that includes the `secondary` argument to make it model the many-to-many relationship.

that is one of the primary conditions that causes this warning as SQLAlchemy cannot guarantee it wont try to write conflicting data across the individual one-to-many relationships vs. the single many-to-many relationship you've configured.  options include setting either the o2m/m2o relationships to viewonly, or the "secondary" relationship to viewonly, or to keep everything the same and silence the warning, add "overlaps" to all of the relationships.  "overlaps" should be given a comma-separated string indicating all the names of other relationships that were noted in the warning as overlapping.

Starting with SQLAlchemy 1.4, this gives warnings that these relationships conflict, and suggesting that these conflicting relationships either need to have one be read-only, or use the "overlaps" parameter to specify that we know about this, and it's what we intend. However, I think this is a strong indication that this is *not* how we should be modeling this.

Well it depends on how the models are used.  The basic issue is that if you have A -> AtoB -> B, and then you create a new AtoB that refers to a certain A and a certain B, and at the same time append that B to A.bs, those two operations represent the same identity in "atobs" but two different paths by which SQLAlchemy will try to INSERT a row, which will produce a constraint conflict.    SQLAlchemy doesn't want this to happen without the user opting in to this limitation, but once you opt in, the issue is just that such a conflict and probably some others can occur that SQLAlchemy doesn't have any mechanism to catch before it gets to the database.

Mark over in IRC suggested that he'd model it either by dropping the relationship with the secondary table, or by replacing that relationship with an association proxy. If the either is present, it should give access to model instances, so I wasn't immediately sure if an association proxy would be able to do that, but it does seem like it's possible. It seems from some experiments he did that an association proxy would not be able to be used to do join.

yes the association proxy is the basic solution to modeling A-> AtoB -> B without using "secondary", and still providing for the ability to query and manipulate from A-> B directly.     At the same time, having a "secondary" in there that is "viewonly" is often easier to work with than the assoc proxy which has some awkwardness involved.

How would you recommend I model this in Aldjemy? I figure whatever I choose is technically going to be a breaking change, so I'd like to choose the wisest option, that most cleanly fits into the patterns that SQLAlchemy users will most readily understand.

It mostly depends on what people need to do.      If people have been fine with the overlapping relationships and not had a problem it's not necessarily a bad thing, otherwise the assoc proxy is pretty common.  But, if there are no significant columns in AtoB other than the A's and B's, I'd probably just go with the single many-to-many relationship.

Thank you,

Ryan

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/cdcecf3a-3e11-441f-bd96-3216d76ffc42n%40googlegroups.com.

[Ryan Hiebert]

On Wednesday, May 19, 2021 at 6:50:57 PM UTC-5 Mike Bayer wrote:

It mostly depends on what people need to do.  If people have been fine with the overlapping relationships and not had a problem it's not necessarily a bad thing, otherwise the assoc proxy is pretty common.  But, if there are no significant columns in AtoB other than the A's and B's, I'd probably just go with the single many-to-many relationship.

Thanks. With this in mind, I think that the general mode of Django is pretty different from SQLAlchemy in this regard. Django users expect different relationships could indeed have conflicts like that, so as you suggest, it might be best to keep the overlapping relationships, so that existing flows don't break.

In the general case I'm working with, I can't know whether there are any additional interesting fields other than the foreign keys on the secondary table. In some cases there are, but in other cases there are not.

One challenge is that I generate these one model at a time. I was originally hoping that I could specify the overlapping relationships only on the many-to-many relationship and solve this case, but my attempt at that did not work the way I'd hoped, and you mentioned needing to add it to all the relationships, so I suspect that this is indeed correct. I suspect I will need to gather all of these conflicts before I create the relationships, and that it may not be possible, or would be bad form, to adjust existing relationships with this after they are created. Does that sound right?

Thanks for all your help, and for the excellent docs on these warnings. While I still have questions, I was able to understand quite a lot of it before I needed to come ask for assistance.

Ryan

[Ryan Hiebert]

So I am able to just add it on the many-to-many relationship, that worked just fine. Unfortunately, I'm now having a similar issue come up when I use `aliased` on a generated model. Do you have any suggestion for how to handle that? It strikes me that either (a) aliased doesn't expect to be working with models with relationships, or (b) aliased really shouldn't be causing this warning, because of course it will be duplicating relationships, right?

[Ryan Hiebert]

And nope. It's coming when I'm running `aliased`, but it doesn't seem to be directly related, even though it's the same warning. I think it has to do with how I'm translating inherited models. Sorry for the false alarm.