在我的设置中,此问题的相关表是:
- 项目
- 用户
- 集合(项目)——每个用户可以有多个
- 集合项目 — 我在其中跟踪每个集合中的项目。
和表非常简单,这里是和表(是我的模式的名称)items
:users
collections
collection_items
mi
CREATE TABLE IF NOT EXISTS mi.users (
id UUID DEFAULT uuid_generate_v4 () CONSTRAINT users_pk PRIMARY KEY,
created_at TIMESTAMP DEFAULT now(),
name VARCHAR(50) UNIQUE NOT NULL CONSTRAINT min_length_users_name CHECK (char_length(name) >= 4),
email VARCHAR UNIQUE NOT NULL,
pic BYTEA
);
CREATE TABLE IF NOT EXISTS mi.items (
id UUID DEFAULT uuid_generate_v4 () CONSTRAINT items_pk PRIMARY KEY,
created_at TIMESTAMP DEFAULT now(),
author_id UUID NOT NULL CONSTRAINT item_author_fk REFERENCES mi.users (id),
title VARCHAR(300) NOT NULL CONSTRAINT min_length_items_title CHECK (char_length(title) >= 4),
description VARCHAR(5000) CONSTRAINT min_length_items_description CHECK (char_length(description) >= 5)
);
CREATE TABLE IF NOT EXISTS mi.collections (
id UUID DEFAULT uuid_generate_v4 () CONSTRAINT collections_pk PRIMARY KEY,
created_at TIMESTAMP DEFAULT now(),
author_id UUID NOT NULL CONSTRAINT collection_author_fk REFERENCES mi.users (id),
title VARCHAR(300) NOT NULL CONSTRAINT min_length_items_title CHECK (char_length(title) >= 4),
description VARCHAR(5000) CONSTRAINT min_length_items_description CHECK (char_length(description) >= 5)
);
CREATE TABLE IF NOT EXISTS mi.collection_items (
id UUID DEFAULT uuid_generate_v4 () CONSTRAINT collection_items_pk PRIMARY KEY,
created_at TIMESTAMP DEFAULT now(),
collection_id UUID NOT NULL CONSTRAINT collection_items_collection_fk REFERENCES mi.collections (id),
item_id UUID NOT NULL CONSTRAINT collection_items_item_id REFERENCES mi.items (id),
CONSTRAINT you_can_only_add_an_item_once_to_a_collection UNIQUE (collection_id, item_id)
);
我想要的是,在查询集合时,还以 JSON 对象的 JSON 数组返回其项目(我正在使用node-pg
,这就是为什么有带有 的变量${}
):
SELECT
mi.collections.created_at,
jsonb_build_object(
'id', mi.collections.author_id,
'name', mi.users.name,
'pic', mi.users.pic
) author,
mi.collections.title,
mi.collections.description,
(
SELECT COUNT(*)
FROM mi.collection_items
WHERE mi.collection_items.collection_id = '${collectionId}'
) AS total_items,
-- This is where I'm trying to get it done.
jsonb_build_array(
SELECT
jsonb_build_object(
'id', mi.items.id,
'title', mi.items.title,
'description', mi.items.description
) item
FROM mi.collection_items
JOIN mi.items ON mi.collection_items.item_id = mi.items.id
WHERE mi.collection_items.collection_id = '${collectionId}'
ORDER BY mi.collection_items.created_at DESC
OFFSET ${offset}
LIMIT ${paginationLimit}
)
FROM mi.collections
JOIN mi.users ON mi.users.id = mi.collections.author_id
WHERE mi.collections.id = '${collectionId}'
该查询将产生以下形式的结果:
{
"created_at": "2023-07-10T20:54:34.883Z",
"title": "Title 1",
"description": "A great description",
"author": {
"id": "906ca589-db0f-4c6e-add4-220cc3f2ea95",
"name": "john",
"pic": "\\x2f686f6d652f7068696c692f436f64652f6d61676e75735f696e6465782f6261636b656e642f6173736574732f737461636b6f766572666c6f775f7069635f312e706e67"
},
"total_items": 2,
"items": [
{
"id": "906ca589-abcd-4c6e-add4-220cc3f2ea95",
"title": "Title 1",
"description": "Description 1"
},
{
"id": "906ca589-efgh-4c6e-add4-220cc3f2ea95",
"title": "Title 2",
"description": "Description 2"
}
]
}
您不能使用
jsonb_build_array
一组行来构建数组,它只接受标量值。所以你需要jsonb_agg
在子查询中使用但我建议您学习如何使用表别名,以使其更具可读性。另外,我不确定你的客户端驱动程序,但它看起来很像危险的SQL 注入。此外,您可以
collection_id
通过使用相关谓词将自己限制为仅过滤一次。