typeorm联表查询：副表json格式放到主表字段下或多个副表字段并列主表字段

最新推荐文章于 2024-05-01 17:25:08 发布

梦想与想象-广州大智汇

最新推荐文章于 2024-05-01 17:25:08 发布

阅读量744

点赞数 10

分类专栏： node # cool-admin 文章标签： typeorm 联表查询 node midway cooladmin

本文为博主原创文章，未经博主允许不得转载。

本文链接：https://blog.csdn.net/u014617119/article/details/135166927

版权

node 同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

cool-admin

8 篇文章 1 订阅

订阅专栏

实体类字段不做映射，typeorm实现联查查询

1、副表json格式放到主表字段下

//goods表和member表联表，关系goods.id = member.uid，member表数据json对象格式放到主表userInfo下
//leftJoinAndMapOne配合getMany实现
const builder = await getConnection().createQueryBuilder();
builder
    .select([
        'goods.id',
        'goods.goodName',
    ])
    .from(DzhPortalGoodsEntity, 'goods')  
    .leftJoinAndMapOne(
        'goods.userInfo',
        DzhMemberUserEntity,
        'member',
        'member.id = goods.uid'
    ); 
const list: any = await builder.getMany();
console.log(list)
//输出
// [
//   {
//         "id": 178,
//         "goodName": "Nature：重大发现！淋巴管竟可产生红细胞和白细胞",
//         "userInfo": {
//             "id": 12,
//             "createTime": "2022-11-23 20:53:33",
//             "nickname": "dzhking",
//             "role_id": 1,
//         }
//     },   
// ]

2、多个副表字段并列主表字段

//goods表和member表联表，关系goods.id = member.uid，多个副表字段并列主表字段
//leftJoinAndSelect配合getRawMany
// 特别要注意 主表字段要加别名， 子查询里面的附表id一定要填写，不然报错，字段前也不能加别名，也报错
const builder = await getConnection().createQueryBuilder();
builder
    .select([
        'goods.id as id',
        'goods.goodName  as id goodName',
    ])
    .from(DzhPortalGoodsEntity, 'goods')  
    .leftJoinAndSelect(qb => {
          return qb.subQuery().select(['id', 'username', 'nickname']).from(DzhMemberUserEntity, 'member');
        },
        'member',
        'member.id = goods.uid'
    )
    .groupBy('goods.id'); 
const list: any = await builder.getRawMany();
console.log(list)


//把leftJoinAndSelect换成也可以leftJoinAndMapOne
    .leftJoinAndMapOne(
        'goods.userInfo',
        qb => {
          return qb
            .subQuery()
            .select(['id', 'username', 'nickname'])
            .from(DzhMemberUserEntity, 'member');
        },
        'member',
        'member.id = goods.uid'
      )


//输出
// [
//   {
//     "id": 178,
//     "username": "dzhking",
//     "nickname": "dzhking",
//     "goodsName": "Nature：重大发现！淋巴管竟可产生红细胞和白细胞"
//   },
// ]

3、副表字段并列主表字段 + 副表字段json格式插入到主表字段 + 关联其他表字段

const builder = getConnection().createQueryBuilder();
builder
    .select([
        'goods.id as id',
        'goods.goodName  as goodName',
        `(SELECT JSON_OBJECT(  'id',id,'username',username,'nickname',nickname )   FROM dzh_member_user member WHERE member.id = goods.uid) 
            as userInfo `,
        `(SELECT cate.cateName  FROM dzh_portal_cate cate WHERE cate.id = goods.cateId ) as cateName`,
    ])
    .from(DzhPortalGoodsEntity, 'goods')  
    .leftJoinAndSelect(qb => {
          return qb.subQuery().select(['id', 'username', 'nickname']).from(DzhMemberUserEntity, 'member');
        },
        'member',
        'member.id = goods.uid'
    )
    .groupBy('goods.id'); 
const list: any = await builder.getRawMany();
//输出
// [
//   {
//     "id": 178,
//     "username": "dzhking",
//     "nickname": "dzhking",
//     "goodName": "Nature：重大发现！淋巴管竟可产生红细胞和白细胞",
//     "userInfo": {
//       "id": 12,
//       "nickname": "dzhking",
//       "username": "dzhking"
//     },
//     "cateName": "市场分析"
//   },
// ]