Problems with inheritance using SQLModel

Question

I want the role attribute from AppUser populated. When I declare it in AppUser class, it works as expected, joining the userrole table. But if I declare it in BaseUser class, it's not populating the field, ignoring the join. My question is, how can I make this inheritance model work? With the code below, you can view the sql query. I'm working with python 3.10.12 and SQLModel 0.0.12

from sqlmodel import Field, SQLModel,Column
from sqlalchemy import create_engine
from sqlalchemy.orm import relationship
from sqlalchemy.pool import StaticPool
from typing import Optional
from sqlalchemy import BigInteger
from sqlmodel import Field, Relationship, SQLModel, Column
from sqlmodel import Session, select


class Base(SQLModel, table = False):
    id:Optional[int] = Field(sa_column=Column(BigInteger(), default=None, primary_key=True))

class UserRole(Base, table = True):
    name:str

class BaseUser(Base, table = False):
    username:str
    role_id:int = Field(default=None, foreign_key="userrole.id")
    role:UserRole = Relationship(sa_relationship=relationship("UserRole", lazy="joined"))


class AppUser(BaseUser, table = True):
    email:str

    # Uncomment to make join work
    #role:UserRole = Relationship(sa_relationship=relationship("UserRole", lazy="joined"))



engine = create_engine(
        'sqlite:///demo2.db',
        connect_args={"check_same_thread": False},
        poolclass=StaticPool,
        echo=True
)
SQLModel.metadata.create_all(engine)

with Session(engine) as session:
        statement = select(AppUser)
        results = session.exec(statement)
        items = results.all()

Answer 1

According to the documentation of SQLModel, only models with table=True may include relationship attributes.

It seems like it doesn't work even if your child class has table=True.

If you need to include this field to output model, just create new model and add this field without relationship:

class BaseUser(Base, table = False):
    username:str
    role_id:int = Field(default=None, foreign_key="userrole.id")


class AppUser(BaseUser, table = True):
    email:str
    role:UserRole = Relationship(sa_relationship=relationship("UserRole", lazy="joined"))


class UserOut(BaseUser):
    role:UserRole

https://sqlmodel.tiangolo.com/tutorial/fastapi/relationships/#data-models-without-relationship-attributes