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
> <http://users.id>'*), nullable=False, index=True)
> office_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.offices.id
> <http://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/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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/9fd0fb5d-7b60-45d3-ac3a-dca438fd7a8bn%40googlegroups.com.
