多个插入到一个表和多对多表 [英] Multiple INSERTS into one table and many to many table

问题描述

我正在尝试使用PostgreSQL数据库在PHP中开发问答网站.我有一个动作来创建一个包含标题，正文，类别和标签的页面.我设法插入了所有这些字段，但是在插入多个标记值时遇到了一些问题.

I'm trying to develop a Q&A website in PHP using a PostgreSQL database. I have an action to create a page which has a title, body, category and tags. I managed to insert all those fields however I'm having some issues inserting multiple tag values.

我使用此函数将逗号分隔的值获取到数组中，现在我需要一些东西将每个数组元素插入表 tags 上的数据库中(避免重复)，然后在我的许多对象上插入到许多关系表 questiontags :

I used this function to get the comma separated values into an array and now I want something that inserts each array element into the database (avoiding repetitions) on table tags and after that insert on my many to many relationship table questiontags:

$tags = explode(',', $_POST['tags']); //Comma separated values to an array

打印如下内容:

Array ( [0] => hello [1] => there [2] => this [3] => is [4] => a [5] => test )

action/create_question.php

$category = get_categoryID_by_name($_POST['category']);

$question = [
    'userid' => auth_user('userid'),
    'body' => $_POST['editor1'],
    'title' => $_POST['title'],
    'categoryid' => $category
];

create_question($question, $tags);

，然后是我的 create_question ，我应该在其中插入标签.

and then my create_question where I should insert the tags.

function create_question($question, $tags) {

    global $conn;
    $query_publications=$conn->prepare("SELECT * FROM insert_into_questions(:body, :userid, :title, :categoryid);
");
    $query_publications->execute($question);
}

我正在考虑做这样的事情:

I was thinking about doing something like this:

全局$ conn;

foreach ($tags as $tag) {

    $query_publications=$conn->prepare("INSERT INTO tags(name) VALUES($tag);
");
    $query_publications->execute($question);    
}

但是然后我需要将标签ID插入到我的多对多表中.我是否需要创建另一个过程 get_tags_id ，然后获取一个 tag_id 数组并在尝试标记时将其插入?我什么时候执行查询?在两个插入之后还是在另一个末端?

But then I'd need the tags id to insert on my many to many table. Do I need to create another procedure, get_tags_id and then get a tag_id array and insert them as I tried for tags? When do I execute the query? After both inserts or in the end of each other?

对任何误用的词或我的新手问题都表示抱歉.我是PHP的新手，正在为一些新概念而苦苦挣扎.

Sorry for any misused term or for my newbie question. I'm new to PHP and I'm struggling with some new concepts.

推荐答案

您可以使用CTE在一个 SQL命令中完成所有操作.

You can do it all in one SQL command using CTEs.

假设Postgres 9.6 和这种经典的多对多模式(因为您未提供):

Assuming Postgres 9.6 and this classic many-to-many schema (since you did not provide it):

CREATE TABLE questions (
  question_id serial PRIMARY KEY
, title text NOT NULL
, body text
, userid int
, categoryid int
);

CREATE TABLE tags (
  tag_id serial PRIMARY KEY
, tag text NOT NULL UNIQUE);

CREATE TABLE questiontags (
  question_id int REFERENCES questions
, tag_id      int REFERENCES tags
, PRIMARY KEY(question_id, tag_id)
);

要插入带有标记数组的 单个问题:

To insert a single question with an array of tags:

WITH input_data(body, userid, title, categoryid, tags) AS (
   VALUES (:title, :body, :userid, :tags)
   )
 , input_tags AS (                         -- fold duplicates
      SELECT DISTINCT tag
      FROM   input_data, unnest(tags::text[]) tag
      )
 , q AS (                                  -- insert question
   INSERT INTO questions
         (body, userid, title, categoryid)
   SELECT body, userid, title, categoryid
   FROM   input_data
   RETURNING question_id
   )
 , t AS (                                  -- insert tags
   INSERT INTO tags (tag)
   TABLE  input_tags  -- short for: SELECT * FROM input_tags
   ON     CONFLICT (tag) DO NOTHING        -- only new tags
   RETURNING tag_id
   )
INSERT INTO questiontags (question_id, tag_id)
SELECT q.question_id, t.tag_id
FROM   q, (
   SELECT tag_id
   FROM   t                                -- newly inserted
   UNION  ALL
   SELECT tag_id
   FROM   input_tags JOIN tags USING (tag) -- pre-existing
   ) t;

dbfiddle 此处

这会创建尚不存在的所有标签.

This creates any tags that do not exist yet on the fly.

Postgres数组的文本表示如下: {tag1，tag2，tag3} .

如果保证输入数组具有不同的标签，则可以从CTE input_tags 中删除 DISTINCT .

If the input array is guaranteed to have distinct tags, you can remove DISTINCT from the CTE input_tags.

详细的说明:

• 在3个表中插入数据一次使用Postgres
• 如何在ON CONFLICT中使用RETURNING在PostgreSQL中?
• 如何实施许多PostgreSQL中有多对多关系?
• 无法插入:错误:数组值必须以"{"开头或尺寸信息

如果您有并发写入，则可能需要做更多的事情.特别考虑第二个链接.

If you have concurrent writes you may have to do more. Consider the second link in particular.

这篇关于多个插入到一个表和多对多表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！