well if only one relationship() has the foreign(Transaction.schema), then it doesn't conflict with anything else.
I'd still take it off though for consistency On Mon, Dec 28, 2020, at 1:34 PM, sector119 wrote: > Thanks! > > service = relationship(*'Service'*, primaryjoin=*'and_(Service.schema == > Transaction.schema, Service.id == foreign(Transaction.service_id))'*) > eliminates the error > > But why I have no need to do the same with organization = relationship(...) ? > > Because Service model references Organization one ? > > понедельник, 28 декабря 2020 г. в 19:53:24 UTC+2, Mike Bayer: >> >> >> On Mon, Dec 28, 2020, at 12:37 PM, sector119 wrote: >>> Thank You, Mike, >>> >>> Do you mean that I have to remove all foreign() annotations from all my >>> relationships like this? >>> service = relationship(*'Service'*, primaryjoin=*'and_(Service.schema == >>> Transaction.schema, Service.id == Transaction.service_id)'*) >>> organization = relationship(*'Organization'*, >>> primaryjoin=*'and_(Organization.schema == Transaction.schema, >>> Organization.id == Transaction.organization_id)'*) >>> person = relationship(*'Person'*, primaryjoin=*'and_(Person.schema == >>> Transaction.schema, Person.id == Transaction.person_id)'*) >> >> >> no, only the foreign() annotation that surrounds "Transaction.schema". >> leave the one that surrounds "Transaction.service_id". that should >> eliminate the error. >> >> >> >>> >>> if so, I still get the same warning.. >>> >>> And must I set primaryjoin for service and organization relationships at >>> all if I've set up FK for them >>> ForeignKeyConstraint( >>> (schema, service_id), >>> (Service.schema, Service.id) >>> ) >>> and >>> ForeignKeyConstraint( >>> (schema, organization_id), >>> (Organization.schema, Organization.id) >>> ), >>> >>> >>> понедельник, 28 декабря 2020 г. в 17:39:25 UTC+2, Mike Bayer: >>>> __ >>>> 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 sqlalchemy+...@googlegroups.com. >>>>> 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 sqlalchemy+...@googlegroups.com. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/sqlalchemy/705259fe-7ac2-4905-a88d-ca2af34b2886n%40googlegroups.com >>> >>> <https://groups.google.com/d/msgid/sqlalchemy/705259fe-7ac2-4905-a88d-ca2af34b2886n%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 sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/9fd0fb5d-7b60-45d3-ac3a-dca438fd7a8bn%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/9fd0fb5d-7b60-45d3-ac3a-dca438fd7a8bn%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 sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/60ef139f-a674-4f19-ba3e-6803172c5df0%40www.fastmail.com.
