php - 在表中插入和关联重复数据

Question

我正在 postgresql 中开发一个简单的问题标签数据库关联。一个问题可以有多个标签，一个标签可以关联多个问题。这就是为什么我有一个 M-M 表，问题标签。使用 php 我想创建一个问题并验证给定的标签是否已经存在于标签表中。如果它们确实存在:

• 不要将我的标签添加到标签表
• 将预先存在的标签与我的问题标签表中的问题相关联

如果标签不存在，我想将它添加到标签表中，然后创建一个关联。

为此我正在尝试这样的事情:

function update_tags($questionid, $tags) {
    global $conn;

    //Check if question already exists. If Yes, delete it from the array -> //EDIT PROPOSES
    $questiontags = get_all_tags();
    $existant_tags = [];

    foreach ($questiontags as $tag_idx => $tag) {
        if(in_array($tag['name'], $tags)){
            $key = array_search($tag['name'], $tags);
            unset($tags[$key]);
            $existant_tags[] = $tag['tagid'];
            associate_only_tag($tag['tagid'], $questionid);

        }
        $questiontags[$tag_idx] = $tag['tagid'];
    }

    foreach ($tags as $tag) {
        associate_tag($tag, $questionid);
    }

    $tags_to_delete = array_diff($questiontags, $existant_tags);

    foreach ($tags_to_delete as $tagid) {
        delete_tag_from_question($tagid, $questionid);
    }
}

function get_all_tags() {

    global $conn;

    $query=$conn->prepare("SELECT tags.tagid, tags.name FROM tags ");
    $query->execute();

    return $query->fetchAll();

}

function get_tags_from_question($questionid) {
    global $conn;

    $query=$conn->prepare("SELECT tags.tagid, tags.name FROM tags 
INNER JOIN questiontags 
ON tags.tagid = questiontags.tagid
WHERE questiontags.questionid = :question
");
    $query->execute(['question' => $questionid]);

    return $query->fetchAll();
}

function insert_tag($tag)
{
    global $conn;
    $stmt = $conn->prepare("INSERT INTO tags (name) VALUES(:tag)");
    $stmt->execute([$tag]);
    return (int)$conn->lastInsertId();
}

function associate_tag($tag, $questionid)
{
    global $conn;
    $tagid = insert_tag($tag);
    $stmt = $conn->prepare("INSERT INTO questiontags (questionid, tagid) VALUES(:question, :tag)");
    $stmt->execute(['question' => $questionid, 'tag' => $tagid]);
}

function associate_only_tag($tagid, $questionid)
{
    global $conn;
    $stmt = $conn->prepare("INSERT INTO questiontags (questionid, tagid) VALUES(:question, :tag)");
    $stmt->execute(['question' => $questionid, 'tag' => $tagid]);
}

function delete_tag_from_question($tagid, $questionid) {
    global $conn;

    $query = $conn->prepare("DELETE FROM questiontags WHERE questionid = :question AND tagid = :tag");
    $query->execute(['question' => $questionid, 'tag' => $tagid]);
}

问题是这只适用于新问题，而不是当我更新问题时。当我执行 associate_only_tag 时，我需要一些东西来检查问题是否存在然后更新而不是尝试创建新行 questiontags。经过很多努力，我无法弄清楚。

有什么办法可以做到这一点吗？

Answer 1

我建议在 CTE 中使用 INSERT ... ON CONFLICT DO NOTHING 进行单个查询(但要正确!)。
如果您想将其包装到函数中，请使用带有 VARIADIC 输入参数的服务器端 SQL 函数以方便使用:

CREATE OR REPLACE FUNCTION update_tags(_questionid int, VARIADIC _tags text[])
  RETURNS void AS
$func$
   WITH ins_tags AS (
      INSERT INTO tags (name) 
      SELECT * FROM unnest(_tags)
      ON     CONFLICT (name) DO NOTHING
      RETURNING tagid
      )
   INSERT INTO questiontags (questionid, tagid)
   SELECT _questionid, i.tagid FROM ins_tags i
   UNION  ALL
   SELECT _questionid, t.tagid FROM tags t
   WHERE  t.name = ANY(_tags)
   ON     CONFLICT (questionid, tagid) DO NOTHING;
$func$  LANGUAGE sql;

这将创建数组(或列表)中尚不存在的所有标签。它将给定的问题与所有问题相关联 - 除非已经相关联。

它需要对 tags 表中的 name 和 (questionid, tagid) 表中的唯一(或 PK)约束 问题标签。两者通常都存在于 many-to-many implementation 中.否则创建每个。

它假定 tags.tagid 是一个 serial 列。也应该如此。

调用:

SELECT update_tags(123, 'foo', 'bar');

或者:

SELECT update_tags(123, VARIADIC '{foo,bar}'::text[]);

在并发写入负载 下它仍然会失败(即使不太可能)。如果有，请改用 ON CONFLICT ... DO UPDATE。详细解释:

• How to use RETURNING with ON CONFLICT in PostgreSQL?

关于 VARIADIC:

• Pass array of tags to a plpgsql function and use it in WHERE condition