<?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="com.ylx.collect.mapper.CollectMapper">

    <select id="selectMyCollectPage" resultType="com.ylx.collect.domain.vo.CollectPageVO">
        SELECT
            c.id,
            c.merchant_id AS merchantId,
            t.te_nick_name AS merchantNickName,
            t.te_avatar AS merchantAvatar,
            t.te_brief AS brief,
            t.n_star AS star,
            t.n_num AS serviceNum,
            (
                SELECT COUNT(*)
                FROM collect c2
                WHERE c2.merchant_id = t.id
                  AND c2.is_delete = 0
            ) AS collectNum,
            @dist_km := (6371 * ACOS(
            COS(RADIANS(#{dto.latitude})) * COS(RADIANS(a.latitude)) *
            COS(RADIANS(a.longitude) - RADIANS(#{dto.longitude})) +
            SIN(RADIANS(#{dto.latitude})) * SIN(RADIANS(a.latitude))
            )) AS dist_km,
            CASE
            WHEN (@dist_km * 1000) &lt; 1000
            THEN CONCAT(CAST(ROUND(@dist_km * 1000) AS UNSIGNED), 'm')
            ELSE CONCAT(ROUND(@dist_km, 1), 'km')
        END AS distanceStr
    FROM collect c
    INNER JOIN ma_technician t ON c.merchant_id = t.id
    LEFT JOIN t_address a ON t.id = a.merchant_id AND a.is_default = 1 AND a.user_type = 2
    WHERE
      c.user_id = #{dto.userId}
    AND c.is_delete = 0
    ORDER BY c.create_time DESC
    </select>
</mapper>
