multiple foreign keys

[StackTrace18]

I am trying to create attributes for database objects with more than one foreign key relationships to the same object(s) e.g. object(s) with multiple many to many relations between them.
Tried sqlalchemy docs but still unclear in reflex.
Any ideas? Thanks in advance

[picklelo]

@StackTrace18 What code have you tried? Can't you just set them as different foreign key fields?

class App(rx.Model, table=True):
    ...

class Event(rx.Model, table=True):
    app_id1: int = Field(foreign_key=f"{App.__tablename__}.id")
    app_id2: int = Field(foreign_key=f"{App.__tablename__}.id")

Or do you mean an arbitrary (unknown) amount of references, like a list of ids? In that case, the recommended way is to make a third table that joins the two together.

class AppEvent(rx.Model, table=True):
    app_id: Field(foreign_key=f"{App.__tablename__}.id")
    event_id: Field(foreign_key=f"{Event.__tablename__}.id")

The other option is to just store it as a stringified json list, but this won't have the foreign key integrity constraints.

[StackTrace18]

Right, the foreign key constraints have been set. The problem is when I try to add the "back-populates" field, which is the ORM layer.
So,

event_type_2:[Event]=sqlmodel.Relationship(back_populates="Event")

won't compile I suppose because the ORM doesn't know which field to point to (in 'Event').
Your suggestion of serializing to/from json is the work-around for now, but it would be nice to have object relation lookups on the python side.
Any suggestions|pointers most grateful.

[picklelo]

This may be a better question on the sqlalchemy side. Maybe this helps:https://stackoverflow.com/questions/77099509/how-to-use-back-populates-in-sqlalchemy-with-two-or-more-foreign-keys-from-s

[StackTrace18]

OK, great, I'll check it out,
thanks,

[StackTrace18]

Found a good (helpful) answer
here