Cross reference while defining FK and children relationships SQLAlchemy

Question

Class Bed
   id
Class Guest
    id
    bed_assignments = relationship(

          primaryjoin=(
              "and_(Guest.id"
              "==foreign(BedAssignment.guest_id),"
              "BedAssignment.bed_status=='occupied')"
         ),

)

Class BedAssignment
    FK Guest
    FK Bed

each class is in a file and referencing Guest in BedAssignment class for the FK purpose next to referencing BedAssignment for relationship purpose creates a cross reference.

I tried using text version of FK or relationship referencing , similar to: Column(Integer, ForeignKey("bed.id") But it seems like a workaround as it stops alembic scripts generation that errors looking for reference.

How the cross reference be solved in this case of relationship model?

Does my answer solve your concern @Azuz ? – Yaakov Bressler Dec 16 '20 at 15:48 — Yaakov Bressler, Dec 16 '20 at 15:48

Yaakov Bressler · Answer 1 · 2020-12-10T21:34:44.543

0

Since your relationship is from an association table + has secondary join specifications, construct your relationship as follows:

bed_assignments = relationship(
    "Bed", #class name of the table
    secondary = "BedAssignment", #table name of the association table
    # Consider using exact tablenames for your join statement...
    primaryjoin = """and_(
        Guest.id==BedAssignment.guest_id,
        BedAssignment.bed_status=='occupied'
    )""",
    backref=backref("Bed", lazy="dynamic"), # if you want the relationship to be observable on the other class... (Guests as children of Beds)
    lazy='dynamic', #only load as needed
    post_update=True, # this allows updating of dependent rows without collisions or cyclical dependencies 
    )

edited Dec 10 '20 at 21:34

answered Dec 10 '20 at 17:51

Yaakov Bressler

9,056
2
45
69

1

Thanks Yaakov! I needed to reference BedAssignment in guest and vice versa for interpretting relationship classes. I was missing the fact that I could reference classes in the _init_.py in models folder which makes my question answered in other threads. – Azuz Dec 17 '20 at 21:50
Glad to hear it. Also, `backref` would only require the relationship to be declared once. @Azuz – Yaakov Bressler Dec 17 '20 at 22:14
Also, consider accepting this answer if it solves your Q – Yaakov Bressler Dec 17 '20 at 22:15

Cross reference while defining FK and children relationships SQLAlchemy

1 Answers1