Hi,
thanks for your insights.
We're on MySQL / MyISAM, so there're no real foreign key constraints
anyway...
As long as our comparator works - and it seems to be "right" way - we're
fine I guess until the scheme will be refactored more completely
Am Dienstag, 8. November 2016 16:12:34 UTC+1 schrieb Mike Bayer:
>
>
>
> 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] <javascript:>
> > <mailto:[email protected] <javascript:>>.
> > To post to this group, send email to [email protected]
> <javascript:>
> > <mailto:[email protected] <javascript:>>.
> > 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.
