Re: [sqlalchemy] Overlapping FK problem

[Mike Bayer]

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 

Re: [sqlalchemy] Overlapping FK problem

[sector119]

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, 

Re: [sqlalchemy] Overlapping FK problem

[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: 

Re: [sqlalchemy] Overlapping FK problem

[sector119]

>>> is the above possible?  or an error condition?

I don't want to restrict that case 

>>> 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.

"schema" will match across all the objects - 100%



понедельник, 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
>  
> 
> .
>
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, 

Re: [sqlalchemy] Overlapping FK problem

[sector119]

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)')

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 
> 

Re: [sqlalchemy] Overlapping FK problem

[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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/0d422feb-956f-4efc-b3c0-17473652c603n%40googlegroups.com
>  
> .

-- 
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/5166f726-10d9-4861-9c0c-c0606fee22c8%40www.fastmail.com.

Re: [sqlalchemy] Multitenancy, filter selects, updates and deletes in do_orm_execute events

[Mike Bayer]

yeah that's great

with_loader_criteria is going to be great


On Mon, Dec 28, 2020, at 1:18 AM, Frédéric CLEMENT wrote:
> Hi Mike,
> 
> In the meantime, I tested like that and it works like a charm :
> 
> if orm_execute_state.is_update:
> mapper = orm_execute_state.bind_arguments['mapper']
> scope = mapper.entity.__name__
> rights = check_rights(session, infos, role, get_scope(scope))
> orm_execute_state.statement = orm_execute_state.statement.options(
> with_loader_criteria(
> mapper.entity,
> mapper.entity.id.in_(rights),
> include_aliases=True
> )
> )
> return
> 
> Le dimanche 27 décembre 2020 à 11:31:41 UTC+1, Frédéric CLEMENT a écrit :
>> Hi Mike,
>> Many thanks for your reply, giving a try to it as soon as it will be 
>> released.
>> 
>> 
>> 
>> Le mercredi 23 décembre 2020 à 16:46:53 UTC+1, Mike Bayer a écrit :
>>> __
>>> In a little while the git master will have new accessors:
>>> 
>>> ORMExecuteState.bind_mapper
>>> ORMExecuteState.all_mappers
>>> 
>>> so that you can reliably get at the Mapper objects that are the target of 
>>> an ORM-enabled select(), update() , or delete()
>>> 
>>> see https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2432 for the 
>>> patch going through testing now.
>>> 
>>> 
>>> 
>>> On Wed, Dec 23, 2020, at 10:16 AM, Mike Bayer wrote:
 First off I admire your motivation to make that happen, that's all new 
 stuff and note there's a lot of fixes to the with_loader_criteria() thing 
 that are sitting in github master right now that will be in 1.4.0b2.
 
 The good news is that update() and delete() both support options as well 
 so feel free to use with_loader_criteria() with these constructs as well, 
 using the 2.0-style execution described at 
 https://docs.sqlalchemy.org/en/14/orm/session_basics.html#update-and-delete-with-arbitrary-where-clause
  .
 
 it might be nice for us to add a universal "mappers" accessor to 
 ORMExecuteState but for the moment you can likely work for all statements 
 generically as:
 
 if orm_execute_state.is_orm_statement:
 mapper = orm_execute_state.bind_arguments['mapper']
 cls = mapper.class_
 
 see if that works out and we will likely need to add an accessor that 
 guarantees being able to provide the target "mapper" and/or mappers for 
 any operation.
 
 
 
 
 
 On Wed, Dec 23, 2020, at 4:54 AM, Frédéric CLEMENT wrote:
> Hi the list,
> 
> 
> Version 1.4Beta (hopefully 2.0 very soon)
> 
> I am writing a multitenant application and I need to filter all requests 
> using subqueries.
> I succeeded to do it for selects doing something like that :
> 
> *for desc in orm_execute_state.statement.column_descriptions:
> t = desc['type']
> classname = desc['name']
> scope = get_scope(classname)
> 
> if scope != None:
> rights = check_rights(session, infos, role, scope)
> orm_execute_state.statement = orm_execute_state.statement.options(
> with_loader_criteria(
> t,
> t.id.in_(rights),
> include_aliases=True
> )
> )*
> 
> But I didn't find out how to do it for updates and deletes.
> If some of yours have an idea  :)
> 
> Thanks a lot,
> 
> Fred
> 
> 
> 

> -- 
> 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/69ab301d-e223-4b5d-9e37-756ce5b04801n%40googlegroups.com
>  
> .
 
 

 -- 
 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/7c47c079-bca5-44de-aea2-40d8eb708c7b%40www.fastmail.com
  
 

[sqlalchemy] Overlapping FK problem

[sector119]

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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/0d422feb-956f-4efc-b3c0-17473652c603n%40googlegroups.com.