php - 在 SELECT 语句中返回嵌套的行数组？

Question

我试图从 MYSQL 查询返回一个嵌套数组。我有一个包含帖子、用户、关键字和类别的表，我想优化查询，这样我就没有那么多选择。目前，我对一篇文章有​​ 4 个 SELECT 查询，每个查询都通过 foreach 循环，并在 PHP 中创建一个嵌套数组。有没有办法将这些整合为更少的查询和更快的执行？

这是一行的样子。关键字、类别和作者编号都是各自表中的 ID。

帖子表:

| id | title |    content     | keywords | category | author |
|----|-------|----------------|----------|----------|--------|
|  1 | Test  | Lorem ipsum... | 1, 2     |        1 |      1 |
|----|-------|----------------|----------|----------|--------|

关键字表:

| id |   name   |   url    |  description   |
|----|----------|----------|----------------|
|  1 | keyword1 | keyword1 | Lorem ipsum... |
|  2 | keyword2 | keyword2 | Lorem ipsum... |
|----|----------|----------|----------------|

用户表:

| id |   name   |    email     |
|----|----------|--------------|
|  1 | John Doe | john@doe.com |
|----|----------|--------------|

类别表:

| id |   name    |    url    |
|----|-----------|-----------|
|  1 | Category1 | category1 |
|----|-----------|-----------|

这是我想要实现的输出:

Array
(
    [0] => Array
    (
        [id] = 1
        [title] = Test
        [content] = Lorem ipsum dolor sit amet, consectetur adipisicing elit. Alias, sapiente assumenda ratione dicta cumque accusantium id labore cupiditate maiores obcaecati repudiandae at eum fuga doloremque commodi. Quidem, nulla cupiditate aperiam!
        [keywords] => Array
            (
                [0] => Array
                (
                    [id] => 1
                    [name] => keyword1
                    [url] => keyword1
                    [description] => Lorem ipsum dolor sit amet, consectetur adipisicing elit. Asperiores, dolorem, consectetur voluptatem amet hic placeat alias rerum unde quis quia aperiam officia aliquam incidunt sit fugit quo iusto porro repellat!
                )
                [1] => Array
                (
                    [id] => 2
                    [name] => keyword2
                    [url] => keyword2
                    [description] => Lorem ipsum dolor sit amet, consectetur adipisicing elit. Magnam, culpa, repudiandae voluptatibus odit nam id sed maxime ullam quia accusamus minima nisi! Dolor, doloremque similique voluptatibus at eos vitae id?
                )
        )
        [category] => Array
        (
            [id] => 1
            [name] => Category1
            [url] => category1
        )
        [author] => Array
        (
            [id] => 1
            [name] => John Doe
            [email] => john@doe.com
        )
    )
)

Answer 1

首先，您应该规范化数据，为 post_keywords 创建一个新表，如下所示:

| post_id | keyword_id |
|---------|------------|
|       1 |          1 |
|       1 |          2 |
|---------|------------|

然后，通过简单的 JOIN 即可获取所需的数据。

一旦有了 post_keywords 表，您就可以执行以下操作:

    $db = new PDO('mysql:host=localhost;dbname=<SOMEDB>', '<USERNAME>', 'PASSWORD');

    $sql = "
       SELECT p.id      as p_id
          ,p.title   as p_title
          ,p.content as p_content
          ,c.id      as c_id
          ,c.name    as c_name
          ,c.url     as c_url
          ,u.id      as u_id
          ,u.name    as u_name
          ,u.email   as u_email
          ,GROUP_CONCAT( CONCAT( k.id, '|', k.name, '|', k.url, '|', k.description )
                         SEPARATOR '||' ) as keywords

      FROM posts p

      LEFT OUTER
      JOIN post_keywords pk
        ON pk.post_id = p.id

      LEFT OUTER
      JOIN keywords k
        ON k.id = pk.keyword_id

      LEFT OUTER
      JOIN category c
        ON c.id = p.category

      LEFT OUTER
      JOIN user u
        ON u.id = p.author

     GROUP BY p.id";

    $final = array();
    $results = $db->query( $sql );
    while ( $row = $results->fetch(PDO::FETCH_ASSOC) ) {
            $k = array();
        foreach ( ecplode( '||', $row[ 'keywords' ] as $kw ) {
            $kw = explode( '|', $kw );
            $k[] = array( 'id' => $kw[0], 'name' => $kw[1], 'url' => $kw[2], 'description' => $kw[3] );
        }
        $final[] = array( 'id'       => $row[ 'p_id' ]
                , 'title'    => $row[ 'p_title' ]
                , 'content'  => $row[ 'p_content' ]
                , 'keywords' => $k
                , 'category' => array( 'id' => $row[ 'u_id' ], 'name' => $row[ 'u_name' ], 'url' => $row[ 'c_url' ] )
                , 'author'   => array( 'id' => $row[ 'u_id' ], 'name' => $row[ 'u_name' ], 'email' => $row[ 'u_email' ] )
                );
    }

请注意，这未经测试，因此可能不是 100%。 SQL 假设可以有没有关键字和/或空类别或作者的帖子。如果情况并非如此，则可以从 JOIN 中删除“LEFT OUTER”。

$final 数组应根据您的要求进行格式化。 SQL 可以在 SQL Fiddle 上查看 http://sqlfiddle.com/#!2/fd630/1

更新

您可以使用以下 SQL 而不是上面的 SQL 对当前表执行相同的操作(不需要 post_keywords)。它确实假设所有关键字 ID 均以逗号分隔，且不嵌入空格:

SELECT p.id      as p_id
      ,p.title   as p_title
      ,p.content as p_content
      ,c.id      as c_id
      ,c.name    as c_name
      ,c.url     as c_url
      ,u.id      as u_id
      ,u.name    as u_name
      ,u.email   as u_email
      ,GROUP_CONCAT( CONCAT( k.id, '|', k.name, '|', k.url, '|', k.description )
                     SEPARATOR '||' ) as keywords

  FROM posts p

  LEFT OUTER
  JOIN keywords k
    ON k.id REGEXP REPLACE(p.keywords,',','|')

  LEFT OUTER
  JOIN category c
    ON c.id = p.category

  LEFT OUTER
  JOIN user u
    ON u.id = p.author

 GROUP BY p.id

SQLFiddle 位于 http://sqlfiddle.com/#!2/2e1ec7/2