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
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!