Thanks. Sorry I missed that. I latched onto "*Joins to a Target Entity or Selectable*"
On Tue, Jul 21, 2020 at 11:15 PM Mike Bayer <mike...@zzzcomputing.com> wrote: > 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 a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/jlMX6Z2q740/unsubscribe. > To unsubscribe from this group and all its topics, 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 > <https://groups.google.com/d/msgid/sqlalchemy/4ce547da-efbf-43ec-b9ff-788c117d6eb8%40www.fastmail.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/CAHUdipkv1%2Bg4L8TSNjQyXgndg8t0ORS4AOn%2B%2BhQd3vzZTLokpg%40mail.gmail.com.
