时间戳

首页 > 解决方案 > 查询 M:N 关系中的所有 N 个元素

sql - 查询 M:N 关系中的所有 N 个元素

问题描述

假设我有以下表格，这些表格对附加到文章的标签进行建模：

articles (article_id, title, created_at, content)
tags (tag_id, tagname)
articles_tags (article_fk, tag_fk)

n检索带有所有附加标签名的最新文章的惯用方法是什么？这似乎是一个标准问题，但我是 SQL 新手，不知道如何优雅地解决这个问题。

从应用程序的角度来看，我想编写一个函数，它返回表单的记录列表[title, content, [tags]]，即，附加到文章的所有标签都将包含在可变长度列表中。SQL 关系没有那么灵活。到目前为止，我只能考虑一个查询来连接为每个文章/标签组合返回一个新行的表，然后我需要以编程方式将其压缩为上述形式。

或者，我可以想到一个解决方案，我发出两个查询：首先，对于文章；第二，inner join关于链接表和标签表。那么，在应用程序中，我可以过滤每个结果集article_id以获得给定文章的所有标签吗？后者似乎是一个相当冗长和低效的解决方案。

我错过了什么吗？是否有规范的方法来制定单个查询？还是单个查询加上较小的后处理？

除了简单的 SQL 问题之外，在 Opaleye DSL 中相应的查询会是什么样子？也就是说，如果它可以翻译呢？

标签： sqlpostgresqljoinsubqueryopaleye

解决方案

您通常会使用限制行的查询来选择文章并按日期降序排列，并使用带有聚合函数的连接或相关子查询来生成标签列表。

以下查询为您提供了 10 篇最新文章，以及它们在数组中的相关标签的名称：

select 
    a.*,
    (
        select array_agg(t.tagname) 
        from article_tags art
        inner join tags t on t.tag_id = art.tag_fk
        where art.article_fk = a.article_id
    ) tags
from articles
order by a.created_at desc
limit 10

推荐阅读