Khi làm việc với dữ liệu phân cấp như danh mục sản phẩm, menu đa cấp, việc truy vấn và sắp xếp theo thứ tự cha-con là một yêu cầu phổ biến. Bài viết này trình bày cách giải quyết vấn đề này trong MySQL kết hợp với MyBatis-Plus.

Vấn đề cần giải quyết

Giả sử có bảng category lưu trữ danh mục xe hơi:

id  | parent_id | name
----|-----------|----------
8   | 0         | BMW
2   | 0         | Mercedez
3   | 0         | Porsche
4   | 8         | 3 Series
5   | 2         | E60
6   | 8         | 5 Series
7   | 3         | Cayenne

Yêu cầu: Hiển thị danh sách sao cho danh mục cha đứng đầu, các danh mục con của nó liền kề phía dưới, sau đó đến danh mục cha tiếp theo:

BMW
3 Series
5 Series
Mercedez
E60
Porsche
Cayenne

Câu truy vấn SQL

Câu lệnh SQL sử dụng CASE để tạo giá trị sắp xếp:

SELECT
  name
FROM
  category
ORDER BY
  CASE WHEN parent_id = 0 THEN id ELSE parent_id END,
  parent_id,
  id

Giải thích cơ chế sắp xếp:

1. Cột sắp xếp thứ nhất: Với danh mục cha (parent_id = 0), dùng chính id của nó; với danh mục con, dùng parent_id. Điều này nhóm tất cả danh mục con vào cùng nhóm với cha của chúng.
2. Cột sắp xếp thứ hai: parent_id - đảm bảo danh mục cha (có parent_id = 0) luôn đứng đầu nhóm.
3. Cột sắp xếp thứ ba: id - sắp xếp các danh mục con theo thứ tự tăng dần.

Tích hợp với MyBatis-Plus

MyBatis-Plus cung cấp QueryWrapper và LambdaQueryWrapper để xây dựng truy vấn động. Để chèn mệnh đề ORDER BY tùy chỉnh, sử dụng phương thức last().

Phương thức last()

last(String lastSql)
last(boolean condition, String lastSql)

Đặc điểm:

• Gắn trực tiếp chuỗi SQL vào cuối câu truy vấn
• Chỉ gọi được một lần, nếu gọi nhiều lần chỉ lần cuối cùng có hiệu lực
• Cảnh báo: Có rủi ro SQL injection, cần kiểm soát chặt chẽ dữ liệu đầu vào

Ví dụ triển khai

@Autowired
private CategoryMapper categoryMapper;

@Test
void hierarchicalSortTest() {
    QueryWrapper<Category> wrapper = new QueryWrapper<>();
    
    wrapper.last("ORDER BY CASE WHEN parent_id = 0 THEN id ELSE parent_id END, parent_id, id");
    
    List<Category> result = categoryMapper.selectList(wrapper);
    result.forEach(System.out::println);
}

Biến thể với LambdaQueryWrapper

@Test
void lambdaSortTest() {
    LambdaQueryWrapper<Category> lambdaWrapper = new LambdaQueryWrapper<>();
    
    lambdaWrapper.last("ORDER BY CASE WHEN parent_id = 0 THEN id ELSE parent_id END, parent_id, id");
    
    List<Category> categories = categoryMapper.selectList(lambdaWrapper);
}

Phương thức apply() - Lựa chọn thay thế

Nếu cần sử dụng hàm cơ sở dữ liệu trong điều kiện WHERE, có thể dùng apply():

apply(String applySql, Object... params)
apply(boolean condition, String applySql, Object... params)

Ví dụ sắp xếp theo tên tiếng Trung:

LambdaQueryWrapper<Category> wrapper = new LambdaQueryWrapper<>();
wrapper.apply("CONVERT(name USING GBK) ASC");

Hoặc với tham số an toàn:

wrapper.apply("date_format(create_time, '%Y-%m-%d') = {0}", "2024-01-15");

Tham số {0} được thay thế bằng giá trị truyền vào, giúp tránh SQL injection.

Lưu ý quan trọng

Giải pháp CASE WHEN hoạt động tốt với điều kiện:

• Danh mục gốc có parent_id = 0 (hoặc giá trị nhận diện rõ ràng)
• Chỉ có một cấp danh mục con (không hỗ trợ đệ quy nhiều tầng)

Với cấu trúc đệ quy nhiều cấp, cần sử dụng Recursive CTE (MySQL 8.0+) hoặc xử lý phân cấp trong ứng dụng.