by having foreign() on the Transaction.schema column, that means when you do this:
t1 = Transaction() t1.service = some_service() the ORM is being instructed to copy some_service.schema over to t1.schema. Because "foreign" means "this is the column that mirrors the value of a canonical value on the related row". so you have to decide what should happen if you did this: t1.service = Service(schema="a", ...) t1.organiazation = Organization(schema="b", ...) t1.person = Person(schema="c", ...) is the above possible? or an error condition? overall, if the plan is that "schema" will match across all the objects involved, and your application will make sure those are all set as needed, just remove the foreign() annotation from the Transaction.service column. the primary joins already have enough information based on the service_id, organization_id and person_id columns. On Mon, Dec 28, 2020, at 6:04 AM, sector119 wrote: > I get following warning with my model: > > SAWarning: relationship 'Transaction.organization' will copy column > organizations.schema to column transactions.schema, which conflicts with > relationship(s): 'Transaction.service' (copies services.schema to > transactions.schema). 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. The 'overlaps' parameter may be used to remove this > warning. > > I have all my tables partitioned by "schema" column so I have to put it to > every PK and FK > I add "primaryjoin" to my relations as described at > https://docs.sqlalchemy.org/en/14/orm/join_conditions.html#overlapping-foreign-keys > but still get that warning. How can I fix it? > > Thank You > > class Transaction(Base): > __tablename__ = *'transactions'* > * *schema = Column(String(63), nullable=False, index=True) > id = Column(BigInteger, nullable=False, index=True) > > user_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.users.id'*), > nullable=False, index=True) > office_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.offices.id'*), > nullable=False, index=True) > service_id = Column(Integer, nullable=False, index=True) > organization_id = Column(Integer, nullable=False, index=True) > > ... > > service = relationship(*'Service'*, > primaryjoin=*'and_(Service.schema == foreign(Transaction.schema), Service.id > == foreign(Transaction.service_id))'*) > organization = relationship(*'Organization'*, > primaryjoin=*'and_(Organization.schema == foreign(Transaction.schema), > Organization.id == foreign(Transaction.organization_id))'*) > person = relationship(*'Person'*, > primaryjoin=*'and_(Person.schema == foreign(Transaction.schema), Person.id == > foreign(Transaction.person_id))'*) > rollback_user = relationship(*'User'*, primaryjoin=*'User.id == > Transaction.rollback_user_id'*) > > __table_args__ = ( > PrimaryKeyConstraint(*'schema'*, *'id'*), > ForeignKeyConstraint( > (schema, service_id), > (Service.schema, Service.id) > ), > ForeignKeyConstraint( > (schema, organization_id), > (Organization.schema, Organization.id) > ), > { > *'postgresql_partition_by'*: *'LIST (schema)' ** *} > ) > > -- > 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/0d422feb-956f-4efc-b3c0-17473652c603n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/0d422feb-956f-4efc-b3c0-17473652c603n%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/5166f726-10d9-4861-9c0c-c0606fee22c8%40www.fastmail.com.
