# Model Relationship 配置修复

> **变更日期**：2026-01-28  
> **变更类型**：修复  
> **影响范围**：`server/app/models/folder.py`, `server/app/models/project.py`

---

## 变更概述

修复 SQLModel Relationship 配置，为所有关联关系添加 `primaryjoin` 参数，解决移除物理外键后 SQLAlchemy 无法自动推断关联条件的问题。

---

## 问题背景

在执行用户服务集成测试时，遇到以下错误：

```
sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between 
parent/child tables on relationship Folder.owner - there are no foreign keys 
linking these tables. Ensure that referencing columns are associated with a 
ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.
```

### 根本原因

项目已移除所有物理外键约束（FOREIGN KEY），仅在应用层保证引用完整性。但 SQLModel 的 `Relationship` 依赖物理外键或 `ForeignKey` 定义来自动推断关联条件。

移除物理外键后，必须通过 `primaryjoin` 参数明确指定关联条件。

---

## 修复内容

### 1. Folder Model

**文件**：`server/app/models/folder.py`

#### 修复前

```python
# 关系
owner: Optional["User"] = Relationship(
    sa_relationship_kwargs={"foreign_keys": "[Folder.owner_id]"}
)
parent: Optional["Folder"] = Relationship(
    sa_relationship_kwargs={
        "remote_side": "Folder.id",
        "foreign_keys": "[Folder.parent_folder_id]",
    }
)
members: List["FolderMember"] = Relationship(
    back_populates="folder",
    sa_relationship_kwargs={"cascade": "all, delete-orphan"},
)
```

#### 修复后

```python
# 关系（无物理外键，使用 primaryjoin 明确指定关联条件）
owner: Optional["User"] = Relationship(
    sa_relationship_kwargs={
        "primaryjoin": "Folder.owner_id == User.user_id",
        "foreign_keys": "[Folder.owner_id]",
    }
)
parent: Optional["Folder"] = Relationship(
    sa_relationship_kwargs={
        "primaryjoin": "Folder.parent_folder_id == Folder.id",
        "remote_side": "[Folder.id]",
        "foreign_keys": "[Folder.parent_folder_id]",
    }
)
members: List["FolderMember"] = Relationship(
    back_populates="folder",
    sa_relationship_kwargs={
        "primaryjoin": "Folder.id == FolderMember.folder_id",
        "foreign_keys": "[FolderMember.folder_id]",
        "cascade": "all, delete-orphan",
    },
)
```

#### FolderMember 修复

```python
# 关系（无物理外键，使用 primaryjoin 明确指定关联条件）
folder: Optional[Folder] = Relationship(
    back_populates="members",
    sa_relationship_kwargs={
        "primaryjoin": "FolderMember.folder_id == Folder.id",
        "foreign_keys": "[FolderMember.folder_id]",
    }
)
user: Optional["User"] = Relationship(
    sa_relationship_kwargs={
        "primaryjoin": "FolderMember.user_id == User.user_id",
        "foreign_keys": "[FolderMember.user_id]",
    }
)
inviter: Optional["User"] = Relationship(
    sa_relationship_kwargs={
        "primaryjoin": "FolderMember.invited_by == User.user_id",
        "foreign_keys": "[FolderMember.invited_by]",
    }
)
```

---

### 2. Project Model

**文件**：`server/app/models/project.py`

#### Project 修复

```python
# 关系（无物理外键，使用 primaryjoin 明确指定关联条件）
owner: Optional["User"] = Relationship(
    sa_relationship_kwargs={
        "primaryjoin": "Project.owner_id == User.user_id",
        "foreign_keys": "[Project.owner_id]",
    }
)
folder: Optional["Folder"] = Relationship(
    sa_relationship_kwargs={
        "primaryjoin": "Project.folder_id == Folder.id",
        "foreign_keys": "[Project.folder_id]",
    }
)
members: List["ProjectMember"] = Relationship(
    back_populates="project",
    sa_relationship_kwargs={
        "primaryjoin": "Project.id == ProjectMember.project_id",
        "foreign_keys": "[ProjectMember.project_id]",
        "cascade": "all, delete-orphan",
    },
)
shares: List["ProjectShare"] = Relationship(
    back_populates="project",
    sa_relationship_kwargs={
        "primaryjoin": "Project.id == ProjectShare.project_id",
        "foreign_keys": "[ProjectShare.project_id]",
        "cascade": "all, delete-orphan",
    },
)
```

#### ProjectMember 修复

```python
# 关系（无物理外键，使用 primaryjoin 明确指定关联条件）
project: Optional[Project] = Relationship(
    back_populates="members",
    sa_relationship_kwargs={
        "primaryjoin": "ProjectMember.project_id == Project.id",
        "foreign_keys": "[ProjectMember.project_id]",
    }
)
user: Optional["User"] = Relationship(
    sa_relationship_kwargs={
        "primaryjoin": "ProjectMember.user_id == User.user_id",
        "foreign_keys": "[ProjectMember.user_id]",
    }
)
inviter: Optional["User"] = Relationship(
    sa_relationship_kwargs={
        "primaryjoin": "ProjectMember.invited_by == User.user_id",
        "foreign_keys": "[ProjectMember.invited_by]",
    }
)
```

#### ProjectShare 修复

