mybatisFlex各种链式sql写法小结

 public list<tocagentuserrelationvo> selecttocagentuserrelationbyphones(list<string> phones) {
        querywrapper querywrapper = querywrapper.create()
                .select(toc_agent_user_relation.all_columns)
                .select(toc_user_info.phone,toc_user_info.nick_name)
                .select(toc_group_patient_relations.doctor_id,toc_group_patient_relations.create_time.as("binddoctortime"))
                .from(toc_agent_user_relation)
                .leftjoin(toc_user_info).on(toc_user_info.id.eq(toc_agent_user_relation.user_id))
                .leftjoin(toc_group_patient_relations).on(toc_group_patient_relations.patient_id.eq(toc_agent_user_relation.user_id))
                .where(toc_user_info.phone.in(phones))
                .orderby(toc_agent_user_relation.create_time.desc());
        return tocagentuserrelationmapper.selectlistbyqueryas(querywrapper,tocagentuserrelationvo.class);
    }

 public <r> page<r> dietitianlist(dietitianlistrequest request, class<r> astype) {
        querywrapper querywrapper = querywrapper.create()
                .select(toc_user_info.id,toc_user_info.nick_name,toc_user_info.img_url,toc_user_info.tag,toc_user_info.good_at,toc_user_info.good_at_remark)
                .select(toc_nutritionist_order_num.consult_order_number)
                //计算好评率=好评数目/总订单数目
                .select(toc_nutritionist_order_num.good_reputation_number.divide(toc_nutritionist_order_num.consult_order_number).as("goodrate"))
                .from(toc_user_info)
                .leftjoin(toc_nutritionist_order_num).on(toc_nutritionist_order_num.user_id.eq(toc_user_info.id));
        querywrapper.where(toc_user_info.platform.eq(3));
        querywrapper.where(toc_user_info.user_type.eq(usertypeenum.toc_doctor));
        if(request.getillnessid() != null){
            querywrapper.and("find_in_set('" + request.getillnessid() + "',good_at)");
        }

        if(request.getillnessids() != null && !request.getillnessids().isempty()){
            querywrapper.and(q -> { for (long illnessid : request.getillnessids()) { q.or("find_in_set('" + illnessid + "',good_at)"); } });

        }
        querywrapper.orderby("goodrate", false);
        pagedomain pagedomain = tablesupport.buildpagerequest();
        return tocuserinfomapper.paginateas(pagedomain.getpagenum(), pagedomain.getpagesize(), querywrapper, astype);
    }

   public void updatereservephone(nutritionistrequest request) {
        updatechain.of(tocuserinfo.class)
                .set(tocuserinfo::getreservephone, request.getreservephone())
                .where(tocuserinfo::getid).eq(request.getuserid())
                .update();
    }

    public statisticsreturnvo selectregistrationcodeincome(statisticsrequest request) {
        querywrapper wrapper = querywrapper.create()
                .select(sum(case_().when(sys_wx_pay_order.user_from.eq(4)).then(sys_wx_pay_order.pay_money).else_(0.00).end()).as("num1"))
                .select(sum(case_().when(sys_wx_pay_order.user_from.eq(5)).then(sys_wx_pay_order.pay_money).else_(0.00).end()).as("num2"))
                .select(sum(case_().when(sys_wx_pay_order.user_from.eq(2)).then(sys_wx_pay_order.pay_money).else_(0.00).end()).as("num3"))
                .from(sys_wx_pay_order)
                .where(sys_wx_pay_order.user_from.in(2,4,5));
        wrapper.eq(syswxpayorder::getagentuserid, request.getagentid());
        wrapper.between(syswxpayorder::getcreatetime, request.getbegintime(), request.getendtime());
        wrapper.eq(syswxpayorder::getpaystate,1);
        return syswxpayordermapper.selectonebyqueryas(wrapper, statisticsreturnvo.class);
    }

