pgsql查询分页条数问题和属性转json的mapper映射

pgsql查询分页条数问题和属性转json的mapper映射

一对多分页问题解决方法/从表一对多导致的问题条数重复增加？
解决方法：将多的列表转成一行json，在映射到list列表上,变相的将一对多转换成一对一关联查询，将结果集以json形式映射到字段对象列表上。

推荐第二种方法：属性转成一行json然后在mapper映射， 分页条数以主表总数计数统计。

第一种：

select * from xxx
left join (
	select
		array_agg(jsonb_build_object('labelId',
		dl.label_id,
		'labelName',
		dl.label_name)) as labelList,
		array_agg(dl.label_name) as labelNames,
		array_agg(dl.label_id) labelIdList,
		string_agg(dl.label_name,
		',') as labelNameQuerys,
		lr.rel_object_id as vehicleId
	from
		dbd_label_rel lr
	inner join dbd_label dl on
		dl.label_id = lr.label_id
	where
		lr.rel_object_type = '1'
		and lr.rel_object_id = 16562xxx7409
	group by
		lr.rel_object_id
	order by
		lr.rel_object_id) labeltemp on
	labeltemp.vehicleId = vehicle.id

1.对象labelist无法映射
2.对listid的映射，mapper构造方法映射：

<collection property="labelIdList" ofType="java.lang.String" javaType="arraylist">
	<constructor>
		<arg column="labelIdList"/>
	</constructor>
</collection>

@Getter
@Setter
@NoArgsConstructor

第二种：

select  *  from xxx(主表)
left join (
	select
	    jsonb_agg(dl ) as labelList,  jsonb_agg(dl.label_id ) as labelListis,
 		array_agg(dl.label_name) as labelNames,
		array_agg(dl.label_id) labelIdList,
		string_agg(dl.label_name,
		',') as labelNameQuerys,
		lr.rel_object_id as vehicleId
	from
		dbd_label_rel lr
	inner join dbd_label dl on
		dl.label_id = lr.label_id
    where
		lr.rel_object_type = '1'
		and lr.rel_object_id = 1656203643885457409
	group by
		lr.rel_object_id
	order by
		lr.rel_object_id) labeltemp on
	labeltemp.vehicleId = vehicle.id

@TableField(exist = false,typeHandler = JacksonTypeHandler.class)

第三种：第二种存在驼峰命名映射问题

select
	    jsonb_agg(dl ) as labelList,  jsonb_agg(dl.label_id::varchar ) as labelIdList,
	    	    jsonb_agg(JSONB_BUILD_OBJECT('labelId', dl.label_id,
				'labelName', dl.label_name) ) as labelList1, 
 		string_agg(dl.label_name,
		',') as labelNameQuerys,
		lr.rel_object_id as vehicleId
	from
		dbd_label_rel lr
	inner join dbd_label dl on
		dl.label_id = lr.label_id
		and dl.company_id = 1237979465155112961
	where
		lr.rel_object_type = '1'
		and lr.rel_object_id = 1656203643885457409
	group by
		lr.rel_object_id
	order by
		lr.rel_object_id

 1. 自动转换要定义的字段名称
    jsonb_agg(JSONB_BUILD_OBJECT('labelId', dl.label_id,
				'labelName', dl.label_name) ) as labelList1,，