Standard M:N secondary association table relationship pattern warnings

[Sanchay]

Suppose I have the standard M:N association table pattern, like so

class Xs(ModelBase):
    id = Column(Integer, primary_key=True)
   
    ys = relationship('Ys', secondary='test.xy_assoc', back_populates='xs')

class Ys(ModelBase):
    id = Column(Integer, primary_key=True)

    xs = relationship('Xs', secondary='test.xy_assoc', back_populates='ys')

class XY_assoc(ModelBase):
    x_id = Column(Integer, ForeignKey(Xs.id), primary_key=True)
    y_id = Column(Integer, ForeignKey(Ys.id), primary_key=True)

Everything is fine and works. But now I want to add a few extra relationships to the association table itself:

class Xs(ModelBase):
    id = Column(Integer, primary_key=True)
   
    ys = relationship('Ys', secondary='test.xy_assoc', back_populates='xs')
    xy_assocs = relationship('XY_assoc', back_populates='x')

class Ys(ModelBase):
    id = Column(Integer, primary_key=True)

    xs = relationship('Xs', secondary='test.xy_assoc', back_populates='ys')
    xy_assocs = relationship('XY_assoc', back_populates='y')

class XY_assoc(ModelBase):
    x_id = Column(Integer, ForeignKey(Xs.id), primary_key=True)
    y_id = Column(Integer, ForeignKey(Ys.id), primary_key=True)
   
    x = relationship('Xs', back_populates='xy_assocs')
    y = relationship('Ys', back_populates='xy_assocs')

With this, I get the following SQLA warnings. What is the right way to achieve this?

/var/folders/lk/x4t5tp_n28qgpyp14fr6r5700000gn/T/ipykernel_74888/982636505.py:2: SAWarning: relationship 'Xs.xy_assocs' will copy column xs.id to column xy_assoc.x_id, which conflicts with relationship(s): 'Xs.ys' (copies xs.id to xy_assoc.x_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="ys"' to the 'Xs.xy_assocs' relationship. xs = session.query(Xs).all() /var/folders/lk/x4t5tp_n28qgpyp14fr6r5700000gn/T/ipykernel_74888/982636505.py:2: SAWarning: relationship 'Ys.xs' will copy column xs.id to column xy_assoc.x_id, which conflicts with relationship(s): 'Xs.xy_assocs' (copies xs.id to xy_assoc.x_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="xy_assocs"' to the 'Ys.xs' relationship. xs = session.query(Xs).all() /var/folders/lk/x4t5tp_n28qgpyp14fr6r5700000gn/T/ipykernel_74888/982636505.py:2: SAWarning: relationship 'Ys.xy_assocs' will copy column ys.id to column xy_assoc.y_id, which conflicts with relationship(s): 'Xs.ys' (copies ys.id to xy_assoc.y_id), 'Ys.xs' (copies ys.id to xy_assoc.y_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="xs,ys"' to the 'Ys.xy_assocs' relationship. xs = session.query(Xs).all() /var/folders/lk/x4t5tp_n28qgpyp14fr6r5700000gn/T/ipykernel_74888/982636505.py:2: SAWarning: relationship 'XY_assoc.x' will copy column xs.id to column xy_assoc.x_id, which conflicts with relationship(s): 'Xs.ys' (copies xs.id to xy_assoc.x_id), 'Ys.xs' (copies xs.id to xy_assoc.x_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="xs,ys"' to the 'XY_assoc.x' relationship. xs = session.query(Xs).all() /var/folders/lk/x4t5tp_n28qgpyp14fr6r5700000gn/T/ipykernel_74888/982636505.py:2: SAWarning: relationship 'XY_assoc.y' will copy column ys.id to column xy_assoc.y_id, which conflicts with relationship(s): 'Xs.ys' (copies ys.id to xy_assoc.y_id), 'Ys.xs' (copies ys.id to xy_assoc.y_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="xs,ys"' to the 'XY_assoc.y' relationship. xs = session.query(Xs).all()

[Sanchay]

Adding a bunch of 'overlaps' remove the warnings , is this the right way?

class Xs(ModelBase):
    id = Column(Integer, primary_key=True)
   

    ys = relationship('Ys', secondary='test.xy_assoc', back_populates='xs', overlaps='xy_assocs')
    xy_assocs = relationship('XY_assoc', back_populates='x', overlaps='ys')

class Ys(ModelBase):
    id = Column(Integer, primary_key=True)

    xs = relationship('Xs', secondary='test.xy_assoc', back_populates='ys', overlaps='xy_assocs')
    xy_assocs = relationship('XY_assoc', back_populates='y', overlaps='xs')

   
class XY_assoc(ModelBase):
    x_id = Column(Integer, ForeignKey(Xs.id), primary_key=True)
    y_id = Column(Integer, ForeignKey(Ys.id), primary_key=True)
   

    x = relationship('Xs', back_populates='xy_assocs', overlaps='xs,ys')
    y = relationship('Ys', back_populates='xy_assocs', overlaps='xs,ys')

[Mike Bayer]

apply viewonly=True to those relationships that you aren't going to be append()'ing towards.

the warnings are trying to indicate that if you have a certain Xs, and you append a certain Ys to it, but at the same time, you also create an XY_assoc object with those same Xs/Ys and also append that to Xs.xy_assoc, youll get a primary key violation error.  because you are inserting the same row twice in different ways.   the ORM doesn't track that.

--

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/0aed6514-494b-4cb3-a17a-a821c7c3a92fn%40googlegroups.com.