On 03/08/2016 12:50 PM, adrianodilu...@gmail.com wrote:
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:
your results are not surprising, because the "Manager" mapping as a whole represents the JOIN of employee and manager; you are then emiting a JOIN against that JOIN and that's exactly what the SQL output is saying.
for Core, you need to refer to the specific employee/manager tables individually, using Employee.__table__ and Manager.__table__ most directly. The examples at http://docs.sqlalchemy.org/en/rel_1_0/orm/inheritance.html#advanced-control-of-which-tables-are-queried illustrates a brief example.
| fromsqlalchemy importColumn,Integer,String,ForeignKey fromsqlalchemy.ext.declarative importdeclarative_base fromsqlalchemy.orm importsessionmaker fromsqlalchemy importcreate_engine importsqlalchemy.sql Base=declarative_base() engine =create_engine('sqlite:///:memory:',echo=True) Session=sessionmaker(bind=engine) Session.configure(bind=engine) session =Session() classEmployee(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 } classEngineer(Employee): __tablename__ ='engineer' id =Column(Integer,ForeignKey('employee.id'),primary_key=True) engineer_name =Column(String(30)) __mapper_args__ ={ 'polymorphic_identity':'engineer', } classManager(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 sqlalchemy+unsubscr...@googlegroups.com <mailto:sqlalchemy+unsubscr...@googlegroups.com>. To post to this group, send email to sqlalchemy@googlegroups.com <mailto:sqlalchemy@googlegroups.com>. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
-- 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.