全文搜索多对多关系 [英] Full text search on many to many relationship

问题描述

我有以下表格

关键字表

CREATE TABLE trigger_keyword
(
    id bigint NOT NULL,
    keyword text NOT NULL,
    CONSTRAINT trigger_keyword_id PRIMARY KEY (id)
)

这是桥接表

CREATE TABLE trigger_keyword_trigger_message
(
    trigger_keyword_id bigint NOT NULL,
    trigger_message_id bigint NOT NULL,
    CONSTRAINT trigger_keyword_trigger_message_trigger_keyword_id_fkey FOREIGN KEY (trigger_keyword_id)
        REFERENCES public.trigger_keyword (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE NO ACTION,
    CONSTRAINT trigger_keyword_trigger_message_trigger_message_id_fkey FOREIGN KEY (trigger_message_id)
        REFERENCES public.trigger_message (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE NO ACTION
)

消息表

CREATE TABLE trigger_message
(
    id bigint NOT NULL,
    message text NOT NULL,
    CONSTRAINT trigger_message_id PRIMARY KEY (id)
)

我有一个字符串列表

我们假设我们在 trigger_keyword table

Let's assume we have the following keywords in the trigger_keyword table

trigger_keyword表

The trigger_keyword table

id  keyword
----------------------------------------
1   hi
2   hello
3   the weather
4   the climate

trigger_message表

The trigger_message table

id  message
-----------------------------------------
1   Hi how is your day?
2   Hello, have a wonderful day
3   Looks sunny today
4   Excellent, no rain today
5   looks like we'll have showers today

假设我们的字符串之一是 Hi Robot！，那么SQL查询应该返回你今天好吗？或你好，祝你有美好的一天；它应该随机选择其中之一。如果字符串包含 hello机器人而不是 hi机器人，则字符串应做相同的操作，因为这两个字符串均 hi 和 hello 在关键字表中。

Let's say one of our strings is Hi Robot!, then the SQL query should return Hi how is your day? or Hello, have a wonderful day; it should pick one of them randomly. It should do the same if the string contained hello robot instead of hi robot since both hi and hello are in the keywords table.

如果字符串包含告诉我天气，那么SQL查询应该返回今天看起来很晴朗或非常好，今天没有雨或似乎今天我们将随机洗澡。

And if the string contains tell me the weather then the SQL query should return Looks sunny today or Excellent, no rain today or looks like we'll have showers today randomly.

我想我会为此使用全文搜索？

I assume I would have to use full text search for this?

这是我第一次使用桥表，是否需要在桥表中手动插入关系？

It's my first time using a bridge table, do I manually insert the relations in the bridge table?

推荐答案

您应在“ bridge”上定义主键约束。包含两个列的表格。

You should define a primary key constraint on the “bridge” table that contains both columns.

全文搜索，如此答案是执行此操作的一种方法。

Full text search as indicated in this answer is one way to do this.

要随机选择一个结果行，可以将以下内容附加到查询中：

To randomly pick one result row, you can append the following to the query:

ORDER BY random() LIMIT 1

要插入表中，可以使用 DEFAULT 子句，并在 id 列的定义中添加一个序列，然后使用 INSERT ... RETURNING 获取桥表的值。

To insert into the tables, you could use a DEFAULT clause with a sequence in the definition of the id columns and use INSERT ... RETURNING to get the values for the bridge table.

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