```python
# 关系（无物理外键，使用 primaryjoin 明确指定关联条件）
project: Optional[Project] = Relationship(
    back_populates="shares",
    sa_relationship_kwargs={
        "primaryjoin": "ProjectShare.project_id == Project.id",
        "foreign_keys": "[ProjectShare.project_id]",
    }
)
creator: Optional["User"] = Relationship(
    sa_relationship_kwargs={
        "primaryjoin": "ProjectShare.created_by == User.user_id",
        "foreign_keys": "[ProjectShare.created_by]",
    }
)
```

---

## 修复原理

### primaryjoin 参数

`primaryjoin` 参数明确指定了两个表之间的关联条件，格式为：

```python
"LeftTable.left_column == RightTable.right_column"
```

### 为什么需要 primaryjoin

1. **无物理外键**：项目禁止使用物理外键约束
2. **SQLAlchemy 推断失败**：没有 `ForeignKey` 或 `ForeignKeyConstraint`，SQLAlchemy 无法自动推断关联条件
3. **明确指定**：通过 `primaryjoin` 明确告诉 SQLAlchemy 如何关联两个表

### 自引用关系

对于自引用关系（如 `Folder.parent`），还需要 `remote_side` 参数：

```python
parent: Optional["Folder"] = Relationship(
    sa_relationship_kwargs={
        "primaryjoin": "Folder.parent_folder_id == Folder.id",
        "remote_side": "[Folder.id]",  # 指定远端列
        "foreign_keys": "[Folder.parent_folder_id]",
    }
)
```

---

## 验证结果

### 集成测试通过

修复后，用户服务集成测试全部通过：

```bash
docker exec jointo-server-app pytest tests/integration/test_user_service.py -v
```

**结果**：

```
============================== test session starts ==============================
collected 7 items

tests/integration/test_user_service.py::TestUserServiceIntegration::test_create_and_get_user PASSED [ 14%]
tests/integration/test_user_service.py::TestUserServiceIntegration::test_logout_with_session_ownership_validation PASSED [ 28%]
tests/integration/test_user_service.py::TestUserServiceIntegration::test_delete_user_cascade PASSED [ 42%]
tests/integration/test_user_service.py::TestUserServiceIntegration::test_create_session_with_user_validation PASSED [ 57%]
tests/integration/test_user_service.py::TestUserServiceIntegration::test_update_username_once_only PASSED [ 71%]
tests/integration/test_user_service.py::TestUserServiceIntegration::test_bind_email_uniqueness PASSED [ 85%]
tests/integration/test_user_service.py::TestUserServiceIntegration::test_get_credits_info PASSED [100%]

============================== 7 passed in 0.64s ==============================
```

✅ **7/7 集成测试通过**

---

## 影响范围

### 修改的文件

1. `server/app/models/folder.py`
   - `Folder` 类：3 个 Relationship
   - `FolderMember` 类：3 个 Relationship

2. `server/app/models/project.py`
   - `Project` 类：4 个 Relationship
   - `ProjectMember` 类：3 个 Relationship
   - `ProjectShare` 类：2 个 Relationship

### 不影响的功能

- ✅ 数据库表结构不变
- ✅ 应用层引用完整性验证逻辑不变
- ✅ Repository 和 Service 代码不变
- ✅ API 接口不变

---

## 最佳实践

### 无物理外键项目的 Relationship 配置

在禁止使用物理外键的项目中，所有 Relationship 必须遵循以下模式：

```python
# 单向关系
related_object: Optional["RelatedModel"] = Relationship(
    sa_relationship_kwargs={
        "primaryjoin": "CurrentModel.foreign_key_column == RelatedModel.primary_key_column",
        "foreign_keys": "[CurrentModel.foreign_key_column]",
    }
)

# 双向关系（back_populates）
related_objects: List["RelatedModel"] = Relationship(
    back_populates="current_object",
    sa_relationship_kwargs={
        "primaryjoin": "CurrentModel.primary_key_column == RelatedModel.foreign_key_column",
        "foreign_keys": "[RelatedModel.foreign_key_column]",
        "cascade": "all, delete-orphan",  # 可选
    },
)

# 自引用关系
parent: Optional["CurrentModel"] = Relationship(
    sa_relationship_kwargs={
        "primaryjoin": "CurrentModel.parent_id == CurrentModel.id",
        "remote_side": "[CurrentModel.id]",
        "foreign_keys": "[CurrentModel.parent_id]",
    }
)
```

---

## 相关文档

- [用户服务测试](./2026-01-28-user-service-tests.md)
- [UUID 生成应用层迁移](./2026-01-27-uuid-generation-app-layer.md)
- [移除物理外键约束](./2026-01-27-remove-physical-foreign-keys.md)
- [数据库设计规范](./../../../.claude/skills/jointo-tech-stack/references/database.md)

---

## 总结

成功修复了 Folder 和 Project Model 的 Relationship 配置，为所有关联关系添加了 `primaryjoin` 参数。修复后：

1. ✅ 集成测试全部通过（7/7）
2. ✅ SQLAlchemy 可以正确推断关联关系
3. ✅ 符合项目"禁止物理外键"的架构约束
4. ✅ 为后续 Model 开发提供了标准模式

这是项目架构演进的重要一步，确保了在无物理外键的情况下，ORM 关系仍然可以正常工作。