I read the docs but it never occurred to me that *both* the join objects
have to be tables.
Thanks! :)
Leaving the correct join formula here for future references, that produces
indeed the right query.
SELECT employee.name, manager.manager_name
> FROM employee JOIN manager ON employee.id = manager.id
q = sqlalchemy.sql.select(
[e.name, m.manager_name],
).select_from(
e.__table__.join(m.__table__)
)
Il giorno martedì 8 marzo 2016 18:50:40 UTC+1, [email protected] ha
scritto:
>
> I've spotted the following strange behaviour while using the last version
> (SQLAlchemy==1.0.12) with SQLite or PostgreSQL (possibly others, too).
>
> To be short, I created a few polymorphic classes that map to their
> respective tables;
> I'm unable to perform any query containing a LEFT OUTER JOIN from the
> "superclass" to one of the subclasses.
>
> In fact, queries containing the left outer join are of the following form,
> resulting in sqlalchemy.exc.OperationalError/ProgrammingError both on
> SQLite and on PostgreSQL.
>
> SELECT employee.name, manager.manager_name
> FROM employee JOIN (employee JOIN manager ON employee.id = manager.id) ON
> employee.id = manager.id
>
> As you can see, the "ON" condition appears twice, rendering the query
> invalid.
>
> A full program showing the problematic behaviour follows:
>
> from sqlalchemy import Column, Integer, String, ForeignKey
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import sessionmaker
> from sqlalchemy import create_engine
> import sqlalchemy.sql
>
>
> Base = declarative_base()
> engine = create_engine('sqlite:///:memory:', echo=True)
> Session = sessionmaker(bind=engine)
> Session.configure(bind=engine)
> session = Session()
>
>
>
>
> class Employee(Base):
> __tablename__ = 'employee'
> id = Column(Integer, primary_key=True)
> name = Column(String(50))
> type = Column(String(50))
>
>
> __mapper_args__ = {
> 'polymorphic_identity': 'employee',
> 'polymorphic_on': type
> }
>
>
>
>
> class Engineer(Employee):
> __tablename__ = 'engineer'
> id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
> engineer_name = Column(String(30))
>
>
> __mapper_args__ = {
> 'polymorphic_identity': 'engineer',
> }
>
>
>
>
> class Manager(Employee):
> __tablename__ = 'manager'
> id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
> manager_name = Column(String(30))
>
>
> __mapper_args__ = {
> 'polymorphic_identity': 'manager',
> }
>
>
> Base.metadata.create_all(engine)
>
>
> e = Employee
> m = Manager
>
>
> q = sqlalchemy.sql.select(
> [e.name, m.manager_name],
> ).select_from(
> e.__table__.join(m)
> )
> print(q)
> session.execute(q)
>
> Am I missing something here, maybe abusing the Core API?
>
> Best regards,
> Adriano
>
--
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.
