codeigniter 用于从另一个表中检索相关数据的MySQL查询

siotufzp 于 2022-12-07 发布在 Mysql

关注(0)|答案(3)|浏览(105)

I'm not too experienced with SQL queries (most times I used query builder from frameworks, like CodeIgniter and Laravel). Now, I need to retrieve data from a relational DB which have two tables: one for entity entries and other for complemental data of entities. For example, see below:
tbl_posts
| id | name | slug |
| ------------ | ------------ | ------------ |
| 1 | Lorem ipsum | lorem-ipsum |
| 2 | Testing post | testing-post |
| 3 | Hello world | hello-world |
tbl_posts_meta
| id | post_id | key | value |
| ------------ | ------------ | ------------ | ------------ |
| 1 | 1 | first_name | John |
| 2 | 1 | last_name | Doe |
| 3 | 1 | rating | 5 |
| 4 | 2 | parent | 1 |
| 5 | 2 | rating | 3 |
| 6 | 3 | rating | 4 |
In this example, I need to retrieve an array of objects in this format:

[
  {
    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,
    }
  },
]

I've tried using subquery to handle this, but I'm reveiving Cardinality violation: 1241 Operand should contain 1 column(s) error. My query looks like this:

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

I've already tried using JOIN, but the result looks more away from expected (I have one "key" property and one "value" property in result, containing the last found entry in tbl_posts_meta).

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

Is there someway I can retrieve desired results with one query? I don't want to do this by applogic (like first retrieving data from tbl_posts and appending another query on "data" property, that returns all data from tbl_posts_meta), since this way may cause database overload.
Thanks!

codeigniter

来源：https://stackoverflow.com/questions/72506644/mysql-query-to-retrieve-related-data-from-another-table

3条答案

按热度按时间

eoigrqb61#

代码没有在注解中显示出来，所以在这里你可以检查一下，对不起，我不能为理论上的争论做出更多的贡献：

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

赞(0）回复(0）举报 2022-12-07

6kkfgxo02#

您可以按照以下简单的步骤来获得所需的json：

连接两个表以集中数据
通过使用GROUP_CONCAT聚合函数聚合您感兴趣的列（tbl_posts.id、tbl_posts.name、tbl_posts.slug），生成一个tbl_posts_meta类JSON字符串。由于键可以是整数或字符串，因此需要使用CAST(<col> AS UNSIGNED)进行快速检查，如果字符串不是数字，则返回0。
使用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

检查演示here。

赞(0）回复(0）举报 2022-12-07

a6b3iqyw3#

经过一些研究、测试和阅读您在此处发布的查询后，我终于得到了我所需要的结果。最终的查询结构如下：

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的serialize/unserialize函数，最终结果如下所示：

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

感谢您的支持和关注！：）

赞(0）回复(0）举报 2022-12-07

我来回答

codeigniter 用于从另一个表中检索相关数据的MySQL查询

3条答案

相关问题

热门标签

最新问答