<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="shop.alien.mapper.PlatformLifeUserMapper">

    <!-- 查询用户列表，支持分页和动态条件 -->
    <select id="getUserList" resultType="shop.alien.entity.store.vo.LifeUserVo">
        WITH orders AS (
        SELECT user_id, COUNT(1) consume_num, SUM(final_price) consume_price
        FROM life_user_order
        WHERE delete_flag = 0 AND status = '1'
        GROUP BY user_id
        ),
        comment AS (
        SELECT user_id, COUNT(1) bad_num
        FROM store_comment
        WHERE delete_flag = 0 AND business_type = '1' AND comment_star &lt; 1 AND reply_id IS NULL
        GROUP BY user_id
        )
        SELECT
        user.id,
        user.user_name,
        user.real_name,
        user.user_phone,
        user.created_time,
        ifnull(orders.consume_num, 0) consume_num,
        ifnull(orders.consume_price, 0) consume_price,
        IFNULL(comment.bad_num, 0) AS bad_num,
        COUNT(lf.id) AS fans_count,
        lue.id as expert_id,
        lue.expert_code as expert_code
        FROM life_user user
        LEFT JOIN orders ON orders.user_id = user.id
        LEFT JOIN comment ON comment.user_id = user.id
        LEFT JOIN life_fans lf ON lf.followed_id = CONCAT('user_', user.user_phone) and lf.delete_flag = 0
        LEFT JOIN life_user_expert lue ON lue.user_id = user.id and lue.delete_flag = 0
        <!-- 动态 SQL 片段，用于拼接查询条件 -->
        <if test="ew.customSqlSegment != null and ew.customSqlSegment != ''">
            ${ew.customSqlSegment}
        </if>
        GROUP BY user.id, user.user_name, user.real_name, user.user_phone, user.created_time,
        orders.consume_num, orders.consume_price, comment.bad_num,expert_id,expert_code
        ORDER BY user.created_time DESC
    </select>
</mapper>