PHP 一次查询生成二维 Json

场景:

一次查询,返回给前端一个列表,需要在列表的每一项中加一个相关子对象。比如,返回给前端一个文章列表,列表中的每一项中,都要有一个文章作者的信息对象,如下:

{
  "count": "548",
  "list": [
    {
      "id": "620",
      "user_id": "1",
      "category_id": "17",
      "name": "买藕了",
      "thumbnail": "",
      "content": "一斤藕半斤都是空心的,以后不买了...",
      "comment_counter": "0",
      "like_counter": "0",
      "user": {
        "id": "1",
        "nickname": "宇文泰",
        "avatar": "",
        "signature": "。。。"
      }
    },
    {
      "id": "621",
      "user_id": "2",
      "category_id": "17",
      "name": "买藕了",
      "thumbnail": "",
      "content": "月光倒囊入水,江涛吞吐,露气吸之,噀天为白。 林下漏月光,疏疏如残雪。",
      "comment_counter": "0",
      "like_counter": "0",
      "user": {
        "id": "2",
        "nickname": "张岱",
        "avatar": "",
        "signature": ""
      }
    },
    {
      "id": "622",
      "user_id": "3",
      "category_id": "17",
      "name": "买藕了",
      "thumbnail": "",
      "content": "人散后,一钩淡月天如水。",
      "comment_counter": "0",
      "like_counter": "0",
      "user": {
        "id": "3",
        "nickname": "谢逸",
        "avatar": "",
        "signature": "药不能停"
      }
    }
    ...
  ]
}


方法:

自定义字段名后重新组装数据。

SQL 查询:

SELECT 
    a.*,
    (SELECT COUNT(*) FROM tp_article_comment AS ac WHERE a.id=ac.article_id) AS comment_counter,
    (SELECT COUNT(*) FROM tp_article_like AS al WHERE a.id=al.article_id) AS like_counter,
    u.id AS 'user#id',
    u.nickname AS 'user#nickname',
    u.avatar AS 'user#avatar',
    u.signature AS 'user#signature'
FROM tp_article AS a 
    LEFT JOIN tp_user AS u ON a.user_id = u.id
    LEFT JOIN tp_article_category AS ac ON ac.id = a.category_id
WHERE
    ...


PHP 组装数据:

/**
 * 字段过滤
 * {
 *      "list_order": "1",
 *      "is_show": "1",
 *      "user#id": "1",
 *      "user#nickname": "林深雾122223",
 * }
 * 生成
 * {
 *      "list_order": "1",
 *      "is_show": "1",
 *      "user": {
 *          "id": "1",
 *          "nickname": "林深雾122223",
 *      }
 * }
 * @param [type] $arr
 * @param [type] $flag
 * @return void
 * @author javascript.net.cn
 * @since  2020-02-22
 */
function field_filter($arr, $flag){
    foreach ($arr as $k => $v) {
        $field = explode($flag,$k);
        if(sizeof($field) > 1){
            $arr[$field[0]][$field[1]] = $v;
            unset($arr[$k]);
        }
    }
    return $arr;
}


真诚赞赏,手留余香
赞赏
随机推荐
MAC 升级 Nodejs 和 Npm 到最新版
PSR规范
为什么Axios ajax每次patch都会有两次网络请求?
Cordova 权限问题 Error: spawn EACCES
Android 学习笔记(一)
树妖1
composer 把默认镜像修改成阿里云镜像
阿里云ECS磁盘扩容
十大编程算法
使用Node.js http 模块采集数据时的日文 EUC-JP 乱码,使用 iconv-lite 转码