当前位置：首页 > news >正文

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

news 2025/9/18 1:59:00

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