On 11/08/2016 08:00 AM, Sebastian Eckweiler wrote:
Hi there,
I'm having an issue when building queries against a relationship using a
custom primaryjoin.
The issue can be reproduced with a slightly modified Users/Address model
as taken from the docs:
|
fromsqlalchemyimportInteger,ForeignKey,String,Column
fromsqlalchemy.ext.declarativeimportdeclarative_base
fromsqlalchemy.ormimportrelationship
Base=declarative_base()
classUser(Base):
__tablename__ ='user'
id =Column(Integer,primary_key=True)
name =Column(String)
address_id =Column(Integer,ForeignKey('address.id'))
boston_address =relationship("BostonAddress",
primaryjoin="and_(User.address_id==Address.id, "
"Address.city=='Boston')")
classAddress(Base):
__tablename__ ='address'
__mapper_args__ ={'polymorphic_on':'type',
'polymorphic_identity':'any'}
id =Column(Integer,primary_key=True)
type =Column(String,primary_key=True)
street =Column(String)
city =Column(String)
state =Column(String)
zip =Column(String)
classBostonAddress(Address):
__mapper_args__ ={'polymorphic_on':'type',
'polymorphic_identity':'Boston'}
|
The actual application uses more than one Address subclass obviously -
but the above suffices for the example.
When trying to query for "Users not having a boston_address" I'd use
|
User.boston_address ==None
|
as a where clause, which however turns out be become:
|
"user".address_id IS NULL AND 'Boston'IS NULL
|
which obvisouly won't yield any results - I'm not sure though, whether
this is a bug or simply due to the maybe unusual model.
I've now implemented a dedicated comparator for this type of
relationship which seems to work.
But still I'm wondering if there's an easier way to fix this, e.g. by
annotating parts of the join condition as "do not touch"...?
well, this is a many-to-one so the usual comparison tries to cut out the
"Address" table totally for the "address == None" equation, which
doesn't work here. We can make it not consider Address.city as one of
the columns to "null out" like this:
primaryjoin="and_(User.address_id==remote(Address.id), "
"Address.city=='Boston')")
and then the expression comes out as:
"user".address_id IS NULL AND address.city = :city_1
{u'city_1': 'Boston'}
which also does not work, because then in a query you get:
SELECT "user".id AS user_id, "user".name AS user_name, "user".address_id
AS user_address_id
FROM "user", address
WHERE "user".address_id IS NULL AND address.city = :city_1
If we are to use this expression as written to produce a user with no
boston_address, the only way to do this "magically" is to use has():
print Session().query(User).filter(~User.boston_address.has())
SELECT "user".id AS user_id, "user".name AS user_name, "user".address_id
AS user_address_id
FROM "user"
WHERE NOT (EXISTS (SELECT 1
FROM address
WHERE "user".address_id = address.id AND address.city = :city_1 AND
address.type IN (:type_1)))
Where you'll notice that has() is also smart enough to get the
polymorphic discriminator in there.
The relational quirk this model is tripping over is that a BostonAddress
would have a primary key that includes "Boston" in the type but you are
not including this in the schema for your User table, that is,
User.address_id == 5 is ambiguous, it could point to address (5,
'Denver') or (5, 'Boston'). In fact a strict database like Postgresql
will disallow such a structure from being created, because this is not a
unique key:
(psycopg2.ProgrammingError) there is no unique constraint matching
given keys for referenced table "address"
[SQL: '\nCREATE TABLE "user" (\n\tid SERIAL NOT NULL, \n\tname
VARCHAR, \n\taddress_id INTEGER, \n\tPRIMARY KEY (id), \n\tFOREIGN
KEY(address_id) REFERENCES address (id)\n)\n\n']
If we used a better relational form like this:
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String(50))
address_id = Column(Integer)
address_type = Column(String)
boston_address = relationship("BostonAddress")
__table_args__ = (
ForeignKeyConstraint(
['address_id', 'address_type'],
['address.id', 'address.type']),
)
now we see the original User.boston_address == None works smoothly (note
no custom primaryjoin needed):
"user".address_id IS NULL AND "user".address_type IS NULL
The way you're doing it is fine if you are stuck with that model, but
the need to create a custom comparator is warranted since you're moving
away from a generic foreign key constraint model.
Cheers
Sebastian
--
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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
