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, [email protected] 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 [email protected].
To post to this group, send email to [email protected].
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
