嵌套查询-使用多个collection

Hana ·

更新时间:2024-11-10

· 564 次阅读

需求：一个文章有人，有机构，有类型三种，查询一个文章额外附加三种查询，一定是嵌套查询，这样就是用collection标签，但是collection标签只能额外查询一个SQL，那么怎么查询多个呢？当然是多个collection标签了

基本查询结果

查询SQL语句

  
    select sa.id, sa.article_name, sa.article_img_url, sa.article_intro_url, sa.article_content_url, sa.create_by, sa.create_time, sa.last_update_by, sa.last_update_time, sa.del_flag
    from sys_article sa
        inner join sys_dept_article sda
        on    sda.dept_id = #{deptId} and sa.id = sda.article_id
        inner join sys_type_article sta
        on  sta.type_id = #{typeId} and sa.id = sta.article_id
        inner join sys_user_article sua
        on  sua.user_id = #{userId} and sua.article_id = sa.id
    where 1 = 1
        and sa.id = #{id}
        and sa.article_name LIKE  CONCAT('%',#{articleName},'%')
        and sa.del_flag = #{delFlag}

查询结果

{
  "code": 200,
  "msg": null,
  "data": {
    "id": 34,
    "articleName": "n",
    "articleImgUrl": "21",
    "articleIntroUrl": "21",
    "articleContentUrl": "112",
    "createBy": "liubei",
    "createTime": "2020-03-15T11:36:23.730+0000",
    "lastUpdateBy": "liubei",
    "lastUpdateTime": "2020-03-15T11:36:23.730+0000",
    "delFlag": 0,
    "deptId": null,
    "userId": null,
    "typeId": null,
    "deptList": [
      {
        "id": 1,
        "name": "12",
        "deptLogo": "http://hcnet2006-file-apk.oss-cn-shenzhen.aliyuncs.com/12.jpg",
        "parentId": 2,
        "createBy": "1",
        "createTime": "2020-03-03",
        "lastUpdateTime": "2020-03-06",
        "lastUpdateBy": "1",
        "delFlag": -1,
        "userList": null
      }
    ],
    "typeList": [],
    "userList": [
      {
        "id": 1,
        "name": "1223456",
        "password": "23456",
        "avator": "http://hcnet2006-file-apk.oss-cn-shenzhen.aliyuncs.com/27505428-25dc-4244-92b3-8f7cd6f7855d.jpg",
        "grade": "2121",
        "email": "212",
        "mobile": "123456",
        "deptId": 1,
        "deptZh": null,
        "createBy": "1",
        "createTime": "2020-03-02",
        "lastUpdateTime": "2020-03-06",
        "lastUpdateBy": "ldyff",
        "delFlag": -1,
        "roleList": null
      },
      {
        "id": 2,
        "name": "21",
        "password": "2",
        "avator": "http://hcnet2006-file-apk.oss-cn-shenzhen.aliyuncs.com/21.jpg",
        "grade": "1",
        "email": "1",
        "mobile": "1",
        "deptId": 1,
        "deptZh": null,
        "createBy": "1",
        "createTime": "2020-03-02",
        "lastUpdateTime": "2020-03-02",
        "lastUpdateBy": "1",
        "delFlag": 0,
        "roleList": null
      }
    ]
  }
}

作者：天又热了

collection 嵌套

1024 个赞