Insert and associate repeated data in table

Question

I'm developing a simple question tags database association in postgresql. A question can have multiple tags and one tag can be associated to multiple questions. That's why I have a M-M table, questiontags. Using php I want to create a question and verify if the tags given dont exist on the tags table already. If they do exist:

Don't add my tag to the tags table
Associate the pre existant tag to the question on my questiontags table

If the tag doesnt exist I want to add it to the tags table and then create an assoaciation.

For that I was trying something like this:

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]);
}

The problem is that this only works for a new question and not when I update the question. When I do associate_only_tag I need something that checks if the question exists and then update instead of trying to create a new row questiontags. After a lot of effort I can't figure it out.

Is there any way to accomplish this?

score 0 · Answer 1 · edited May 23 '17 at 12:02

I suggest a single query with INSERT ... ON CONFLICT DO NOTHING in a CTE (but get it right!).
And if you want to wrap this into a function use a server-side SQL function with a VARIADIC input parameter for convenience:

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;

This creates all tags in the array (or list) which did not exist, yet. And it associates the given question with all of them - unless already associated.

It requires a unique (or PK) constraint on name in the table tags and on (questionid, tagid) in the table questiontags. Both typically exist in a many-to-many implementation. Else create each.

And it assumes tags.tagid to be a serial column. Should also be the case.

Call:

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

Or:

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

It can still fail under concurrent write load (even if very unlikely). If you have that, use ON CONFLICT ... DO UPDATE instead. Detailed explanation:

How to use RETURNING with ON CONFLICT in PostgreSQL?

About VARIADIC:

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

Insert and associate repeated data in table

1 Answers1