many-to-many orm warnings

[Michael Merickel]

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 Merickel]

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) 

--

Michael

[Michael Merickel]

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.

--

Michael

[Michael Merickel]

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',
    )

--

Michael

[Mike Bayer]

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

--

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/CAKdhhwEG%2BE8kY2mMW1R%3D-Mi%3DnevYyR%2BAfftrqEzFyVsz%2BG23Nw%40mail.gmail.com.

[Michael Merickel]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/df6b41d7-4cf3-4d65-bbce-ef2b4a3b7fb8%40www.fastmail.com.

--

- Michael

[Jonathan Vanasco]

I'm sorry you're getting bit by this messaging - but also glad that I'm not the only one.  This got me a while ago too.

SqlAlchemy just uses a bare field name when emitting the warning and accepting the `overlaps` arguments. In more complex models with 3+ tables that have standardize relationship names, it's hard to tell what caused the issue and fixing one relationship can unknowingly affect others.

There is a related ticket/PR. I'm not sure if you can pull it against the current main branch, but you can do a manual patch of the warnings code locally to make the output better:

    https://github.com/sqlalchemy/sqlalchemy/issues/7309  - Make the overlaps arguments use fully-qualified names

There's also a related ticket to improve the errors when not calling `configure_mappers` as above: https://github.com/sqlalchemy/sqlalchemy/issues/7305