Open wuou-learn opened 1 year ago
主要改动了三个地方的sql,因这几个表的id都是自增的,取数据id最大值即=最新创建的数据,再根据id去查询相应数据
public List<SceneAuthorizedDO> getAuthorizedRecordsGroupBy(SceneAuthorizedQueryRequest query) {
List<SceneAuthorizedDO> sceneAuthorizedDoList = sceneAuthorizedMapper.selectList(buildQueryWrapper(query)
.select("max(id) id")
.groupBy(
"function_id"
));
if (CollectionUtil.isNullOrEmpty(sceneAuthorizedDoList)) {
return Lists.newArrayList();
}
List<Long> collect = sceneAuthorizedDoList.stream().map(SceneAuthorizedDO::getId).filter(Objects::nonNull).collect(Collectors.toList());
return sceneAuthorizedMapper.selectList(new QueryWrapper<SceneAuthorizedDO>().lambda().in(SceneAuthorizedDO::getId, collect));
}
public List<ApplicationDeviceDO> findUserDeviceGroupByAppId(String userId,List<Long> appIds) {
QueryWrapper<ApplicationDeviceDO> queryWrapper = new QueryWrapper<>();
queryWrapper.select("max(id) id");
queryWrapper.eq("status", DeviceStatus.ONLINE.getStatus());
queryWrapper.eq("is_deleted", 0);
if (CollectionUtil.isNullOrEmpty(appIds)) {
queryWrapper.eq("user_id", userId);
} else {
queryWrapper.and(wrapper -> wrapper.eq("user_id", userId).or().in("app_id", appIds));
}
queryWrapper.groupBy("app_id");
List<ApplicationDeviceDO> applicationDeviceIdList = applicationDeviceMapper.selectList(queryWrapper);
if (CollectionUtil.isNullOrEmpty(applicationDeviceIdList)) {
return Lists.newArrayList();
}
return applicationDeviceMapper.selectList(
new QueryWrapper<ApplicationDeviceDO>()
.lambda()
.in(ApplicationDeviceDO::getId,
applicationDeviceIdList
.stream()
.map(ApplicationDeviceDO::getId)
.filter(Objects::nonNull)
.collect(Collectors.toList())));
}
@Select("<script>" +
"select tmp.* from (" +
"SELECT t.* FROM t_chaos_application_device t where t.id in ( " +
"select " +
"max(ad.id) " +
"from t_chaos_application_device ad WHERE 1=1 " +
"<if test='null != query.appId and query.appId != \"\" '>" +
"AND ad.app_id = #{query.appId} " +
"</if>" +
"<if test='null != query.status and query.status != \"\" '>" +
"AND ad.status = #{query.status} " +
"</if>" +
"<if test='null != query.partName and query.partName != \"\" '>" +
"AND ad.private_ip like #{query.partName} " +
"</if>" +
"<if test='null != query.osType' >" +
"AND ad.os_type = #{query.osType} " +
"</if>" +
"<if test='null != query.groups and query.groups.size != 0'>" +
"AND ad.group_name in " +
"<foreach collection='query.groups' item='groupName' index='index' open='(' close=')' separator=',' >" +
"#{groupName}" +
"</foreach>" +
"</if>" +
"<if test='null != query.dimensions and query.dimensions.size != 0'>" +
"AND ad.dimension in " +
"<foreach collection='query.dimensions' item='dimension' index='index' open='(' close=')' separator=',' >" +
"#{dimension}" +
"</foreach>" +
"</if>" +
"<if test='null != query.tags and query.tags.size != 0'>" +
" AND ad.configuration_id IN " +
"(select configuration_id from t_chaos_application_device_tag adt where adt.tag_name in " +
"<foreach collection='query.tags' item='tag' index='index' open='(' close=')' separator=',' >" +
"#{tag}" +
"</foreach>" +
")" +
"</if>" +
"group by ad.private_ip " +
")) tmp " +
"ORDER BY tmp.gmt_create DESC" +
"</script>")
IPage<ApplicationDeviceDO> selectPageByTagsForHost(IPage<ApplicationDeviceDO> page, @Param("query") ApplicationDeviceQuery query);
非常感谢你的贡献,如果没有问题,会在下个版本中进行发布
背景
由于我们环境的mysql版本是8.0,所以在一些包含group by关键字的查询sql中,会报如下错误
这是由于5.7.5以后默认sql_mode包含ONLY_FULL_GROUP_BY导致的,由于严格模式开启对我们有利,所以我只能选择对代码中的sql进行兼容。
修改思路
对使用group by关键字查询的sql,先查出相应数据的id,根据id查询相应数据
tips
由于贡献文档的缺失,我不知该如何贡献自己修改的代码