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

2023-12-23 19:56:49

实体类字段不做映射,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": "市场分析"
//   },
// ]

文章来源:https://blog.csdn.net/u014617119/article/details/135166927
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。