I've got a strange relationship on a legacy Oracle 8i database which I need
to support (whether I like it or not).
The cleanest approach is specifying that the 'primaryjoin' to the
relationship in the mapper should include an extra join clause. I hate
doing this, but after many other approaches, I've found this is by far the
cleanest approach due to bad database design (which I can't control --
legacy).
Anyway, the attached script shows an simplified, analogous mock-up, which
works *correctly* when joins are ANSI and *incorrectly* with use_ansi=False.
The script demonstrates an inconsistency in use_ansi True vs. False on
sqlalchemy version 1.1.14 (although my sqlalchemy is older).
In the use_ansi=False SQL, the correct "fix" would be changing the rendered:
AND bugs_1.deathdate IS NULL
into
AND bugs_1.deathdate(+) IS NULL
This then matches the ANSI join and works on 8i (I've tested it).
Is this something we can fix? Since the column is on the* remote table *and
*specified in the join condition*, it really needs "(+)" after the column
name in SQL. This accomplishes the same thing as the ANSI version placing
this join condition in the "ON ..." clause instead of the "WHERE".
Alternatively, is there a hack I could use to fix the rendered SQL on
joinedloads for this particular relationship?
Thanks very much in advance!
Kent
--
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.
from sqlalchemy import *
from sqlalchemy.orm import *
from datetime import date
# if use_ansi=True, this script succeeds
# if False, this script fails
use_ansi = False
#use_ansi = True
engine = create_engine('oracle://kent:kent@localhost:1521/xe', use_ansi=use_ansi, echo=True)
metadata = MetaData(engine)
Session = sessionmaker(bind=engine)
# a rock has many bugs
rocks_table = Table("rocks", metadata,
Column("id", Integer, primary_key=True),
)
bugs_table = Table("bugs", metadata,
Column("id", Integer, primary_key=True),
Column("rockid", Integer, ForeignKey('rocks.id')),
Column("deathdate", Date),
)
class Rock(object):
pass
class Bug(object):
pass
mapper(Rock, rocks_table,
properties={
'livingbugs': relationship(Bug,
primaryjoin=and_(
bugs_table.c.rockid == rocks_table.c.id,
bugs_table.c.deathdate == None,
)),
})
mapper(Bug, bugs_table)
metadata.create_all()
try:
s = Session()
r=Rock()
r.id = 55
b=Bug()
b.id = 1
b.rockid = 55
b.deathdate = date.today()
s.add(r)
s.add(b)
s.commit()
s = Session()
rocks = s.query(Rock).options(joinedload('livingbugs')).all()
if not rocks:
# When not using ANSI, if
# AND bugs_1.deathdate IS NULL
# is changed to:
# AND bugs_1.deathdate(+) IS NULL
# then the join is consistent with ANSI join and doesn't fail
raise Exception("Rock not selected")
finally:
metadata.drop_all()
