将多个INSERTS合并到一个表中并使用多个表

Question

我正在尝试使用PostgreSQL数据库在PHP中开发一个Q &网站。 我有一个动作来创建一个页面，其中有一个标题，正文，类别和标签。我设法插入所有这些字段，但我有一些问题插入多个标记值。

我用这个函数来获取逗号分隔值到一个数组中，现在我想要在表tags之后插入每个数组元素到数据库中的东西（避免重复），然后在我的多对多关系表questiontags上插入：

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

它打印是这样的：

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

动作/ 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我应该插入标签。

function create_question($question, $tags) { 

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

我在想这样做这样的事情：

全球$康恩;

foreach ($tags as $tag) { 

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

但是，然后我需要标签ID插入我多到许多表。我是否需要创建另一个程序，get_tags_id，然后获取tag_id数组，然后在尝试使用标记时插入它们？ 我什么时候执行查询？在两个插入之后或者在彼此之后？

对不起，任何误用的术语或我的新手问题。我是PHP的新手，我正在努力应对一些新的概念。

Answer 1

您可以在之间完成一个使用CTE的SQL命令。

假设Postgres的9.6和这个经典许多一对多的模式（因为你没有提供它）：

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

要插入单问题与阵列的标签 ：

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 here

这会创建任何尚未存在的标签。

Postgres数组的文本表示形式如下所示：{tag1, tag2, tag3}。

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

详细解释：

• Insert data in 3 tables at a time using Postgres
• How to use RETURNING with ON CONFLICT in PostgreSQL?
• How to implement a many-to-many relationship in PostgreSQL?
• Cannot INSERT: ERROR: array value must start with "{" or dimension information

如果你有并发写入您可能需要做更多。特别考虑第二个环节。