從兩個表中檢索資料的優化/最佳方法是什么？-有解無憂

我有兩張桌子。

post table

|post_id | post_title |
 -------- ------------ 
| 1      | Post 1     |
| 2      | Post 2     |
| 3      | Post 3     |

post_creator table

|post_id | creator |
 -------- --------- 
| 1      | John    | 
| 1      | Smith   | 
| 1      | Mike    |
| 2      | Bob     |
| 3      | Peter   |
| 3      | Brad    |

當我加入這些表時，它看起來像這樣。

SELECT * 
FROM post p
JOIN post_creator c ON p.post_id = c.post_id


|post_id | post_title | post_id | creator|
 ---------------------------------------- 
| 1      | Post 1     | 1       | John   |
| 1      | Post 1     | 1       | Smith  |
| 1      | Post 1     | 1       | Mike   |
| 2      | Post 2     | 2       | Bob    |
| 3      | Post 3     | 3       | Peter  |
| 3      | Post 3     | 3       | Brad   |

我想抓住每個帖子的創建者。但在這種情況下，由于創作者的原因，我加入的結果一次又一次地重復了相同的帖子。

我所做的是首先從 post 表中獲取所有資料。然后我回圈該結果并在回圈內獲取每個帖子的所有創建者。但在這種情況下，它一次又一次地查詢每個內容以獲取創建者。

$sql = "SELECT * FROM post";
$stmt = $conn->prepare($sql);
$stmt->execute();
$res = $stmt->fetchAll(PDO::FETCH_OBJ);

$dataObj = new stdClass;
$dataArr = [];

foreach($res as $post){
  $sql = "SELECT creator FROM post_creator WHERE post_id=$post->post_id";
  $stmt = $conn->prepare($sql);
  $stmt->execute();
  $creators = $stmt->fetchAll(PDO::FETCH_OBJ);
   
  $dataObj->post_id = $post->post_id
  $dataObj->post_title = $post->title
  $dataObj->creators = $creators;
  
  array_push($dataArr, $dataObj);
}

所以最后我dataArr有這種結構。

[
  {
    post_id: 1, 
    post_title: Post 1, 
    creators:[John, Smith, Mike] 
  },
  
  {
    post_id: 2, 
    post_title: Post 2, 
    creators:[Bob] 
  },

  {
    post_id: 2, 
    post_title: Post 1, 
    creators:[Peter, Brad] 
  },
]

這就是我想要的。現在我可以回圈這個并渲染到一個視圖。

是否有任何優化/更好的方法來獲得此結果而無需一次又一次地回圈和查詢？

uj5u.com熱心網友回復：

我認為您需要使用group_concat將您的creators.

SELECT p.post_id, post_title, group_concat(creator) 
FROM post p
JOIN post_creator using(post_id) 
group by p.post_id

此外，這：

$sql = "SELECT creator FROM post_creator WHERE post_id=$post->post_id";
$stmt = $conn->prepare($sql);
$stmt->execute();

是對準備好的陳述句的不當使用。應該寫成：

$sql = "SELECT creator FROM post_creator WHERE post_id=?";
$stmt = $conn->prepare($sql);
$stmt->execute(array($post->post_id));

如果需要，但事實并非如此。始終系結值，永遠不要直接放入 SQL。

uj5u.com熱心網友回復：

我想說你可以遵循 3 條不同的道路，所有這些都有一些好處或其他。

選項 1. 帶有 JOIN（和重疊行）的簡單 SELECT 查詢

這或多或少是您已經嘗試過的，包括您列出的第一個查詢；這導致重復的行。

修改您的應用程式代碼以處理欺騙者并簡單地將創建者折疊到相同的陣列/物件中是相當簡單的。開銷也幾乎為零。從關系資料庫設計的角度來看，這種方法仍然是最佳實踐。

   SELECT p.post_id
        , p.post_title
        , c.creator
     FROM post         p
LEFT JOIN post_creator c 
       ON p.post_id = c.post_id
 ORDER BY p.post_id ASC

/* $rows = ...query...; */
$posts = [];
foreach ($rows as $row) {
    if (!isset($posts[( $row['post_id'] )])) {
        // this is a new post_id
        $post                       = [];
        $post['id']                 = $row['post_id'];
        $post['creators']           = [];
        $post['creators'][]         = $row['creator'];
        $posts[( $row['post_id'] )] = $post;
    } else {
        // this is just an additional creator
        $posts[( $row['post_id'] )]['creators'][] = $row['creator'];
    }
}

選項 2. 多值列（陣列或 json）

對于非純粹主義者來說，一種更實用的解決方案可能是讓您的查詢生成包含多個值的輸出列。這通常意味著 JSON 或 ARRAY 列。確切的細節取決于您選擇的資料庫系統。

在任何一種情況下，您都可以將它與 SQLGROUP BY特性結合起來。假設您使用 MySQL 并且更喜歡 JSON 型別；然后你會使用一個查詢，例如：

    SELECT p.post_id
         , p.post_title
         , JSON_ARRAYAGG(c.creator) AS creators
      FROM post         p
 LEFT JOIN post_creator c 
        ON p.post_id = c.post_id
  GROUP BY p.post_id
  ORDER BY p.post_id ASC

這樣一來，你就只能獲得一次后一個記錄，你會得到一個值，如['Mike', 'Paul', 'Susan']它json_decode()可以變成一個適當的PHP陣列。

選項 3. 成熟的檔案

另一種建立在選項 #2 上的替代方案是完全使用 JSON，并完全放棄關系記錄集。

大多數現代 DBMS 具有大量 JSON 功能，并且您自己列出的格式dataArr可以由資料庫完全生成以回應單個SELECT查詢。

這樣，查詢將始終只產生 1 行和 1 列，其中包含dataArr所有這些帖子的整個組合（同樣，可以json_decode像以前一樣將其轉換為原生 PHP 陣列或物件樹）。

雖然此方法的結果可能非常簡潔（取決于您的應用程式的撰寫方式），但有些人可能想知道為什么您使用的是 RDBMS 而不是 MongoDB 之類的東西。

總的來說，我推薦選項 1。

轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/403149.html

標籤：

上一篇：ClickhouseCrossjoin解決方法？

下一篇：H2似乎誤解了有效的連接子句