Postgers查询-返回多到多关系的数据

Question

我有以下表格：stores，items，promotions，promotion_stores，promotion_items。

• 每个商店都链接到促销和商品(商店中的商品)。
• 每个促销活动可以包含几个项目。

我的目标是返回对象的JSON列表，其中包含特定存储的以下数据(通过stores.id)：

1. promotion_items.id (来自promotions)
2. promotion_items，描述(来自promotions)
3. promotion_items.discounted_price (来自促销)
4. 使用promotion_items链接到此促销的项目列表。

这些桌子看起来如下：

stores: id, name
items: id, store_id, price, other metadata..
promotion_stores: store_id,site_id,promotion_id
promotion_items: promotion_id, item_id
promotions: id, discounted_price, description

到目前为止，我所做的--效率很低--使用了3个查询：

1. 查询所有相关的促销活动。使用：SELECT promotion_id FROM promotion_stores WHERE store_id = ${branch}
2. 对于每个升级ID，在promotion_items查询所有项(另一个查询)
3. 对于每个项ID，查询包含3个不同查询的items表(另一个查询)，它们彼此解耦。

我的目标是:对DB进行一次查询，效率更高，无需SQL代码，并根据结果构建一个JSON。

我怎么能这么做？

Answer 1

您必须在一个查询中将表连接在一起，如下所示：

SELECT to_jsonb(p.*) || to_jsonb(i.*) 
  FROM promotion_stores AS ps
 INNER JOIN promotions AS p
    ON p.id = ps.promotion_id
 INNER JOIN promotion_items AS pi
    ON pi.promotion_id = p.id
 INNER JOIN items AS i
    ON i.id = pi.item_id
   AND i.store_id = ps.store_id
 WHERE ps.store_id = ${branch} ;

请参阅[医]小提琴中的测试结果。