public list<tocreservetimevo> getreservetimeslotrule(tocreservequery query) {
        querywrapper querywrapper = querywrapper.create()
                .select(toc_reserve_time_slot_rule.id.as("slotid"),
                        toc_reserve_time_slot_rule.time_slot,
                        toc_reserve_time_slot_rule.max_capacity.as("totalavailable"))
                .select(count(case_().when(toc_reserve_detail.status.eq(1)).then(toc_reserve_detail.id).else_(null).end()).as("reservedcount"))
                .select(toc_reserve_time_slot_rule.max_capacity.subtract(count(toc_reserve_detail.id)) .as("remainingslots"))
                .from(toc_reserve_time_slot_rule)
                .leftjoin(toc_reserve_detail).on(toc_reserve_detail.slot_id.eq(toc_reserve_time_slot_rule.id))
                .where(toc_reserve_time_slot_rule.date_rule_id.eq(query.getid()))
                .groupby(toc_reserve_time_slot_rule.id, toc_reserve_time_slot_rule.time_slot, toc_reserve_time_slot_rule.max_capacity);
        return tocreservetimeslotrulemapper.selectlistbyqueryas(querywrapper,tocreservetimevo.class);
    }

 public <r> page<r> dietitianlist(dietitianlistrequest request, class<r> astype) {
        querywrapper querywrapper = querywrapper.create()
                .select(toc_user_info.id,toc_user_info.nick_name,toc_user_info.img_url,toc_user_info.tag,toc_user_info.good_at,toc_user_info.good_at_remark)
                .select(toc_nutritionist_order_num.consult_order_number)
                //计算好评率=好评数目/总订单数目
                .select(toc_nutritionist_order_num.good_reputation_number.divide(toc_nutritionist_order_num.consult_order_number).as("goodrate1"))
                .select(toc_nutritionist_order_num.good_reputation_number.multiply(toc_nutritionist_order_num.consult_order_number).as("goodrate2"))
                .select(toc_nutritionist_order_num.good_reputation_number.subtract(toc_nutritionist_order_num.consult_order_number).as("goodrate3"))
                .select(toc_nutritionist_order_num.good_reputation_number.add(toc_nutritionist_order_num.consult_order_number).as("goodrate4"))
                .from(toc_user_info)
                .leftjoin(toc_nutritionist_order_num).on(toc_nutritionist_order_num.user_id.eq(toc_user_info.id));
        querywrapper.where(toc_user_info.platform.eq(3));
        querywrapper.where(toc_user_info.user_type.eq(usertypeenum.toc_doctor));
        pagedomain pagedomain = tablesupport.buildpagerequest();
        return tocuserinfomapper.paginateas(pagedomain.getpagenum(), pagedomain.getpagesize(), querywrapper, astype);
    }

  public <r> page<r> dietitianlist(dietitianlistrequest request, class<r> astype) {
        querywrapper querywrapper = querywrapper.create()
                .select(toc_user_info.id,toc_user_info.nick_name,toc_user_info.img_url,toc_user_info.tag,toc_user_info.good_at,toc_user_info.good_at_remark)
                .select(toc_nutritionist_order_num.consult_order_number)
                //计算好评率=好评数目/总订单数目
                .select(toc_nutritionist_order_num.good_reputation_number.divide(toc_nutritionist_order_num.consult_order_number).as("goodrate"))
                .from(toc_user_info)
                .leftjoin(toc_nutritionist_order_num).on(toc_nutritionist_order_num.user_id.eq(toc_user_info.id));
        querywrapper.and(toc_nutritionist_order_num.good_reputation_number.eq(1).or(toc_nutritionist_order_num.good_reputation_number.gt(1)));

        
        pagedomain pagedomain = tablesupport.buildpagerequest();
        return tocuserinfomapper.paginateas(pagedomain.getpagenum(), pagedomain.getpagesize(), querywrapper, astype);
    }

  public int batchuntiegroup(list<long> relationids) {
        //每次执行1000条
        db.executebatch(relationids, 1000, tocgrouppatientrelationsmapper.class
                , (mapper, relationid) -> {
                    //下面是具体的执行sql写法
                    updatechain.of(mapper)
                            .set(tocgrouppatientrelations::getgroupid, null)
                            .set(tocgrouppatientrelations::getgroupname, "")
                            .where(tocgrouppatientrelations::getrelationid).eq(relationid)
                            .update();
                });
        return constants.success_code;
    }