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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. 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()