我继承了一些表，我正试图清理，但首先我试图加入我需要的所有内容，但有问题，因为有不止一种方法可以到达表SpecialEvents到EventRegistrations。
在某些情况下，可以使用EventRegistrations直接连接event_registrations.scoreable_id，而在其他情况下，必须首先连接另一个表SpecialPlaces，您可以知道需要通过event_registrations.scoreable_type哪种方式，即SpecialEvent或SpecialPlace。
基本上，如果我必须先加入SpecialEvents的话，我怎样才能加入SpecialPlaces？如果我尝试以两种不同的方式加入SpecialEvents，就会得到一个错误：“table name”special_events“指定了多次”。
SELECT event_registrations.id, array_agg(teams.name), event_registrations.number_of_players, event_registrations.state, event_registrations.created_at, array_agg(players.email), array_agg(special_events.name), array_agg(special_places.id)
FROM event_registrations
LEFT JOIN teams ON event_registrations.team_id = teams.id
LEFT JOIN team_memberships ON teams.id = team_memberships.team_id
LEFT JOIN players ON team_memberships.player_id = players.id
LEFT JOIN special_events ON event_registrations.scoreable_id = special_events.id AND event_registrations.scoreable_type = 'SpecialEvent'
LEFT JOIN special_places ON event_registrations.scoreable_id = special_places.id AND event_registrations.scoreable_type = 'SpecialPlace'
GROUP BY event_registrations.id, event_registrations.number_of_players, event_registrations.state, event_registrations.created_at
特产
+----+-----------+---------------------------+-----------+---------------------------+
| id | region_id | start_at | state | created_at |
+----+-----------+---------------------------+-----------+---------------------------+
| 2 | 1 | 2015-10-22 19:30:00 +0100 | published | 2015-09-21 09:41:05 +0100 |
| 4 | 1 | 2016-01-21 19:30:00 +0000 | published | 2015-11-26 15:11:25 +0000 |
| 3 | 1 | 2016-01-28 19:30:00 +0000 | published | 2015-11-23 16:16:27 +0000 |
| 5 | 1 | 2016-12-31 19:30:00 +0000 | draft | 2016-02-24 15:17:22 +0000 |
| 6 | 1 | 2016-05-16 19:30:00 +0100 | published | 2016-03-29 14:33:40 +0100 |
| 10 | 1 | 2016-09-12 19:30:00 +0100 | published | 2016-06-28 17:18:54 +0100 |
| 8 | 1 | 2016-10-07 19:30:00 +0100 | draft | 2016-06-09 15:03:36 +0100 |
| 7 | 1 | 2016-05-23 19:30:00 +0100 | published | 2016-03-30 19:30:21 +0100 |
| 9 | 1 | 2016-08-04 19:30:00 +0100 | published | 2016-06-09 15:18:56 +0100 |
| 11 | 1 | 2016-11-07 19:30:00 +0000 | draft | 2016-07-11 17:20:11 +0100 |
+----+-----------+---------------------------+-----------+---------------------------+
特殊位置
+----+------------------+----------+---------------------------+
| id | special_event_id | place_id | created_at |
+----+------------------+----------+---------------------------+
| 1 | 2 | 243 | 2015-10-12 18:07:09 +0100 |
| 3 | 2 | 83 | 2015-10-15 15:54:40 +0100 |
| 5 | 4 | 262 | 2015-11-26 16:29:35 +0000 |
| 4 | 3 | 262 | 2015-11-23 16:25:31 +0000 |
| 6 | 5 | 281 | 2016-02-24 15:20:33 +0000 |
| 7 | 6 | 262 | 2016-03-29 14:34:00 +0100 |
| 8 | 7 | 262 | 2016-04-11 13:28:00 +0100 |
| 9 | 8 | 262 | 2016-06-09 15:03:52 +0100 |
| 12 | 11 | 262 | 2016-07-11 17:20:26 +0100 |
| 10 | 9 | 262 | 2016-06-09 15:20:08 +0100 |
+----+------------------+----------+---------------------------+
活动登记
+----+---------+--------------+----------------+-------+---------------------------+
| id | team_id | scoreable_id | scoreable_type | state | created_at |
+----+---------+--------------+----------------+-------+---------------------------+
| 1 | 3979 | 2 | SpecialEvent | draft | 2015-11-30 10:09:06 +0000 |
| 2 | 3717 | 2 | SpecialEvent | draft | 2015-11-30 10:09:06 +0000 |
| 3 | 3626 | 8 | SpecialPlace | draft | 2015-11-30 10:09:06 +0000 |
| 4 | 3202 | 8 | SpecialPlace | draft | 2015-11-30 10:09:06 +0000 |
| 5 | 703 | 2 | SpecialEvent | draft | 2015-11-30 10:09:06 +0000 |
| 6 | 278 | 2 | SpecialEvent | draft | 2015-11-30 10:09:06 +0000 |
| 7 | 3166 | 2 | SpecialEvent | draft | 2015-11-30 10:09:06 +0000 |
| 8 | 3147 | 2 | SpecialEvent | draft | 2015-11-30 10:09:06 +0000 |
| 9 | 3146 | 2 | SpecialEvent | draft | 2015-11-30 10:09:06 +0000 |
| 10 | 3145 | 2 | SpecialEvent | draft | 2015-11-30 10:09:06 +0000 |
+----+---------+--------------+----------------+-------+---------------------------+
最佳答案
我的同事们想说的是，你想做的事情是不可行的，然而，有无数种方法可以做同样的事情。
要避免两个连接，可以做的是创建一个包含所有所需信息的SpecialEvents和specialplace的组合表，然后连接它。
像这样的东西：
SELECT event_registrations.id, array_agg(teams.name), event_registrations.number_of_players, event_registrations.state, event_registrations.created_at, array_agg(players.email), array_agg(special_events.name), array_agg(special_places.id)
FROM event_registrations
LEFT JOIN teams ON event_registrations.team_id = teams.id
LEFT JOIN team_memberships ON teams.id = team_memberships.team_id
LEFT JOIN players ON team_memberships.player_id = players.id
LEFT JOIN special_places ON event_registrations.scoreable_id = special_places.id AND event_registrations.scoreable_type = 'SpecialPlace'
LEFT JOIN (
SELECT special_events.id AS special_event_id, special_places.id AS special_place_id, special_events.name
FROM special_places
LEFT JOIN special_events ON special_places.special_event_id = special_events.id
UNION
SELECT special_events.id AS special_event_id, null AS special_place_id, special_events.name
FROM special_events
) el1
ON (event_registrations.scoreable_id = el1.special_place_id AND event_registrations.scoreable_type = 'SpecialPlace') OR (event_registrations.scoreable_id = el1.special_event_id AND event_registrations.scoreable_type = 'SpecialEvent')
GROUP BY event_registrations.id, event_registrations.number_of_players, event_registrations.state, event_registrations.created_at