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

本文主要是介绍typeorm联表查询：副表json格式放到主表字段下或多个副表字段并列主表字段，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

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

这篇关于typeorm联表查询：副表json格式放到主表字段下或多个副表字段并列主表字段的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---