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

[toxypiks]

Hey everyone, I'm trying to get data from my many-to-many relationship using a linked table. I just did it the same way it is described in the tutorial but I get a database error telling me the FROM clause is not where its expected.

Heres my code:

`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 is the following:

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

Note: I wrote a database_interface (self.dbi) were I create an engine with SQLAlchemy and use Session to execute the statement.

The Errror message also 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]

Any ideas on what I'm doing wrong?

[joachimhuet]

First you should format your code better than that using triple ` for multiline code.
Then there are a few things I don't understand, why do you use Field(sa_column=Column("personid", Integer, unique=True)) instead of just Field(unique=True)
And finally your query looks strange, note that query and from_orm are deprecated. Here is a small suggestion:

def select_person_contacts(session, username, n_contacts=3):
    # first you select the person concerned
    statement = select(App_Person).where(App_Person.surname == username).limit(1)
    person = session.exec(statement).first()
    if person:
        # Then you get the n-first contacts of that person
        contacts = person.contacts[:n_contacts]
        return contacts
    # if no one was found return empty list
    return []

I hope this can help you