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

tbl_posts_meta

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!

CodePudding user response：

The code is not shown right in the comment so here it is for you to check, sorry I can't contribute more to the theoretical debate:

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

CodePudding user response：

You can follow these simple steps to get to your needed json:

• join the two tables to have centralized data
• generate a tbl_posts_meta JSON-like string by aggregating on your columns of interest (tbl_posts.id, tbl_posts.name, tbl_posts.slug) using a GROUP_CONCAT aggregation function. Since your keys can either be integers or strings, you need to do a quick check using the CAST(<col> AS UNSIGNED), which will return 0 if the string is not a number.
• use the JSON_OBJECT function to transform each row into a JSON
• use the JSON_ARRAYAGG function to merge all jsons into a single 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

Check the demo here.