MySQL查詢從另一個表中檢索相關資料

我對 SQL 查詢不太熟悉（大多數時候我使用框架中的查詢構建器，如 CodeIgniter 和 Laravel）。現在，我需要從具有兩個表的關系資料庫中檢索資料：一個用于物體條目，另一個用于物體的補充資料。例如，見下文：

tbl_posts

tbl_posts_meta

在這個例子中，我需要以這種格式檢索一個物件陣列：

[
  {
    id: 1,
    name: "Lorem ipsum",
    slug: "lorem-ipsum",
    data: {
      first_name: "John",
      last_name: "Doe",
      rating: 5,
    }
  },
  {
    id: 2,
    name: "Testing post",
    slug: "testing-post",
    data: {
      parent: 1,
      rating: 3,
    }
  },
  {
    id: 3,
    name: "Hello World",
    slug: "hello-world",
    data: {
      rating: 4,
    }
  },
]

我嘗試使用子查詢來處理這個問題，但我收到了Cardinality violation: 1241 Operand should contain 1 column(s)錯誤。我的查詢如下所示：

SELECT *,(SELECT * FROM tbl_posts_meta WHERE "post_id" = "tbl_posts"."id") as data FROM tbl_posts;

我已經嘗試過使用 JOIN，但結果看起來與預期相差甚遠（結果中有一個“鍵”屬性和一個“值”屬性，包含 tbl_posts_meta 中最后找到的條目）。

SELECT * FROM tbl_posts INNER JOIN tbl_posts_meta ON tbl_posts_meta.post_id = tbl_posts.id;

有沒有辦法通過一個查詢來檢索所需的結果？我不想通過 applogic 執行此操作（例如首先從 tbl_posts 檢索資料并在“data”屬性上附加另一個查詢，該查詢回傳來自 tbl_posts_meta 的所有資料），因為這種方式可能會導致資料庫過載。

謝謝！

uj5u.com熱心網友回復：

該代碼未在評論中正確顯示，因此您可以在這里檢查，抱歉，我無法為理論辯論做出更多貢獻：

select 
tp.id,
tp.name,
tp.slug,
(select group_concat(`key`, "=",value separator ';' ) from tbl_posts_meta where post_id= tp.id )as datas
from tbl_posts tp

uj5u.com熱心網友回復：

您可以按照以下簡單步驟獲取所需的 json：

• 連接兩個表以獲得集中的資料
• 通過使用聚合函式tbl_posts_meta聚合您感興趣的列 ( tbl_posts.id, tbl_posts.name, ) 來生成類似JSON 的字串。由于您的鍵可以是整數或字串，因此您需要使用 快速檢查，如果字串不是數字，它將回傳 0。tbl_posts.slugGROUP_CONCATCAST(<col> AS UNSIGNED)
• 使用該JSON_OBJECT函式將每一行轉換為 JSON
• 使用該JSON_ARRAYAGG函式將所有 json 合并為一個 json。

WITH cte AS(
    SELECT p.id,
           p.name, 
           p.slug,
           CONCAT('{',
                  GROUP_CONCAT(
                      CONCAT('"', 
                             m.key_, 
                             '": ', 
                             IF(CAST(m.value_ AS UNSIGNED)=0, 
                                CONCAT('"', m.value_, '"'),
                                CAST(m.value_ AS UNSIGNED))  ) 
                      ORDER BY m.id                             ),
                  '}') AS data_
    FROM       tbl_posts      p
    INNER JOIN tbl_posts_meta m
            ON p.id = m.post_id
    GROUP BY p.id,
             p.name, 
             p.slug
)
SELECT JSON_ARRAYAGG(
           JSON_OBJECT("id",   id, 
                       "name", name, 
                       "slug", slug, 
                       "data", CAST(data_ AS JSON))) 
FROM cte

在此處查看演示。

uj5u.com熱心網友回復：

經過一些研究、測驗和閱讀您在此處發布的查詢，我終于達到了我需要的結果。最終查詢的結構如下：

SELECT *, (
  SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
      key, value
    )
  ) FROM tbl_posts_meta WHERE post_id = post.id
) AS data
FROM tbl_posts;

將其轉換為 Laravel 的 Eloquent 查詢構建器，并使用 PHP 的序列化/反序列化函式，最終結果如下所示：

$posts = Post::query()
          ->select("*")
          ->selectRaw('(SELECT JSON_ARRAYAGG(JSON_OBJECT(key, value)) FROM tbl_posts_meta WHERE post_id = posts.id) as data');

return $posts->get()->map(function($post){
  $meta = [];
  foreach($post->data as $dataItem){
    foreach($dataItem as $key => $value){
      $meta[$key] = unserialize($value);
    }
  }
  $post->data = $meta;
  return $post;
});

感謝您的支持和關注！:)

標籤：php mysql sql 拉拉维尔 代码点火器

上一篇：Postgres發現None時的替換操作

下一篇：下單后只選擇1行