Many-to-many using custom join columns and autoloaded tables

[Rob Rosenfeld]

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))

[Mike Bayer]

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

--

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAHUdip%3DZLwO-KE-3ifiCHNjZ0v7EbqQpA1d7BGhqao9bGLtnCw%40mail.gmail.com.

[Rob Rosenfeld]

Thanks.  Sorry I missed that.  I latched onto "Joins to a Target Entity or Selectable"

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+...@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.