I am encountering an SAWarning related to overlapping relationships in SQLAlchemy. The warning message is as follows:
SAWarning: relationship 'Grupo.users' will copy column ominicontacto_app_user.id to column ominicontacto_app_agenteprofile.user_id, which conflicts with relationship(s): 'ActividadAgenteLog.user' (copies ominicontacto_app_user.id to ominicontacto_app_agenteprofile.user_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="user"' to the 'Grupo.users' relationship.
I have the following code that defines the relationships between my models:
from datetime import datetime
from sqlalchemy import BigInteger, ForeignKey
from sqlalchemy.orm import mapped_column, DeclarativeBase, Mapped, relationship
from typing import List, Optional
class Base(DeclarativeBase):
type_annotation_map = {
int: BigInteger
}
class ActividadAgenteLog(Base):
__tablename__ = 'reportes_app_actividadagentelog'
agente_id: Mapped[int] = mapped_column(ForeignKey('ominicontacto_app_agenteprofile.id'))
event: Mapped[str]
id: Mapped[int] = mapped_column(primary_key=True)
pausa_id: Mapped[Optional[str]]
time: Mapped[datetime]
user: Mapped['User'] = relationship('User', secondary='ominicontacto_app_agenteprofile', back_populates='actividades', uselist=False)
def __repr__(self):
return f'<ActividadAgente(id={self.id})>'
class AgenteProfile(Base):
__tablename__ = 'ominicontacto_app_agenteprofile'
borrado: Mapped[bool]
estado: Mapped[int]
grupo_id: Mapped[int] = mapped_column(ForeignKey('ominicontacto_app_grupo.id'))
id: Mapped[int] = mapped_column(primary_key=True)
is_inactive: Mapped[bool]
reported_by_id: Mapped[int]
sip_extension: Mapped[int]
sip_password: Mapped[Optional[str]]
user_id: Mapped[int] = mapped_column(ForeignKey('ominicontacto_app_user.id'))
def __repr__(self):
return f'<AgenteProfile(id={self.id})>'
class Grupo(Base):
__tablename__ = 'ominicontacto_app_grupo'
acceso_agendas_agente: Mapped[bool]
acceso_calificaciones_agente: Mapped[bool]
acceso_campanas_preview_agente: Mapped[bool]
acceso_contactos_agente: Mapped[bool]
acceso_dashboard_agente: Mapped[bool]
acceso_grabaciones_agente: Mapped[bool]
auto_attend_dialer: Mapped[bool]
auto_attend_inbound: Mapped[bool]
auto_unpause: Mapped[int]
call_off_camp: Mapped[bool]
cantidad_agendas_personales: Mapped[Optional[int]]
conjunto_de_pausa_id: Mapped[Optional[int]]
id: Mapped[int] = mapped_column(primary_key=True)
limitar_agendas_personales: Mapped[bool]
limitar_agendas_personales_en_dias: Mapped[bool]
nombre: Mapped[str]
obligar_calificacion: Mapped[bool]
obligar_despausa: Mapped[bool]
on_hold: Mapped[bool]
show_console_timers: Mapped[bool]
tiempo_maximo_para_agendar: Mapped[Optional[int]]
whatsapp_habilitado: Mapped[bool]
users: Mapped[List['User']] = relationship('User', secondary='ominicontacto_app_agenteprofile', back_populates='grupos')
def __repr__(self):
return f'<Grupo(id={self.id})>'
class LlamadaLog(Base):
__tablename__ = 'reportes_app_llamadalog'
agente_extra_id: Mapped[Optional[int]]
agente_id: Mapped[int] = mapped_column(ForeignKey('ominicontacto_app_agenteprofile.id'))
archivo_grabacion: Mapped[Optional[str]]
bridge_wait_time: Mapped[Optional[int]]
callid: Mapped[str]
campana_extra_id: Mapped[Optional[int]]
campana_id: Mapped[int]
contacto_id: Mapped[Optional[int]]
duracion_llamada: Mapped[int]
event: Mapped[str]
id: Mapped[int] = mapped_column(primary_key=True)
numero_extra: Mapped[Optional[str]]
numero_marcado: Mapped[Optional[str]]
time: Mapped[datetime]
tipo_campana: Mapped[int]
tipo_llamada: Mapped[int]
def __repr__(self):
return f'<LlamadaLog(id={self.id})>'
class User(Base):
__tablename__ = 'ominicontacto_app_user'
borrado: Mapped[bool]
date_joined: Mapped[datetime]
email: Mapped[str]
first_name: Mapped[str]
id: Mapped[int] = mapped_column(primary_key=True)
is_active: Mapped[bool]
is_agente: Mapped[bool]
is_cliente_webphone: Mapped[bool]
is_staff: Mapped[bool]
is_superuser: Mapped[bool]
is_supervisor: Mapped[bool]
last_login: Mapped[datetime]
last_name: Mapped[str]
last_session_key: Mapped[str]
password: Mapped[str]
username: Mapped[str]
actividades: Mapped['ActividadAgenteLog'] = relationship('ActividadAgenteLog', secondary='ominicontacto_app_agenteprofile', back_populates='user')
grupos: Mapped[List['Grupo']] = relationship('Grupo', secondary='ominicontacto_app_agenteprofile', back_populates='users')
def __repr__(self):
return f'<User(id={self.id}, username={self.username})>'
Could anyone help me understand how to resolve this warning?
I tried adding the back_populates arguments to the relationships, but the issue persists and the warning still appears.
Usually when your "secondary" table has extra attributes you should start with an association object and then if you need to make convenience attributes you could add them with viewonly=True otherwise it is hard to access/change those secondary attributes and multiple relationships become hard to reason about. Documentation for association-object
So for example if I insert a User into gupo.users how would an AgenteProfile be created?
So it probably makes more sense to add intermediary relationships like this:
class ActividadAgenteLog(Base):
agente_profile = relationship("AgenteProfile", back_populates="actividads")
class AgenteProfile(Base):
actividads = relationship("ActividadLog", back_populates="agente_profile")
grupo = relationship("Grupo", back_populates="agente_profiles")
user = relationship("User", back_populates="agente_profiles")
class Grupo(Base):
agente_profiles = relationship("AgenteProfile", back_populates="group")
class LlamadaLog(Base):
agente_profile = relationship("AgenteProfile")
class User(Base):
agente_profiles = relationship("AgenteProfile", back_populates="user")
Here is how you would cross the association object to get the actual groups.
# These are not necessarily distinct, you have to apply something either in SQL or afterwards with python to remove duplicates.
some_user_gupos = db_session.scalars(select(Group).join(Group.agente_profiles).where(AgenteProfile.user_id == some_user.id)).all()