Retrieve data from a related table with no primary/foreign keys - SQLModel, FastAPI

I am working on a FastAPI app that pulls data from Oracle table. The issue here is that in the oracle table, we don't have primary or foreign keys. Relationship is created with unique keys. Here is a simplified 'schema' of the tables that hold the data I need:

I want to query all Department data. There are a lot of departments and each department has only 1 employee. Employee_id is the unique key that connects Department and Employee tables. Employee table holds only non sensitive data, so in order to get the name of this Employee I have to get that from Resource table. Resource_id is the unique key in this case.

What the end goal is to query Department and get employee name in the response. Here are my SQLModel classes:

class Resource(SQLModel, table=True):
    __tablename__ = "resource"
        PrimaryKeyConstraint('resource_id'),
    )

    resource_id: int = Field(default=None, primary_key=True)
    name: Optional[str]
    employee_rep: Optional["Employee"] = Relationship(back_populates="resource",
                                                   sa_relationship=RelationshipProperty("Employee",
                                                                                        primaryjoin="foreign(Resource.resource_id) == Employee.resource_id",
                                                                                        uselist=False))


class Employee(SQLModel, table=True):
    __tablename__ = "employee"
    __table_args__ = (
        PrimaryKeyConstraint('employee_id'),
    )

    employee_id: int = Field(default=None, primary_key=True)
    resource_id: Optional[int] = Field(default=None, foreign_key="resource.resource_id")
    resource: Resource = Relationship(back_populates="employee_rep",
                                      sa_relationship=RelationshipProperty("Resource",
                                                                           primaryjoin="foreign(Employee.resource_id) == Resource.resource_id",
                                                                           uselist=False))


class Department(SQLModel, table=True):
    __tablename__ = "department"

    __table_args__ = (
        PrimaryKeyConstraint('department_id'),
    )

    department_id: int = Field(default=None, primary_key=True)
    employee_id: Optional[str] = Field(default=None, foreign_key="employee.employee_id")
    employee_name: Employee = Relationship(back_populates="resource",
                                       sa_relationship=RelationshipProperty("Employee",
                                                                            primaryjoin="foreign(Department.employee_id) == Employee.employee_id",
                                                                            uselist=False))

I tried to query the data in a lot of ways and I just can't make it return the name:

statement = select(Department).join(Employee,Department.employee_id == Employee.employee_id).join(Resource,Employee.resource_id == Resource.resource_id)
results = session.exec(statement).first()

However, if I query it like this:

statement = select(Department,Resource).join(Employee,Department.employee_id == Employee.employee_id).join(Resource,Employee.resource_id == Resource.resource_id)
results = session.exec(statement).first()

result_dicts = [
            {
                "Department": [department, resource]
            }
            for department, resource in results
        ]

This is the value of the result_dicts:

[ { "Department": [ { "department_id": 1, "employee_id: 007 }, { "resource_id": 123456, "name": "Jon Doe" } ] } ]

My goal is to have it return this:

[ { "Department": [ { "department_id": 1, "employee_id: 007, "employee_name": "Jon Doe" } } ]

Is there a way to make SQLModel handle the relationship data or I will have to manually add this ? Also once I get the data, I should be able to update the employee_name.

The issue here is that in the oracle table, we don't have primary or foreign keys. Relationship is created with unique keys.

That is not really a problem provided that your SQLModel classes declare what attributes should be treated as primary keys and foreign keys. For example, with PostgreSQL tables

CREATE TABLE resource (
    resource_id SERIAL NOT NULL,
    name VARCHAR NOT NULL,
    CONSTRAINT uk_resource_id UNIQUE (resource_id)
);

CREATE TABLE employee (
    employee_id SERIAL NOT NULL,
    resource_id INTEGER,
    CONSTRAINT uk_employee_id UNIQUE (employee_id)
);

CREATE TABLE department (
    department_id SERIAL NOT NULL,
    department_name VARCHAR NOT NULL,
    employee_id INTEGER,
    CONSTRAINT uk_department_id UNIQUE (department_id)
);

(note no primary or foreign keys at the table level), and model classes

class Resource(SQLModel, table=True):
    resource_id: int = Field(default=None, primary_key=True)
    name: str
    employee_rep: "Employee" = Relationship(back_populates="resource")


class Employee(SQLModel, table=True):
    employee_id: int = Field(default=None, primary_key=True)
    resource_id: Optional[int] = Field(
        default=None, foreign_key="resource.resource_id"
    )
    resource: "Resource" = Relationship(back_populates="employee_rep")
    department: "Department" = Relationship(back_populates="employee")


class Department(SQLModel, table=True):
    department_id: int = Field(default=None, primary_key=True)
    department_name: str
    employee_id: Optional[int] = Field(
        default=None, foreign_key="employee.employee_id"
    )
    employee: "Employee" = Relationship(back_populates="department")

we can run a query for Department and SQLAlchemy will know how to create the JOINs to the other tables

with Session(engine) as sess:
    dept_1: Department = sess.scalars(
        select(Department)
        .where(Department.department_name == "HR")
        .options(
            joinedload(Department.employee, innerjoin=True).joinedload(
                Employee.resource, innerjoin=True
            )
        )
    ).one()
    """
    SELECT department.department_id, department.department_name, department.employee_id,
        resource_1.resource_id, resource_1.name, employee_1.employee_id AS employee_id_1, 
        employee_1.resource_id AS resource_id_1 
    FROM department 
        JOIN employee AS employee_1 ON employee_1.employee_id = department.employee_id 
        JOIN resource AS resource_1 ON resource_1.resource_id = employee_1.resource_id 
    WHERE department.department_name = %(department_name_1)s
    [generated in 0.00033s] {'department_name_1': 'HR'}
    """

The retrieved Department object does contain the related employee name

    print(dept_1.employee.resource.name)
    # John Doe

and we can update that name

    dept_1.employee.resource.name = "Bob Loblaw"
    sess.commit()
    """
    UPDATE resource SET name=%(name)s WHERE resource.resource_id = %(resource_resource_id)s
    [generated in 0.00030s] {'name': 'Bob Loblaw', 'resource_resource_id': 1}
    """

As for formatting the results as JSON, that would be a FastAPI issue which should be asked as a separate question.