本文介绍了(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中的一对多关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-20 14:38