SQLModel多对多关系数据查询遇ORA-00923报错求助

模型定义 #

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)

查询代码 #

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

报错信息 #

ORA-00923: 未找到预期的FROM关键字
生成的错误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

解决思路 #

• 问题核心：直接在select()中查询ORM关系属性App_Person.contacts时，SQLModel错误地将关联条件拼接成了SELECT字段，而非生成正确的JOIN关联逻辑，导致SQL语法不符合Oracle要求。

• 修正方案1：查询实体表+ORM关系访问
  先查询App_Person实体，再通过ORM预加载的关系属性获取关联的联系人，避免直接查询关系属性：

  from sqlalchemy.orm import joinedload
  
  def select_all_person_contacts(self, username):
      # 用joinedload预加载关联数据，避免N+1查询
      statement = select(App_Person).options(joinedload(App_Person.contacts)).limit(3)
      persons = self.dbi.query(statement, username).all()
      
      contacts = []
      for person in persons:
          contacts.extend(person.contacts)
      # 取前3个联系人（如果需要严格限制数量，可调整逻辑）
      return [Contact.from_orm(contact) for contact in contacts[:3]]
  

• 修正方案2：显式编写JOIN查询
  直接针对App_Contact表编写JOIN语句，关联中间表和App_Person表，生成符合语法的SQL：

  def select_all_person_contacts(self, username):
      statement = (
          select(App_Contact)
          .join(App_PersonContact, App_Contact.objectid == App_PersonContact.contactobjectid)
          .join(App_Person, App_Person.objectid == App_PersonContact.personobjectid)
          .limit(3)
      )
      results = self.dbi.query(statement, username)
      return [Contact.from_orm(data) for data in results]
  

• 额外检查点
  确认模型中foreign_key指定的isis.app_person.objectid和isis.app_contact.objectid与数据库实际的schema、表名、字段名完全匹配，避免因引用错误导致关联逻辑异常。

内容的提问来源于stack exchange，提问作者Immortal_Wombat