（Postgre）SQL中的一对多关系 [英] One-to-Many relationships in (Postgre)SQL

问题描述

我有两个表：

帖子：

 id | ... other stuff ... |     tags                         
----+---------------------+--------------
  1 |         ...         | <foo><bar>
  2 |         ...         | <foo><baz><blah>
  3 |         ...         | <bar><blah><goo>

和标签：

     tag         
--------------
 <foo>
 <bar>
 <baz>
 <blah>
 <goo>

posts.tags和tags.tag都是文本类型。我想要的是从tag.tag到帖子中的行的关系，这样查询< foo> 会给我对应于帖子1和2的行，查询< blah> 给我2和3，&bar; 给我1和3，等等。

posts.tags and tags.tag are both of type text. What I want is a relation from tags.tag to rows in posts such that querying <foo> would give me rows corresponding to posts 1 and 2, querying <blah> gives me 2 and 3, <bar> gives me 1 and 3, etc.

我看过外键，但是我不确定这就是我想要的。 （老实说，我不确定该怎么做）。据我所知，外键必须等于表的主键/唯一列。但是我想要的是所有这样的行，例如 posts.tags〜'。*< foo>。*'等。我也希望能够得到所有以b开头的标签，例如：

I've looked at foreign keys, but I'm not sure it's what I want. (and honestly, I'm not exactly sure what it does). From what I can tell a foreign key must be equal to a primary key/unique column of a table. But what I want is all rows such that posts.tags ~ '.*<foo>.*', etc. I also want to be able to, say, get all tags that start with b, eg:

CREATE VIEW startswithB AS
SELECT tag
FROM tags
WHERE tag ~ '<b.*>';

SELECT DISTINCT * FROM posts, startswithB WHERE posts.tags ~ ('.*' || startswithB || '.*');

如何获得想要的关系？

How do I get the relation I'm looking for? Is it possible?

编辑：

好吧，我做了什么：

创建post_tags：

create post_tags:

SELECT posts.id, tags.tag 
INTO post_tags 
FROM posts, tags 
WHERE posts.tags ~ ('.*' || tags.tag || '.*');

选择所有带有标签< foo> ：

SELECT *
FROM posts
WHERE posts.id IN (
    SELECT id
    FROM post_tags
    WHERE tag = '<foo>'
);

推荐答案

您在这里实际要做的是-一对多的关系。考虑一下：每个标签可以位于多个帖子上，每个帖子可以具有多个标签。

What you actually have going on here is a many-to-many relationship. Think about it: each tag can be on several posts, and each post can have several tags.

正确的关系体系结构是在中间添加另一个表，例如this：

The correct relational architecture for this is to add another table in the middle like this:

CREATE TABLE post_tags (
  id INTEGER REFERENCES posts,
  tag VARCHAR REFERENCES tags
);

然后在帖子中放置标签列表格。

Then drop the tags column on your posts table.

这解决了您的所有问题，因为您可以通过沿不同方向加入post_tags来获得帖子上的标签集或具有给定标签的帖子集。您还可以使用常规的LIKE查询来获取以某些内容开头的标签列表，如果您在一个字段中连接了一串字符串，则难度会更大。

This solves all your issues, because you can get the set of tags on a post or the set of posts with a given tag by joining against post_tags in different directions. You can also get the list of tags that start with something using a regular LIKE query, which will be more difficult if you have a bunch of strings concatenated in one field.

这篇关于（Postgre）SQL中的一对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！