After some testing, I have found where the trouble lies. In the orm/util file there is this code around line 836:
if not prop and getattr(right_info, 'mapper', None) \ and right_info.mapper.single: # if single inheritance target and we are using a manual # or implicit ON clause, augment it the same way we'd augment the # WHERE. single_crit = right_info.mapper._single_table_criterion if right_info.is_aliased_class: single_crit = right_info._adapter.traverse(single_crit) self.onclause = self.onclause & single_crit If right_info is a class mapping an SQLAlchemy table everything is alright because right_info.mapper.single is False and the code is not run. If, on the other hand, right_info is, for example, a python class that extends a class that maps an SQLAlchemy table the right_info.mapper.single value is True but the right_info.mapper._single_table_criterion is None. Thus, the self.onclause ends up being: ... AND NULL. This code was added in the commit dd6389f171736abd28d777b6fde40403cab0c13e. That could be the reason why my previous code worked in the 0.9 series. You can find attached the files to reproduce this behaviour: - models.py: SQLAlchemy tables - client.py: A python class that extends the Client class in models. - feed.py: A python class that extends the Feed class in models. - main.py: Run this file ('python main.py') to reproduce the behaviour Output from main.py: SELECT feed.id AS feed_id, feed.clientid AS feed_clientid, client.id AS client_id FROM feed LEFT OUTER JOIN client ON client.id = feed.clientid AND NULL In case anybody else finds themselves in this situation, some partial solutions: a) If possible, import directly the SQLAlchemy classes that match the tables. b) Add a check for single_crit being anything other than None in orm/util. Doing this breaks some[1] tests though, so be very sure you know what you are doing. if single_crit: self.onclause = self.onclause & single_crit c) Use __bases__ in the outerjoin to get the SQLAlchemy class: q = DBSession.query(Feed, Client).outerjoin(Client.__bases__[0], Client.id == Feed.clientid) Regards [1] test.orm.inheritance.test_single.RelationshipToSingleTest.test_no_aliasing_from_overlap test.orm.inheritance.test_single.RelationshipToSingleTest.test_outer_join_literal_onclause test.orm.inheritance.test_single.RelationshipToSingleTest.test_outer_join_literal_onclause_alias test.orm.inheritance.test_single.RelationshipToSingleTest.test_outer_join_no_onclause test.orm.inheritance.test_single.RelationshipToSingleTest.test_outer_join_no_onclause_alias On Wednesday, June 17, 2015 at 6:12:33 PM UTC+2, david.c...@gmail.com wrote: > > Hi all, > > I was giving SQLAlchemy 1.0 series a try but I have found some problems > along > the way. There are some queries that in the 0.9.9 version used to work, but > they do not work as expected anymore. An example of one of those is: > > feeds = DBSession.query(Feed, Client, ClientPro).outerjoin( > Client, Client.id == Feed.clientid).outerjoin( > ClientPro, ClientPro.clientid == Client.id) > > and it used to return: > > SELECT feed.id AS feed_id, feed.clientid AS feed_clientid ... > FROM feed > LEFT OUTER JOIN client ON client.id = feed.clientid > LEFT OUTER JOIN clientpro ON clientpro.clientid = client.id > > > But since I changed to 1.0 series it returns: > > SELECT feed.id AS feed_id, feed.clientid ... > FROM feed > LEFT OUTER JOIN client ON client.id = feed.clientid AND NULL > LEFT OUTER JOIN clientpro ON clientpro.clientid = client.id AND NULL > > > As you can see, it adds the 'AND NULL' condition to the joins so the > columns > corresponding to the client and clientpro are NULL. > > I have tested it from version 1.0.0 to 1.0.5 and it returns the same SQL > query > in all of them. > > The relevant part of the models.py file is: > > class Feed(Base, ModelBase): > __tablename__ = 'feed' > id = Column(Integer, primary_key=True) > clientid = Column(Integer, ForeignKey('client.id'), nullable=False) > ... > > class Client(Base, ModelBase): > __tablename__ = 'client' > id = Column(Integer, primary_key=True) > ... > > class ClientPro(Base, ModelBase): > __tablename__ = 'clientpro' > id = Column(Integer, primary_key=True) > clientid = Column(Integer, ForeignKey('client.id', ondelete='CASCADE' > ), nullable=False) > ... > > > And finally, the versions I am using: > - PostgreSQL 9.3 > - Pyramid 1.5.7 (zope.sqlalchemy 0.7.6) > - psycopg2 2.6 > > What it is that I am missing? > > Thanks! > -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
from models import Client class Client(Client): @classmethod def method(cls): pass
from models import Feed class Feed(Feed): @classmethod def method(cls): pass
from sqlalchemy import * from sqlalchemy.orm import * from models import Base from feed import Feed from client import Client engine = create_engine("sqlite://", echo=True) Base.metadata.bind = engine Base.metadata.create_all(engine) DBSession = Session(engine) q = DBSession.query(Feed, Client).outerjoin(Client, Client.id == Feed.clientid) print(q) result = q.all()
from sqlalchemy import ( Column, Integer, ForeignKey ) from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Client(Base): __tablename__ = 'client' id = Column(Integer, primary_key=True) class Feed(Base): __tablename__ = 'feed' id = Column(Integer, primary_key=True) clientid = Column(Integer, ForeignKey('client.id'), nullable=False) def __init__(self, clientid=None): self.clientid = clientid