如何有效连接3个表M:N

Question

我有几张桌子：

应用程序

id | name | url_key
===================
1  | Hello World | hello-world
2  | Snake 2 | snake-2

开发人员

id | name | url_key
===================
1  | Mr. Robinson | mr-robinson
2  | Richard | richard
3  | Nokia | nokia

Apps-Developers-Assignment

app_id | developer_id
=====================
1      | 1
1      | 2
2      | 3

因此，正如您可能看到的，一个应用程序可能有多个开发人员。

我真正需要的是编写一个PostgreSQL查询，以选择所有的应用程序，并提供一些有关相关开发人员的有用信息。像这样的事情：

Hello World | 1-hello-world | Mr.Robinson<1-mr-robinson> /and 1 more/
Snake 2     | 2-snake-2     | Nokia<3-nokia>

因此，如果应用程序有一个开发人员，只需将开发人员的姓名、url键和id附加到应用程序中，如果有更多的开发人员，则追加第一个开发人员，并附加“有多少开发人员”的信息。

最重要的是--，我需要最终过滤结果(例如，不返回某个指定开发人员的应用程序)。

我知道可以通过编写应用程序本身的第一个查询，然后在每一行发送下一个查询来解决这个问题，但我认为这不是一个好方法。

如果我只是简单地加入这张桌子：

SELECT * FROM apps a
LEFT JOIN apps_developers_assignment ada ON a.id = ada.app_id
LEFT JOIN developers d ON ada.developer_id = d.id

Hello World | 1-hello-world | Mr.Robinson<1-mr-robinson>
Hello World | 1-hello-world | Richard<2-richard>
Snake 2     | 2-snake-2     | Nokia<3-nokia>

它返回重复的应用程序..。我不知道如何通过开发人员过滤这些结果(正如我上面所写的)。

Answer 1

假设您只想筛选出一个开发人员，则需要传递一个参数。为此，我调用了这个参数/变量@excludeDevId。

我更熟悉the，因此这可能/也需要Postgres中的等效语法。另外，由于左联接为null，所以我将保留null--从您的输出中，您可能希望合并(可能还有其他一些输出调整)。这是没有经验和未经测试的，但至少应该足以让你朝着正确的方向前进：

SELECT 
    a.name AppName, 
    a.id AppId,
    d.id DevId,
    d.url_key Url,
    d.name DevName,
    CAST(agg.dev_count - 1 as VARCHAR) + ' more'
FROM 
    (
    SELECT 
        MAX(ada.developer_id) first_dev_id, 
        COUNT(ada.developer_id) dev_count, 
        app_id
    FROM
        apps_developers_assignment ada 
    WHERE 
        d.id != @excludeDevId
    GROUP BY
        app_id
    ) agg
    INNER JOIN apps a ON a.id = agg.app_id
    LEFT JOIN developers d ON agg.developer_id = d.id

Answer 2

如果要使用左联接，则需要将条件放在联接上，否则它将设置一个内部联接，并且不返回空值的行.试着做这样的事情：

SELECT * FROM apps a
LEFT JOIN apps_developers_assignment ada ON a.id = ada.app_id
                                            AND ada.developerid = :devid

LEFT JOIN developers d ON ada.developer_id = d.id

WHERE a.id = :appid