I'm getting invalid SQL when I try to filter for records that have no
matching related record in a one-to-one relationship using the not_
function. For example, with address being a relation from User:
>>> print User.id==None
users.id IS NULL
>>> print not_(User.id==None)
users.id IS NOT NULL
>>> print User.address==None
users.address_id IS NULL
>>> print not_(User.address==None)
users.address_id != NULL
The last expression fails to return the correct records against MySQL.
Below is a complete test script to reproduce the above output:
from sqlalchemy import create_engine, Table, Column, Integer, String,
Text, \
MetaData, ForeignKeyConstraint, not_
from sqlalchemy.orm import mapper, relation, sessionmaker
# initialize the engine
engine = create_engine('sqlite:///:memory:', echo=False)
metadata = MetaData()
# setup the users table
users = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
Column('address_id', Integer),
ForeignKeyConstraint(['address_id'], ['addresses.id'])
)
# setup the addresses table
addresses = Table('addresses', metadata,
Column('id', Integer, primary_key=True),
Column('address', Text),
)
# create the tables
metadata.create_all(engine)
# define the User class
class User(object):
def __repr__(self):
return '<User "%s">' % self.name
# define the Address class
class Address(object):
def __repr__(self):
return '<Address "%s">' % self.address
# setup the mapping
mapper(Address, addresses)
mapper(User, users, properties={
'address': relation(Address, backref='user')
})
# create the session
Session = sessionmaker(bind=engine)
# perform the tests
print User.id==None
print not_(User.id==None)
print User.address==None
print not_(User.address==None)
A possible fix seems to be to add "binary.negate = operators.isnot"
below lines 394 and 397 of orm/strategies.py
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
