323 lines
8.5 KiB
Markdown
323 lines
8.5 KiB
Markdown
# PostgREST 嵌套查询故障排查
|
||
|
||
## 🐛 问题现象
|
||
|
||
PostgREST 查询中包含了嵌套语法，但返回的数据中**没有嵌套字段**：
|
||
|
||
```javascript
|
||
// 查询语法
|
||
select: `
|
||
id, code, name, ...,
|
||
child_group:evaluation_point_groups!fk_evaluation_points_child_group(id, name),
|
||
parent_group:evaluation_point_groups!fk_evaluation_points_parent_group(id, name)
|
||
`
|
||
|
||
// 实际返回（缺少 child_group 和 parent_group）
|
||
{
|
||
"id": 670,
|
||
"code": "04formcheck-vlm-cz",
|
||
"name": "表格抽取与评查（多模态）",
|
||
"evaluation_point_groups_id": 61,
|
||
"evaluation_point_groups_pid": 60,
|
||
// ❌ 没有 child_group
|
||
// ❌ 没有 parent_group
|
||
...
|
||
}
|
||
```
|
||
|
||
---
|
||
|
||
## 🔍 问题诊断
|
||
|
||
### 检查外键约束
|
||
|
||
```sql
|
||
SELECT
|
||
tc.constraint_name AS "约束名",
|
||
kcu.column_name AS "列名",
|
||
ccu.table_name AS "引用表",
|
||
ccu.column_name AS "引用列"
|
||
FROM information_schema.table_constraints AS tc
|
||
JOIN information_schema.key_column_usage AS kcu
|
||
ON tc.constraint_name = kcu.constraint_name
|
||
JOIN information_schema.constraint_column_usage AS ccu
|
||
ON ccu.constraint_name = tc.constraint_name
|
||
WHERE tc.constraint_type = 'FOREIGN KEY'
|
||
AND tc.table_name = 'evaluation_points'
|
||
AND kcu.column_name IN ('evaluation_point_groups_id', 'evaluation_point_groups_pid')
|
||
ORDER BY kcu.column_name;
|
||
```
|
||
|
||
**发现问题**：
|
||
|
||
```
|
||
约束名 | 列名
|
||
-----------------------------------------|-------------------------------
|
||
fk_evaluation_points_group | evaluation_point_groups_id ← 旧的
|
||
fk_evaluation_points_child_group | evaluation_point_groups_id ← 新的
|
||
fk_evaluation_points_parent_group | evaluation_point_groups_pid
|
||
```
|
||
|
||
**根本原因**：
|
||
- `evaluation_point_groups_id` 列有**两个外键约束**
|
||
- PostgREST 不知道使用哪个，导致嵌套查询失败
|
||
- 只返回主表字段，忽略嵌套部分
|
||
|
||
---
|
||
|
||
## ✅ 解决方案
|
||
|
||
### 步骤 1: 删除旧的外键约束
|
||
|
||
执行脚本：`database/fix_duplicate_foreign_keys.sql`
|
||
|
||
```bash
|
||
psql -h <host> -U <username> -d <database> -f database/fix_duplicate_foreign_keys.sql
|
||
```
|
||
|
||
或者直接执行：
|
||
|
||
```sql
|
||
ALTER TABLE evaluation_points
|
||
DROP CONSTRAINT IF EXISTS fk_evaluation_points_group;
|
||
```
|
||
|
||
---
|
||
|
||
### 步骤 2: 验证外键约束（应该只有 2 个）
|
||
|
||
```sql
|
||
SELECT
|
||
tc.constraint_name AS "约束名",
|
||
kcu.column_name AS "列名"
|
||
FROM information_schema.table_constraints AS tc
|
||
JOIN information_schema.key_column_usage AS kcu
|
||
ON tc.constraint_name = kcu.constraint_name
|
||
WHERE tc.constraint_type = 'FOREIGN KEY'
|
||
AND tc.table_name = 'evaluation_points'
|
||
AND kcu.column_name IN ('evaluation_point_groups_id', 'evaluation_point_groups_pid')
|
||
ORDER BY kcu.column_name;
|
||
```
|
||
|
||
**预期结果**（只有 2 个）：
|
||
```
|
||
约束名 | 列名
|
||
-----------------------------------------|-------------------------------
|
||
fk_evaluation_points_child_group | evaluation_point_groups_id ✅
|
||
fk_evaluation_points_parent_group | evaluation_point_groups_pid ✅
|
||
```
|
||
|
||
---
|
||
|
||
### 步骤 3: 重启应用并测试
|
||
|
||
```bash
|
||
npm run dev # 开发环境
|
||
# 或
|
||
npm run start:pm2:production:multi # 生产环境
|
||
```
|
||
|
||
访问评查点列表页面：`http://localhost:5173/rules/list`
|
||
|
||
**验证数据**：
|
||
打开浏览器开发者工具 → Network → 找到 `/evaluation_points` 请求 → 查看响应
|
||
|
||
**预期返回**（应该包含嵌套字段）：
|
||
```json
|
||
{
|
||
"id": 670,
|
||
"code": "04formcheck-vlm-cz",
|
||
"name": "表格抽取与评查（多模态）",
|
||
"evaluation_point_groups_id": 61,
|
||
"evaluation_point_groups_pid": 60,
|
||
"child_group": { ← ✅ 应该有
|
||
"id": 61,
|
||
"name": "多模态评查"
|
||
},
|
||
"parent_group": { ← ✅ 应该有
|
||
"id": 60,
|
||
"name": "卷宗类"
|
||
}
|
||
}
|
||
```
|
||
|
||
---
|
||
|
||
## 📊 为什么会有重复的外键？
|
||
|
||
### 原因分析
|
||
|
||
在之前的实现中，我们创建了外键约束的顺序：
|
||
|
||
1. **第一次**：执行 `database/add_foreign_keys.sql`
|
||
- 创建了 `fk_evaluation_points_group`
|
||
|
||
2. **第二次**：执行 `database/add_foreign_keys_simplified.sql`
|
||
- 创建了 `fk_evaluation_points_child_group`
|
||
- 创建了 `fk_evaluation_points_parent_group`
|
||
- **但没有删除旧的** `fk_evaluation_points_group`
|
||
|
||
结果：同一个列有两个外键约束！
|
||
|
||
---
|
||
|
||
## 🎓 PostgREST 嵌套查询的要求
|
||
|
||
### 1. 必须有外键约束
|
||
|
||
```sql
|
||
ALTER TABLE evaluation_points
|
||
ADD CONSTRAINT fk_evaluation_points_child_group
|
||
FOREIGN KEY (evaluation_point_groups_id)
|
||
REFERENCES evaluation_point_groups(id);
|
||
```
|
||
|
||
### 2. 外键名称必须唯一
|
||
|
||
- ❌ 一个列不能有多个外键
|
||
- ✅ 每个外键必须有唯一的名称
|
||
|
||
### 3. 查询语法必须指定外键名称
|
||
|
||
```javascript
|
||
// 格式：别名:表名!外键名(字段列表)
|
||
child_group:evaluation_point_groups!fk_evaluation_points_child_group(id, name)
|
||
```
|
||
|
||
### 4. 外键名称必须匹配
|
||
|
||
- 查询中的外键名：`fk_evaluation_points_child_group`
|
||
- 数据库中的外键名：`fk_evaluation_points_child_group`
|
||
- ✅ 必须完全一致
|
||
|
||
---
|
||
|
||
## 🔍 其他可能的问题
|
||
|
||
### 问题 1: select 参数包含换行符 ⚠️ 最常见
|
||
|
||
**症状**：
|
||
- 单独查询一个嵌套字段成功，但同时查询多个嵌套字段时全部失败
|
||
- PostgREST 报错 `PGRST100` 语法解析错误
|
||
- 错误信息包含 `unexpected "\\r"` 或 `unexpected "\\n"`
|
||
|
||
**原因**：PostgREST 的 select 参数不支持多行字符串。模板字符串中的换行符会被包含在 URL 查询参数中，导致解析失败。
|
||
|
||
**错误示例**：
|
||
```javascript
|
||
select: `
|
||
id,
|
||
name,
|
||
child_group:evaluation_point_groups!fk_evaluation_points_group(
|
||
id,
|
||
name
|
||
),
|
||
parent_group:evaluation_point_groups!fk_evaluation_points_parent_group(
|
||
id,
|
||
name
|
||
)
|
||
` // ❌ 包含换行符，导致解析失败
|
||
```
|
||
|
||
**正确写法**：
|
||
```javascript
|
||
select: `
|
||
id,
|
||
name,
|
||
child_group:evaluation_point_groups!fk_evaluation_points_group(id,name),
|
||
parent_group:evaluation_point_groups!fk_evaluation_points_parent_group(id,name)
|
||
`.replace(/\s+/g, ' ').trim() // ✅ 转换为单行字符串
|
||
```
|
||
|
||
**解决方案**：
|
||
1. 嵌套查询的字段列表写成单行：`(id,name)` 而不是多行
|
||
2. 使用 `.replace(/\s+/g, ' ').trim()` 将所有换行符和多余空格转换为单个空格
|
||
3. 或者直接写成完整的单行字符串（牺牲可读性）
|
||
|
||
---
|
||
|
||
### 问题 2: 外键不存在
|
||
|
||
**症状**：PostgREST 报错 `could not find foreign key relationship`
|
||
|
||
**解决**：创建外键约束
|
||
|
||
### 问题 3: 外键名称不匹配
|
||
|
||
**症状**：嵌套字段不返回，无报错
|
||
|
||
**原因**：代码中使用的外键名称与数据库中的不一致
|
||
|
||
**示例**：
|
||
```javascript
|
||
// 代码中使用
|
||
child_group:evaluation_point_groups!fk_evaluation_points_child_group(...)
|
||
|
||
// 但数据库中实际是
|
||
fk_evaluation_points_group ← 名称不匹配
|
||
```
|
||
|
||
**解决**：
|
||
1. 检查数据库中的实际外键名称
|
||
2. 重命名外键约束或修改代码
|
||
3. 推荐：重命名外键以匹配代码（见 `database/rename_foreign_key.sql`）
|
||
|
||
### 问题 3: 引用的表不存在或无权限
|
||
|
||
**症状**：PostgREST 报错 `permission denied`
|
||
|
||
**解决**：检查表权限
|
||
|
||
### 问题 4: 数据中关联ID为 NULL
|
||
|
||
**症状**：部分记录的嵌套字段为 `null`
|
||
|
||
**解决**：正常现象，表示没有关联数据
|
||
|
||
---
|
||
|
||
## 📋 完整检查清单
|
||
|
||
- [ ] **检查 select 字符串是否包含换行符**（最重要！）
|
||
- 嵌套查询字段列表应为单行：`(id,name)` 不是 `(\\n id,\\n name\\n)`
|
||
- 使用 `.replace(/\s+/g, ' ').trim()` 清理字符串
|
||
- [ ] 检查外键约束是否存在
|
||
- [ ] 确认每个列只有一个外键约束
|
||
- [ ] 验证外键名称与查询语法一致
|
||
- [ ] 检查引用表是否存在
|
||
- [ ] 确认数据库用户有查询权限
|
||
- [ ] 删除旧的或重复的外键约束
|
||
- [ ] 重启应用
|
||
- [ ] 测试 API 响应是否包含嵌套字段
|
||
|
||
---
|
||
|
||
## 🚀 快速修复命令
|
||
|
||
```bash
|
||
# 1. 删除重复的外键
|
||
psql -h <host> -U <username> -d <database> -c "
|
||
ALTER TABLE evaluation_points
|
||
DROP CONSTRAINT IF EXISTS fk_evaluation_points_group;
|
||
"
|
||
|
||
# 2. 验证结果
|
||
psql -h <host> -U <username> -d <database> -c "
|
||
SELECT constraint_name, column_name
|
||
FROM information_schema.key_column_usage
|
||
WHERE table_name = 'evaluation_points'
|
||
AND column_name IN ('evaluation_point_groups_id', 'evaluation_point_groups_pid')
|
||
ORDER BY column_name;
|
||
"
|
||
|
||
# 3. 重启应用
|
||
npm run dev
|
||
```
|
||
|
||
---
|
||
|
||
## 📚 参考资料
|
||
|
||
- [PostgREST 嵌套查询文档](https://postgrest.org/en/stable/references/api/resource_embedding.html)
|
||
- [PostgreSQL 外键约束文档](https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK)
|