我將資料存盤在兩個名為 objects 和 object_relationships 的表中。這是一個簡單的多對多自我參照。
物件表
| ID | 描述 | 型別 |
|---|---|---|
| 1 | 主題：關于鳥類的電子郵件 | 電子郵件 |
| 2 | 題目：鳥 | 電子郵件 |
| 3 | 約翰 | 人 |
| 4 | 標記 | 人 |
| 5 | 法 | 人 |
| 6 | 題目：螞蟻 | 電子郵件 |
表之間的單詞以修復 SE 格式
物件關系表
| object_id | child_id | 型別 |
|---|---|---|
| 1 | 3 | 至 |
| 3 | 1 | 從 |
| 6 | 4 | 至 |
| 5 | 4 | 家庭 |
| 2 | 5 | 從 |
| 5 | 3 | 朋友們 |
使用初始查詢，例如
select * from objects where description like '%birds%' or description like '%lex%' or description like '%john%'
回傳 id 的 [1, 2, 3, 5]
然后我特別想要這些“節點”之間的每一條邊：
- 1 - 到 - 3
- 3 - 從 - 1
- 2 - 從 - 5
- 5 - 朋友 - 3
我使用連接獲取邊緣的代碼但它是錯誤的，因為它引入了新節點，我無法弄清楚如何排除初始查詢之外的節點。
我認為我的方法是錯誤的，因為查詢甚至沒有考慮物件的父母。json 構建物件是在任何兼容 cytoscape 的查看器中快速繪制輸出
json_build_object(
'source', base.object_id,
'target', base.child_id,
'type', base.child_type
) as edge1,
json_build_object(
'source', base.child_id,
'target', base.child2_id,
'type', base.child2_type
) as edge2 from
(
with parent as (
select
distinct unnest(array[base.object_id, base.child_id, base.child2_id]) as id
from
(
select
o.id as object_id,
o.type,
or1.child_object_id as child_id,
or1."type" as child_type
or2.child_object_id as child2_id,
or2."type" as child2_type
from
objects o
join object_relationships or1 on
or1.object_id = o.id
join objects o1 on
o1.id = or1.child_object_id
join object_relationships or2 on or2.object_id = o1.id
join objects o2 on o2.id = or2.child_object_id
where
o.description like '%birds%' or o.description like '%lex%' or o.description like '%john%'
limit 1) base
limit 100)
select
o.id as object_id,
or1.child_object_id as child_id,
or1."type" as child_type,
or2.child_object_id as child2_id,
or2."type" as child2_type
from
parent p
join objects o on
o.id = p.id
join object_relationships or1 on
or1.object_id = o.id
join objects o1 on
o1.id = or1.child_object_id
join object_relationships or2 on
or2.object_id = o1.id
join objects o2 on
o2.id = or2.child_object_id
limit 100) base;
uj5u.com熱心網友回復：
ids從表中獲取陣列object并按陣列元素搜索行object_relationships。
select object_id, type, child_id
from (
select array_agg(id) as ids
from objects
where description like any('{%birds%, %lex%, %john%}')
) s
join object_relationships
on object_id = any(ids) and child_id = any(ids)
order by object_id, child_id;
在db<>fiddle中測驗它。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/516615.html