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+unsubscr...@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/9461aa59-7bb4-404d-aee3-0b1f2381a591%40www.fastmail.com.
