<?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.backendsys.modules.cms.page.dao.PageDao">

    <sql id="includePage">
        p.id id,
        p.page_sign page_sign,
        pt.title title,
        COALESCE(pt.description, '') description,
        p.sort sort,
        p.create_time create_time,
        p.update_time update_time
    </sql>

    <sql id="includePageDetail">
        p.id id,
        p.page_sign page_sign,
        p.sort sort,
        p.create_time create_time,
        p.update_time update_time
    </sql>
    <sql id="includePageTranslation">
        id, page_sign, language,
        title, description, content,
        COALESCE(meta_keyword, '') meta_keyword,
        COALESCE(meta_description, '') meta_description
    </sql>

    <sql id="leftJoinTranslations">
        LEFT JOIN cms_page_i18n pt ON p.page_sign = pt.page_sign
    </sql>

    <resultMap id="resultMapPage" type="java.util.LinkedHashMap">
        <id property="id" column="id" jdbcType="BIGINT" />
        <result property="page_sign" column="page_sign" />
        <result property="title" column="title" />
        <result property="description" column="description" />
        <result property="sort" column="sort" javaType="java.lang.Integer" />
        <result property="create_time" column="create_time" />
        <result property="update_time" column="update_time" />
    </resultMap>

    <!--  -->
    <resultMap id="resultMapPageDetail" type="java.util.LinkedHashMap" >
        <id property="id" column="id" jdbcType="BIGINT" />
        <result property="page_sign" column="page_sign" />
        <result property="sort" column="sort" javaType="java.lang.Integer" />
        <result property="create_time" column="create_time" />
        <result property="update_time" column="update_time" />
        <collection property="translations" javaType="java.util.List"
            select="queryTranslationsById" column="page_sign">
            <id property="id" column="id" />
            <result property="page_sign" column="page_sign" />
            <result property="title" column="title" />
            <result property="content" column="content" />
            <result property="description" column="description" />
            <result property="meta_keyword" column="meta_keyword" />
            <result property="meta_description" column="meta_description" />
        </collection>
    </resultMap>


    <!-- 查 列表 -->
    <select id="selectPageList" resultMap="resultMapPage">
        SELECT <include refid="includePage" /> FROM cms_page p
        <include refid="leftJoinTranslations" />
        <where>
            pt.language = #{lang}
            <if test="title != null and title != ''">
                AND pt.title LIKE CONCAT('%', #{title}, '%')
            </if>
            <if test="create_time != null and create_time != ''">
                AND p.create_time >= #{create_time}
            </if>
        </where>
        ORDER BY p.create_time DESC, p.sort DESC
    </select>

    <!-- 查 详情 -->
    <select id="selectPageDetail" resultMap="resultMapPageDetail">
        SELECT <include refid="includePageDetail" />
        FROM cms_page p
        <where>
            <if test="page_sign != null and page_sign != ''">
                AND p.page_sign = #{page_sign}
            </if>
        </where>
    </select>

    <!-- 查 翻译详情 (子查询) -->
    <select id="queryTranslationsById" resultType="java.util.LinkedHashMap">
        SELECT <include refid="includePageTranslation" />
        FROM cms_page_i18n
        WHERE page_sign = #{page_sign}
    </select>

</mapper>