How to get data from many-to-many relationship in SQLModel

41 Views Asked by At

I wonder how to get data from my model using a linked table.

class App_PersonContact(SQLModel, table=True):
    personobjectid: int = Field(default=None, foreign_key='isis.app_person.objectid', primary_key=True)
    contactobjectid: int = Field(default=None, foreign_key='isis.app_contact.objectid', primary_key=True)

class App_Person(SQLModel, table=True):
    objectid: Optional[int] = Field(default=None, primary_key=True)
    personid: int = Field(sa_column=Column("personid", Integer, unique=True))
    surname: str
    forename1: Optional[str] = None
    contacts: List['App_Contact'] = Relationship(back_populates="persons", link_model=App_PersonContact)

class App_Contact(SQLModel, table=True):
    objectid: Optional[int] = Field(default=None, primary_key=True)
    personid: int = Field(sa_column=Column("personid", Integer, unique=True))
    persons: List[App_Person] = Relationship(back_populates="contacts", link_model=App_PersonContact)

My query looks like this:

 def select_all_person_contacts(self, username):
     statement = select(App_Person.contacts).limit(3)
     results = self.dbi.query(statement, username)
     contacts = []
     for data in results:
         print(data)
         contacts.append(Contact.from_orm(data))
     return contacts

After executing the query it tells me that the FROM clause is not at the right place and gives me my SQL Statement:

Help: https://docs.oracle.com/error-help/db/ora-00923/
[SQL: SELECT isis.app_person.objectid = app_personcontact_1.personobjectid AND isis.app_contact.objectid = app_personcontact_1.contactobjectid AS contacts
FROM isis.app_person, isis.app_personcontact app_personcontact_1, isis.app_contact
 FETCH FIRST 3 ROWS ONLY]

dbi is my database_interface where I create an enginge using SQLALchemy and execute the query with a session. So there is a function defined which is called query and it gets the username and the statement and uses the session from SQLAlchemy to execute the query. With all my other endpoints it works fine so I higly doubt the problem is the query function but something in the model

Any ideas?

0

There are 0 best solutions below