<?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="cn.com.ty.lift.ud.expertComment.mapper.ExpertCommentMapper">
    <resultMap id="BaseResultMap" type="cn.com.ty.lift.ud.expertComment.mapper.entity.ExpertCommentEntity">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="tech" property="tech" jdbcType="INTEGER"/>
        <result column="response_speed" property="responseSpeed" jdbcType="INTEGER"/>
        <result column="attitude" property="attitude" jdbcType="INTEGER"/>
        <result column="comment" property="comment" jdbcType="VARCHAR"/>
        <result column="expert_id" property="expertId" jdbcType="INTEGER"/>
        <result column="user_id" property="userId" jdbcType="INTEGER"/>
        <result column="create_by" property="createBy" jdbcType="VARCHAR"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_by" property="updateBy" jdbcType="VARCHAR"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
        <result column="life_case_id" property="lifeCaseId" jdbcType="INTEGER"/>
        <result column="statuz" property="statuz" jdbcType="INTEGER"/>


    </resultMap>
    <sql id="Base_Column_List">
    id, tech, response_speed, attitude, comment, expert_id,user_id,life_case_id,
    create_by, create_time, update_by, update_time,statuz
  </sql>

    <!--多表查询-->
    <select id="selectByQuery" resultType="cn.com.ty.lift.ud.expertComment.mapper.entity.ExpertCommentEntity">
        select lc.expression,ec.*,
        info.`name` as expertName,us.name as userName,us.avatar_url as userUrl
        from expert_comment ec
        left join  user_info  us on us.user_id = ec.user_id
        left join  lift_case  lc on  ec.life_case_id = lc.id
        left join user_info info on info.user_id = ec.expert_id
        <where>
            ${ew.sqlSegment}
        </where>
        limit #{page.pageIndex},#{page.pageSize}
</select>

<!--查询总数量-->
    <select id="countByQuery" resultType="java.lang.Integer">
        select count(0) from expert_comment ec
        left join  user_info  us on us.user_id = ec.expert_id
        left join  lift_case  lc on  ec.life_case_id = lc.id
        left join user_info info on info.user_id = ec.expert_id
        <where>
            ${ew.sqlSegment}
        </where>
    </select>

    <!--获取单个专家评论-->
    <select id="getExpertComment" resultType="cn.com.ty.lift.ud.expertComment.mapper.entity.ExpertCommentEntity">
        select ec.`comment` as comment, ec.create_time as createTime,ui.mobile as mobile ,
        lift.expression as expression
        from expert_comment ec
        left join user_info ui on ui.user_id = ec.user_id
        left join lift_case lift on ec.life_case_id = lift.id
        <where>
            ec.statuz = 0 and
            ${ew.sqlSegment}
        </where>
        limit #{page.pageIndex},#{page.pageSize}
    </select>

    <!--查询评论总数量-->
    <select id="countAllByQuery" resultType="java.lang.Integer">
        select count(0) from expert_comment ec
        left join user_info ui on ui.user_id = ec.user_id
        left join lift_case lift on ec.life_case_id = lift.id
        <where>
            ec.statuz = 0 and
            ${ew.sqlSegment}
        </where>
    </select>



</mapper>