title: How to simply and efficiently query for nested relationships in SQL?
body: <p>I'm looking to write the simplest, most efficient SQL query to retrieve all of the <code>events</code> that are related to a given <code>user</code>.</p>
  
  <hr>
  
  <h1>Setup</h1>
  
  <p>Here's a simplistic representation of what my schema looks like:</p>
  
  <p><a href="https://i.stack.imgur.com/NUOOT.png" rel="noreferrer"><img src="https://i.stack.imgur.com/NUOOT.png" alt="enter image description here"></a></p>
  
  <p>A couple things to note:</p>
  
  <ul>
  <li><code>users</code> belong to <code>teams</code> via <code>memberships</code>.</li>
  <li><code>teams</code> can have many <code>collections</code>, <code>apps</code>, and <code>webhooks</code>.</li>
  <li><code>collections</code> can also have many <code>webhooks</code>.</li>
  <li><code>webhooks</code> can belong to either a <code>team</code> or <code>collection</code>, but only one.</li>
  <li><code>events</code> can belong to any object, but only one.</li>
  </ul>
  
  <p>This seems like a fairly basic setup that most SaaS-type companies would have (eg. Slack or Stripe). Everything is "owned" by the teams, but users belong to teams and interact with the interface.</p>
  
  <hr>
  
  <h1>Problem</h1>
  
  <p>Given that setup, I'd like to create a SQL query that solves...</p>
  
  <blockquote>
    <p>Find all of the events that are related (directly or indirectly) to a given user by <code>id</code>.</p>
  </blockquote>
  
  <p>I can easily write queries that find either directly, or indirectly via a specific means. For example...</p>
  
  <blockquote>
    <p>Find all of the events that are <strong>directly</strong> related to a user by <code>id</code>.</p>
  </blockquote>
  
  <pre><code>SELECT *
  FROM events
  WHERE user_id = ${id}
  </code></pre>
  
  <p>Or...</p>
  
  <blockquote>
    <p>Find all of the events that are <strong>indirectly</strong> related to a user via their teams.</p>
  </blockquote>
  
  <pre><code>SELECT events.*
  FROM events
  JOIN memberships ON memberships.team_id = events.team_id
  WHERE memberships.user_id = ${id}
  </code></pre>
  
  <p>Or even...</p>
  
  <blockquote>
    <p>Find all of the events that are <strong>indirectly</strong> related to a user via any collections of their teams.</p>
  </blockquote>
  
  <pre><code>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}
  </code></pre>
  
  <p>Webhooks get a more complex, because they can be related in two different ways...</p>
  
  <blockquote>
    <p>Find all of the events that are <strong>indirectly</strong> related to a user via any webhooks of their teams or collections.</p>
  </blockquote>
  
  <pre><code>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}
  )
  </code></pre>
  
  <p>But as you can see, there are a lot of different ways for a user to be related to an event that occured, via all those paths! So when I try to a query that successfully gets all of those related events, it ends up looking like...</p>
  
  <pre><code>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}
  )
  </code></pre>
  
  <hr>
  
  <h1>Questions</h1>
  
  <ul>
  <li>Is that final "all included" query very inefficient?</li>
  <li>Is there a more efficient way to write it?</li>
  <li>Is there a simpler, easier-to-read-later way to write it?</li>
  </ul>