如何简单有效地查询SQL中的嵌套关系?[英] How to simply and efficiently query for nested relationships in SQL?

本文是小编为大家收集整理的关于如何简单有效地查询SQL中的嵌套关系?的处理方法,想解了如何简单有效地查询SQL中的嵌套关系?的问题怎么解决?如何简单有效地查询SQL中的嵌套关系?问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

我希望编写最简单、最有效的 SQL 查询来检索与给定 user 相关的所有 events.

<小时>

设置

这是我的架构的简单表示:

在此处输入图片描述

需要注意的几点:

  • users 通过 memberships 属于 teams.
  • teams 可以有多个 collections、apps 和 webhooks.
  • collections 也可以有多个 webhooks.
  • webhooks 可以属于 team 或 collection,但只能属于一个.
  • events 可以属于任何对象,但只能属于一个.

这似乎是大多数 SaaS 类型的公司(例如 Slack 或 Stripe)都会拥有的相当基本的设置.一切都归团队"拥有",但用户属于团队并与界面交互.

<小时>

问题

鉴于该设置,我想创建一个 SQL 查询来解决...

<块引用>

查找与 id 给定用户相关(直接或间接)的所有事件.

我可以轻松编写直接或通过特定方式间接查找的查询.比如……

<块引用>

查找与 id 的用户直接相关的所有事件.

SELECT *
FROM events
WHERE user_id = ${id}

或者……

<块引用>

通过其团队查找与用户间接相关的所有事件.

SELECT events.*
FROM events
JOIN memberships ON memberships.team_id = events.team_id
WHERE memberships.user_id = ${id}

甚至……

<块引用>

通过用户团队的任何集合查找与用户间接相关的所有事件.

SELECT events.*
FROM events
JOIN collections ON collections.id = events.collection_id
JOIN memberships ON memberships.team_id = collections.team_id
WHERE memberships.user_id = ${id}

Webhook 变得更加复杂,因为它们可以通过两种不同的方式关联...

<块引用>

通过用户团队或集合的任何 webhook 查找与用户间接相关的所有事件.

SELECT *
FROM events
WHERE webhook_id IN (
  SELECT webhooks.id
  FROM webhooks
  JOIN memberships ON memberships.team_id = webhooks.team_id
  WHERE memberships.user_id = ${id}
)
OR webhook_id IN (
  SELECT webhooks.id
  FROM webhooks
  JOIN collections ON collections.id = webhooks.collection_id
  JOIN memberships ON memberships.team_id = collections.team_id
  WHERE memberships.user_id = ${id}
)

但正如您所见,用户通过所有这些路径与发生的事件相关联的方式有很多!因此,当我尝试成功获取所有这些相关事件的查询时,它最终看起来像......

SELECT * 
FROM events
WHERE user_id = ${id}
OR app_id IN (
  SELECT apps.id
  FROM apps
  JOIN memberships ON memberships.team_id = apps.team_id
  WHERE memberships.user_id = ${id}
)
OR collection_id IN (
  SELECT collections.id
  FROM collections
  JOIN memberships ON memberships.team_id = collections.team_id
  WHERE memberships.user_id = ${id}
)
OR memberships_id IN (
  SELECT id
  FROM memberships
  WHERE user_id = ${id}
)
OR team_id IN (
  SELECT team_id
  FROM memberships
  WHERE user_id = ${id}
)
OR webhook_id IN (
  SELECT webhooks.id
  FROM webhooks
  JOIN memberships ON memberships.team_id = webhooks.team_id
  WHERE memberships.user_id = ${id}
)
OR webhook_id IN (
  SELECT webhooks.id
  FROM webhooks
  JOIN collections ON collections.id = webhooks.collection_id
  JOIN memberships ON memberships.team_id = collections.team_id
  WHERE memberships.user_id = ${id}
)
<小时>

问题

  • 最后的"全部包含"查询效率很低吗?
  • 有没有更高效的写法?
  • 有没有更简单、以后更容易阅读的方式来编写它?

推荐答案

与任何查询一样,最有效的方法是"取决于".有很多变量在起作用 - 表中的行数、行长度、索引是否存在、服务器上的 RAM 等等.

我能想到的处理此类问题的最佳方法(考虑可维护性和提高效率的粗略方法)是使用 CTE,它允许您创建临时结果并在整个查询中重用该结果.CTE 使用 WITH 关键字,本质上将结果别名为表,以便您可以多次 JOIN:

WITH user_memberships AS (
    SELECT *
    FROM memberships
    WHERE user_id = ${id}
), user_apps AS (
    SELECT *
    FROM apps
    INNER JOIN user_memberships
        ON user_memberships.team_id = apps.team_id
), user_collections AS (
    SELECT *
    FROM collections
    INNER JOIN user_memberships
        ON user_memberships.team_id = collections.team_id
), user_webhooks AS (
    SELECT *
    FROM webhooks
    LEFT OUTER JOIN user_collections ON user_collections.id = webhooks.collection_id
    INNER JOIN user_memberships
        ON user_memberships.team_id = webhooks.team_id
        OR user_memberships.team_id = user_collections.team_id
)

SELECT events.* 
FROM events
WHERE app_id IN (SELECT id FROM user_apps)
OR collection_id IN (SELECT id FROM user_collections)
OR membership_id IN (SELECT id FROM user_memberships)
OR team_id IN (SELECT team_id FROM user_memberships)
OR user_id = ${id}
OR webhook_id IN (SELECT id FROM user_webhooks)
;

这样做的好处是:

  1. 每个 CTE 都可以利用适当的 JOIN 谓词上的索引并更快地返回该子集的结果,而不是让执行计划程序尝试解析一系列复杂的谓词
  2. 可以单独维护 CTE,从而更轻松地解决子集问题
  3. 您没有违反 DRY 原则
  4. 如果 CTE 在查询之外有值,您可以将其移动到存储过程中并改为引用它

本文地址:https://www.itbaoku.cn/post/1763792.html