【Mybatis-plus-join】 SelectSunQuery

Metadata

title: 【Mybatis-plus-join】 SelectSunQuery
date: 2023-01-22 18:47
tags:
  - 行动阶段/完成
  - 主题场景/组件
  - 笔记空间/KnowladgeSpace/ProgramSpace/ModuleSpace
  - 细化主题/Module/Mybatis-plus-join
categories:
  - Mybatis-plus-join
keywords:
  - Mybatis-plus-join
description: 【Mybatis-plus-join】 SelectSunQuery

【Mybatis-plus-join】 SelectSunQuery

// joinList
List<UsersVo> list = Joins.of(Users.class)
                .masterLogicDelete(false)
                .pushLeftJoin(UsersVo::getUsersAge, UsersAge.class)
                .selectSunQuery(UsersAge.class, w -> {
                    w.eq(UsersAge::getId, Users::getAgeId)
                            .eq(UsersAge::getId, 1)
                            .le(UsersAge::getCreateTime, new Date())
                                  // 需要注意的是这个查询字段只能有一个
                            .selectAs(cb -> {
//                                cb.add("count(1)", "counts", false);
                                cb.add(UsersAge::getId, "counts");
                              // 这里的话，他的关联表是需要在之前出现的，这个selectSunQuery 在主表也是一样字表也是一样的，但是需要放在后面，因为如果在前面可能关联表的别名被重写定义了，那么他就会出现SQL错误
                            }).leftJoin(Users.class, Users::getAgeId, UsersAge::getId, w2 -> {
                                w2.eq(Users::getUserId, 1);
                            });
                })
                .joinAnd(0, w -> w.eq(UsersAge::getId, Users::getAgeId)
                                            .ne(UsersAge::getId, 10))
                .isNotNull(UsersAge::getId).end().joinList(UsersVo.class);

SELECT
    users.user_name,
    users.create_time,
    users.age_id,
    users.content_json,
    users.user_id,
    t1.age_doc AS t1_ageDoc,
    t1.age_name AS t1_ageName,
    t1.create_time AS t1_createTime,
    t1.content_json_age AS t1_contentJsonAge,
    t1.id AS t1_id,
    (
    SELECT
        t1.id AS counts 
    FROM
        users_age t1
        LEFT JOIN users AS users ON users.age_id = t1.id 
        AND users.age_id = 0 
    WHERE
        ( users.user_id = 1 ) 
        AND ( t1.id = users.age_id AND t1.id = 1 AND t1.create_time <= '2023-01-20 16:11:14.38' ) 
    ) AS counts 
FROM
    users AS users
    LEFT JOIN users_age AS t1 ON t1.id = users.age_id 
    AND ( t1.id = users.age_id AND t1.id <> 10 ) 
WHERE
    ( t1.id IS NOT NULL );