How to query the "parent" of arbitrary child relationships in SQL?

Question

I want to write the simplest, most efficient SQL query to retrieve one teamof the given event. But it eventmay not be directly related to team, it may be connected at any level of the table relationship hierarchy.

---

Customization

Here's a simplified view of what my circuit looks like:

A few notes:

• teamscan have a lot collections, appsand webhooks.
• collectionsmay also have a lot webhooks.
• webhooksmay belong to either team, or collection, but only to one.
• events can belong to any object, but only to one.

, SaaS (, Slack Stripe). "", .

---

, SQL-, ...

id.

, , . ...

" ".

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

...

.

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

Webhooks , ...

" -".

SELECT teams.*
FROM teams
JOIN collections ON collections.team_id = teams.id
JOIN webhooks AS team_webhooks ON team_webhooks.team_id = teams.id
JOIN webhooks AS collection_webhooks ON collection_webhooks.collection_id = collections.id
JOIN events AS team_webhook_events ON team_webhook_events.webhook_id = team_webhooks.id
JOIN events AS collection_webhook_events ON collection_webhook_events.webhook_id = collection_webhooks.id
WHERE team_webhook_events.id = ${id}
OR collection_webhook_events.id = ${id}

, , UNION ...

SELECT teams.*
FROM teams
JOIN webhooks ON webhooks.team_id = teams.id
JOIN events ON events.webhook_id = webhooks.id
WHERE events.id = ${id}

UNION

SELECT teams.*
FROM teams
JOIN collections ON collections.team_id = teams.id
JOIN webhooks ON webhooks.collection_id = collections.id
JOIN events ON events.webhook_id = webhooks.id
WHERE events.id = ${id}

... unsure, .

---

, , ! , " ", ...

№ 1

UNION, , , , ?

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

UNION

SELECT teams.*
FROM teams
JOIN apps ON apps.team_id = teams.id
JOIN events ON events.app_id = apps.id
WHERE events.id = ${id}

UNION

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

UNION

SELECT teams.*
FROM teams
JOIN webhooks ON webhooks.team_id = teams.id
JOIN events ON events.webhook_id = webhooks.id
WHERE events.id = ${id}

UNION

SELECT teams.*
FROM teams
JOIN collections ON collections.team_id = teams.id
JOIN webhooks ON webhooks.collection_id = collections.id
JOIN events ON events.webhook_id = webhooks.id
WHERE events.id = ${id}

№2

JOIN , teams? JOIN s...

SELECT teams.*
FROM teams

JOIN apps ON apps.team_id = teams.id
JOIN collections ON collections.team_id = teams.id
JOIN webhooks AS team_webhooks ON team_webhooks.team_id = teams.id
JOIN webhooks AS collection_webhooks ON collection_webhooks.collection_id = collections.id

JOIN events AS app_events ON app_events.app_id = apps.id
JOIN events AS collection_events ON collection_events.collection_id = collections.id
JOIN events AS team_events ON team_events.team_id = teams.id
JOIN events AS collection_webhook_events ON collection_webhook_events.webhook_id = collection_webhooks.id
JOIN events AS team_webhook_events ON team_webhook_events.webhook_id = team_webhooks.id

WHERE app_events.id = ${id}
OR collection_events.id = ${id}
OR team_events.id = ${id}
OR collection_webhook_events.id = ${id}
OR team_webhook_events.id = ${id}

---

• - ?
• ?
• , ?

---

: , !

" SQL?" , ( ) . , , .

-, , ...

, ( ) .. , "" , .

, ...

. , "" , , . (, , .)

, . , -, , . (), , , , , , .

, !

: , ! : D

Answer 1

:

, team_id.

select coalesce(e.team_id,a.team_id,c.team_id,w.team_id) eteam_id 
from events e 
   left join apps a on e.app_id=a.id 
   left join collections c on c.id=e.collection_id 
   left join webhooks w on w.id=e.webhook_id 
   left join teams t on t.id=e.team_id 
where e.id=${id};

, :

select t.* from teams t 
join 
(select coalesce(e.team_id,a.team_id,c.team_id,w.team_id) eteam_id
from events e left join apps a on e.app_id=a.id 
left join collections c on c.id=e.collection_id 
left join webhooks w on w.id=e.webhook_id 
left join teams t on t.id=e.team_id 
where e.id=1) t1 
on t.id=eteam_id;

Answer 2

OR .

teams, ( ), () .

---

SELECT * 
FROM teams t
WHERE EXISTS (
        SELECT 1
        FROM events e
        WHERE e.SOME_FIELD = SOME_EVENT
        AND
          (     team_id = t.id  -- direct reference
        OR EXISTS (             -- reference via webhooks
                SELECT 1 FROM webhooks w
                WHERE  w.id = e.webhook_id
                AND w.team_id = t.id
                )
        OR EXISTS (             -- reference via apps
                SELECT 1 FROM apps a
                WHERE  a.id = e.app_id
                AND a.team_id = t.id
                )
        OR EXISTS (             -- reference via collections
                SELECT 1 FROM collections c
                WHERE  c.id = e.collection_id
                AND c.team_id = t.id
                )
        OR EXISTS (             -- reference via webhooks*collections
                SELECT 1 FROM webhooks w
                JOIN collections c ON w.collection_id = c.id
                WHERE  w.id = e.webhook_id
                AND c.team_id = t.id
                WHERE c.team_id = t.id
                )
          )
        ) ;