Thank you Mike. Really appreciate you unpacking my rambling. This works for me. I found a few spots in our codebase where we were relying on append() working because it really was a simple link table but I rewrote them to just create the link manually and add it to the session which also causes them to appear in the lists.
On Thu, Mar 10, 2022 at 9:17 AM Mike Bayer <mike...@zzzcomputing.com> wrote: > hey there. > > The warnings go away entirely by making Parent.children viewonly=True, > which for this type of mapping is recommended: > > class Parent(Base): > __tablename__ = "left" > id = Column(Integer, primary_key=True) > children = relationship( > "Child", secondary=Association.__table__, backref="parents", > viewonly=True > > ) > > > you wouldn't want to append new records to Parent.children because that > would create invalid Association rows (missing extra_data). > > The warning box at the end of > https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#association-object > discusses this situation and the desirability of making the relationship > which includes "secondary" as viewonly=True. > > hope this helps > > > On Wed, Mar 9, 2022, at 8:09 PM, Michael Merickel wrote: > > Sorry for the rambling, it's been difficult for me to figure out what > question to ask because I'm so confused. Below is the minimum viable > example that produces no warnings with respect to the overlaps flags and I > cannot explain hardly any of them. For example, why does Child.parents > require "child_links,parent,child"? 3 values that seem to be somewhat > unrelated and are at the very least definitely on different models? > > class Association(Base): > __tablename__ = 'association' > left_id = Column(ForeignKey('left.id'), primary_key=True) > right_id = Column(ForeignKey('right.id'), primary_key=True) > extra_data = Column(String(50)) > > parent = relationship('Parent', back_populates='child_links') > child = relationship('Child', back_populates='parent_links') > > class Parent(Base): > __tablename__ = 'left' > id = Column(Integer, primary_key=True) > > children = relationship( > 'Child', > secondary=Association.__table__, > back_populates='parents', > overlaps='child,parent', > ) > child_links = relationship( > 'Association', > back_populates='parent', > overlaps='children', > ) > > class Child(Base): > __tablename__ = 'right' > id = Column(Integer, primary_key=True) > > parents = relationship( > 'Parent', > secondary=Association.__table__, > back_populates='children', > overlaps='child_links,parent,child', > ) > parent_links = relationship( > 'Association', > back_populates='child', > overlaps='children,parents', > ) > > > On Wed, Mar 9, 2022 at 4:50 PM Michael Merickel <mmeri...@gmail.com> > wrote: > > I think ultimately I want the overlaps config but reading through > https://docs.sqlalchemy.org/en/14/errors.html#relationship-x-will-copy-column-q-to-column-p-which-conflicts-with-relationship-s-y > it doesn't make any sense to me what the values in the overlaps= argument > are referring to. For example in last snippet that was simpler, what is > overlaps='parent' referring to? Neither the Parent object, nor the Child > object has something named "parent" so other than blinding trusting the > warning I'm unclear how to see what the mapper is building that conflicts > here. > > On Wed, Mar 9, 2022 at 4:33 PM Michael Merickel <mmeri...@gmail.com> > wrote: > > It's probably worth noting I can narrow it down to a single warning with > the following snippet and it's still unclear to me how to resolve this: > > class Association(Base): > __tablename__ = 'association' > left_id = Column(ForeignKey('left.id'), primary_key=True) > right_id = Column(ForeignKey('right.id'), primary_key=True) > extra_data = Column(String(50)) > > parent = relationship('Parent') > > class Parent(Base): > __tablename__ = 'left' > id = Column(Integer, primary_key=True) > children = relationship('Child', secondary=Association.__table__) > > class Child(Base): > __tablename__ = 'right' > id = Column(Integer, primary_key=True) > > foo.py:24: SAWarning: relationship 'Parent.children' will copy column > left.id to column association.left_id, which conflicts with > relationship(s): 'Association.parent' (copies left.id to > association.left_id). If this is not the intention, consider if these > relationships should be linked with back_populates, or if viewonly=True > should be applied to one or more if they are read-only. For the less common > case that foreign key constraints are partially overlapping, the > orm.foreign() annotation can be used to isolate the columns that should be > written towards. To silence this warning, add the parameter > 'overlaps="parent"' to the 'Parent.children' relationship. (Background on > this error at: https://sqlalche.me/e/14/qzyx) > > > On Wed, Mar 9, 2022 at 4:31 PM Michael Merickel <mmeri...@gmail.com> > wrote: > > I have looked at the couple examples in the docs (many-to-many, and > association table) and have noticed that my codebase has a slightly > different pattern which is causing warnings when upgrading to 1.4. I'm > trying to figure out the best pattern to accomplish what I've been doing > which doesn't match the docs exactly. > > In the below example you can see that there are backrefs on all of the > links, and that there are backrefs from the link table to the related > objects, as well as a secondary link from Parent to Child via > Parent.children and Child.parents. > > There seem to be several options and I'm struggling to figure out what the > solution should be to maintain the behavior with all of the following > relationships working: > > - Parent.children > - Parent.child_links > - Child.parents > - Child.parent_links > - Association.parent > - Association.child > > Code and warnings are below: > > from sqlalchemy import Column, ForeignKey, String, Integer > from sqlalchemy.orm import configure_mappers, relationship > from sqlalchemy.ext.declarative import declarative_base > > Base = declarative_base() > > class Association(Base): > __tablename__ = 'association' > left_id = Column(ForeignKey('left.id'), primary_key=True) > right_id = Column(ForeignKey('right.id'), primary_key=True) > extra_data = Column(String(50)) > > parent = relationship('Parent', backref='child_links') > child = relationship('Child', backref='parent_links') > > class Parent(Base): > __tablename__ = 'left' > id = Column(Integer, primary_key=True) > children = relationship('Child', secondary=Association.__table__, > backref='parents') > > class Child(Base): > __tablename__ = 'right' > id = Column(Integer, primary_key=True) > > configure_mappers() > > foo.py:25: SAWarning: relationship 'Child.parents' will copy column > right.id to column association.right_id, which conflicts with > relationship(s): 'Association.child' (copies right.id to > association.right_id), 'Child.parent_links' (copies right.id to > association.right_id). If this is not the intention, consider if these > relationships should be linked with back_populates, or if viewonly=True > should be applied to one or more if they are read-only. For the less common > case that foreign key constraints are partially overlapping, the > orm.foreign() annotation can be used to isolate the columns that should be > written towards. To silence this warning, add the parameter > 'overlaps="child,parent_links"' to the 'Child.parents' relationship. > (Background on this error at: https://sqlalche.me/e/14/qzyx) > configure_mappers() > foo.py:25: SAWarning: relationship 'Child.parents' will copy column > left.id to column association.left_id, which conflicts with > relationship(s): 'Association.parent' (copies left.id to > association.left_id), 'Parent.child_links' (copies left.id to > association.left_id). If this is not the intention, consider if these > relationships should be linked with back_populates, or if viewonly=True > should be applied to one or more if they are read-only. For the less common > case that foreign key constraints are partially overlapping, the > orm.foreign() annotation can be used to isolate the columns that should be > written towards. To silence this warning, add the parameter > 'overlaps="child_links,parent"' to the 'Child.parents' relationship. > (Background on this error at: https://sqlalche.me/e/14/qzyx) > configure_mappers() > foo.py:25: SAWarning: relationship 'Parent.children' will copy column > left.id to column association.left_id, which conflicts with > relationship(s): 'Association.parent' (copies left.id to > association.left_id), 'Parent.child_links' (copies left.id to > association.left_id). If this is not the intention, consider if these > relationships should be linked with back_populates, or if viewonly=True > should be applied to one or more if they are read-only. For the less common > case that foreign key constraints are partially overlapping, the > orm.foreign() annotation can be used to isolate the columns that should be > written towards. To silence this warning, add the parameter > 'overlaps="child_links,parent"' to the 'Parent.children' relationship. > (Background on this error at: https://sqlalche.me/e/14/qzyx) > configure_mappers() > foo.py:25: SAWarning: relationship 'Parent.children' will copy column > right.id to column association.right_id, which conflicts with > relationship(s): 'Association.child' (copies right.id to > association.right_id), 'Child.parent_links' (copies right.id to > association.right_id). If this is not the intention, consider if these > relationships should be linked with back_populates, or if viewonly=True > should be applied to one or more if they are read-only. For the less common > case that foreign key constraints are partially overlapping, the > orm.foreign() annotation can be used to isolate the columns that should be > written towards. To silence this warning, add the parameter > 'overlaps="child,parent_links"' to the 'Parent.children' relationship. > (Background on this error at: https://sqlalche.me/e/14/qzyx) > configure_mappers() > > > Thanks! > > -- > > Michael > > > > -- > > Michael > > > > -- > > Michael > > > > -- > > Michael > > > -- > 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+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CAKdhhwEG%2BE8kY2mMW1R%3D-Mi%3DnevYyR%2BAfftrqEzFyVsz%2BG23Nw%40mail.gmail.com > <https://groups.google.com/d/msgid/sqlalchemy/CAKdhhwEG%2BE8kY2mMW1R%3D-Mi%3DnevYyR%2BAfftrqEzFyVsz%2BG23Nw%40mail.gmail.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 sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/df6b41d7-4cf3-4d65-bbce-ef2b4a3b7fb8%40www.fastmail.com > <https://groups.google.com/d/msgid/sqlalchemy/df6b41d7-4cf3-4d65-bbce-ef2b4a3b7fb8%40www.fastmail.com?utm_medium=email&utm_source=footer> > . > -- - Michael -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAKdhhwHhNsYO6y8v5kUwVUB%3Dgeh%3DaubLL-3GqLsCKSLJZiK4mw%40mail.gmail.com.
