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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/cdcecf3a-3e11-441f-bd96-3216d76ffc42n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/cdcecf3a-3e11-441f-bd96-3216d76ffc42n%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/107d646b-24e4-44d7-8bd1-e2c3352e9d01%40www.fastmail.com.
