to join on the relationships you have to name them: Class.query.join(Class.enrollments).join(Enrollment.students).join(Student.details)
the different ways to join are laid out at https://docs.sqlalchemy.org/en/13/orm/query.html?highlight=query%20join#sqlalchemy.orm.query.Query.join On Tue, Jul 21, 2020, at 9:40 PM, Rob Rosenfeld wrote: > > Hi All, > > I'm struggling to put several concepts together. I am working with a legacy > SQL Server database (2008 R2 and 2019) that was not setup with much planning, > let alone ORM in mind. I would like to set up a many to many relationship > that joins through columns I specify and all the tables are autoloaded from > the database schema. I've been doing fine until I hit this association > object pattern. Can you please offer any advice? > > Thanks, > Rob > > In the end my goal is to execute the following and have it issue a single > statement to the database. > > results = > Class.query.join(Enrollment).join(Student).join(StudentDetails).all() > > > > But even this call fails and gets the error below. I assume I've incorrectly > defined the relationships if it doesn't know how to do the join. > > results = Class.query.join(Enrollment).all() > > sqlalchemy.exc.InvalidRequestError: Don't know how to join to <class > '__main__.Enrollment'>. Please use the .select_from() method to establish an > explicit left side, as well as providing an explcit ON clause if not present > already to help resolve the ambiguity. > > > Here's a trimmed example illustrating what I'm trying > > class Class(Model): > __table__ = Table(*"classes"*, metadata, autoload=True, > autoload_with=engine) > > > class Student(Model): > __table__ = Table(*"Students"*, metadata, autoload=True, > autoload_with=engine) > > > class Enrollment(Model): > __table__ = Table(*"Enrollment"*, metadata, autoload=True, > autoload_with=engine) > > class_join_string = > (*"and_(remote(Class.ClassID)==foreign(Enrollment.class)," ** "remote(Class.TimeSlot)==foreign(Enrollment.time_slot))"*) > class_ = relationship(*"Class"*, primaryjoin=class_join_string, > backref=*"enrollments"*) > > student_join_string = > *"remote(Student.StudentId)==foreign(Enrollment.studentID)" ** *student = relationship(*"Student"*, primaryjoin=student_join_string, backref=*"classes"*) > > > class StudentDetails(Model): > *# 1 to 1 with Student ** *__table__ = Table(*"StudentDetails"*, metadata, autoload=True, autoload_with=engine) > student_join_string = > *"foreign(StudentDetails.student)==remote(Student.Student_id)" ** *student = Db.db().relationship(*'Student'*, primaryjoin=student_join_string, > backref=Db.db().backref(*'details'*, > uselist=False)) > > -- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CAHUdip%3DZLwO-KE-3ifiCHNjZ0v7EbqQpA1d7BGhqao9bGLtnCw%40mail.gmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/CAHUdip%3DZLwO-KE-3ifiCHNjZ0v7EbqQpA1d7BGhqao9bGLtnCw%40mail.gmail.com?utm_medium=email&utm_source=footer>. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/4ce547da-efbf-43ec-b9ff-788c117d6eb8%40www.fastmail.com.
