spring同一个接口如何分页实现主表或主+联表group by查询

1 需求背景

我们的上游系统想要知道主表的记录关联子表所有记录中是否有一条满足特定的条件，如果有满足的就返回主表中的id。比如品牌brand主表中id为10的记录，在子表brand_rel中有id为1，2，3，4的这四个记录，其中1，2所在记录的status字段值满足等于0，那么就可以返回10给调用方。

其次这个查询功能是要做在原来的查询接口中的，旧接口只允许匹配针对主表的条件进行分页查询。

2 需求分析

2.1 如何兼容旧功能

通过在入参的请求体中新增一个子表的查询字段，后端拿到请求体会先判断子表的这个字段是否为空，如果为空则走旧有的查询条件，否则走新的查询分支。

2.2 联表查询，一个主表的记录会匹配到多个子表的记录，需要去重。

使用group by关键字对主表的id进行去重。

2.3 量很大时，需要分页

使用合理的分页插件。

3 实现

3.1 需求背景和分析

1. 主表 brand 记录与子表 brand_rel 记录有一对多的关系。
2. 新增一个子表查询字段来扩展旧接口。
3. 使用 GROUP BY 去重主表记录。
4. 使用分页插件实现数据的分页。

为了实现这一需求，假设我们在使用 Spring Boot 框架和 MyBatis-Plus 分页插件。

3.2 数据库表结构

-- 主表 brand
CREATE TABLE brand (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100)
);

-- 子表 brand_rel
CREATE TABLE brand_rel (
    id BIGINT PRIMARY KEY,
    brand_id BIGINT,
    status INT,
    FOREIGN KEY (brand_id) REFERENCES brand(id)
);

3.3 SQL 查询语句

为了得到符合条件的主表 brand ID 列表，SQL 查询如下：

SELECT b.id
FROM brand b
JOIN brand_rel br ON b.id = br.brand_id
WHERE br.status = 0
GROUP BY b.id
LIMIT #{offset}, #{pageSize};

3.4 Controller 和 Service 层实现

以下提供一个简单的 Spring Boot Controller 和 Service 实现。

3.4.1 请求体对象

public class BrandQueryRequest {
    private String mainTableCondition; // 主表的查询条件，可以扩展
    private String subTableCondition;  // 子表的查询条件，比如 "br.status = 0"
    private int offset;
    private int pageSize;

    // getters and setters
}

3.4.2 Controller

@RestController
@RequestMapping("/api/brands")
public class BrandController {

    @Autowired
    private BrandService brandService;

    @PostMapping("/query")
    public ResponseEntity<List<Long>> queryBrands(@RequestBody BrandQueryRequest request) {
        List<Long> result = brandService.queryBrandIds(request);
        return ResponseEntity.ok(result);
    }
}

3.4.3 Service

@Service
public class BrandService {

    @Autowired
    private BrandMapper brandMapper;

    public List<Long> queryBrandIds(BrandQueryRequest request) {
        if (request.getSubTableCondition() == null) {
            // 走旧查询逻辑
            return brandMapper.queryBrandsByMainCondition(request.getMainTableCondition(), request.getOffset(), request.getPageSize());
        } else {
            // 新的查询逻辑
            return brandMapper.queryBrandsBySubCondition(request.getSubTableCondition(), request.getOffset(), request.getPageSize());
        }
    }
}

3.4.4 Mapper

利用 MyBatis-Plus，定义一个自定义的 SQL 查询方法：

@Mapper
public interface BrandMapper {
    
    @Select("<script>" +
            "SELECT b.id " +
            "FROM brand b " +
            "<where>" +
            "  <if test='mainTableCondition != null'>AND ${mainTableCondition}</if> " +
            "</where>" +
            "GROUP BY b.id " +
            "LIMIT #{offset}, #{pageSize}" +
            "</script>")
    List<Long> queryBrandsByMainCondition(@Param("mainTableCondition") String mainTableCondition, 
                                          @Param("offset") int offset, 
                                          @Param("pageSize") int pageSize);

    @Select("<script>" +
            "SELECT b.id " +
            "FROM brand b " +
            "JOIN brand_rel br ON b.id = br.brand_id " +
            "<where>" +
            "  <if test='subTableCondition != null'>AND ${subTableCondition}</if> " +
            "</where>" +
            "GROUP BY b.id " +
            "LIMIT #{offset}, #{pageSize}" +
            "</script>")
    List<Long> queryBrandsBySubCondition(@Param("subTableCondition") String subTableCondition, 
                                         @Param("offset") int offset, 
                                         @Param("pageSize") int pageSize);
}

3.4.5 总结

这样，我们就成功地实现了一个能够兼容旧功能并支持新功能的查询接口。通过在请求体中新增一个子表的查询字段来扩展逻辑，确保最大限度的代码复用和功能扩展。