<?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.education.business.mapper.education.CourseInfoMapper">

    <select id="selectPageList" resultType="com.education.model.dto.CourseInfoDto">
        SELECT
            distinct course.*,
            subject.name subject_name,
            grade.name gradeInfoName
        FROM
           course_info course
        LEFT JOIN subject_info subject ON course.subject_id = subject.id
        LEFT JOIN grade_info grade ON course.grade_info_id = grade.id
       <where>
           <if test="courseInfo.name != null and courseInfo.name != ''">
               <bind name="name" value="'%' + courseInfo.name + '%'"/>
               and course.name like #{name}
           </if>

           <if test="courseInfo.recommendIndexFlag != null">
               and course.recommend_index_flag = #{courseInfo.recommendIndexFlag}
           </if>

           <if test="courseInfo.gradeInfoId != null">
               and course.grade_info_id = #{courseInfo.gradeInfoId}
           </if>

           <if test="courseInfo.subjectId != null">
               and course.subject_id = #{courseInfo.subjectId}
           </if>

           <if test="courseInfo.status != null">
               and course.status = #{courseInfo.status}
           </if>
       </where>
       order by course.sort desc
    </select>

    <select id="myCourseList" resultType="com.education.model.dto.CourseInfoDto">
        SELECT  distinct  course_info.*,student_course_collect.student_id
        FROM course_info,student_course_collect
        WHERE  course_info.id = student_course_collect.course_id
        AND student_course_collect.student_id =  #{userId}
    </select>

    <select id="selectListBySubjectId" resultType="com.education.model.dto.CourseInfoDto">
        SELECT  distinct  course_info.*,collect.student_id
        FROM course_info
            LEFT JOIN (select * from student_course_collect where student_id=#{userId}) as collect
        ON course_info.id = collect.course_id
        WHERE  course_info.subject_id =  #{subjectId}
    </select>
</mapper>