<?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.article.dao.ArticleCategoryDao">

    <sql id="includeArticleCategory">
        ac.id id,
        ac.id article_category_id,
        ac.parent_id,
        act.category_name category_name,
        COALESCE(act.category_description, '') category_description,
        ac.sort,
        ac.status
    </sql>
    <sql id="includeArticleCategoryDetail">
        ac.id id,
        ac.id article_category_id,
        ac.parent_id,
        ac.sort,
        ac.status
    </sql>

    <sql id="includeArticleCategoryTranslation">
        id,
        article_category_id,
        language,
        category_name,
        COALESCE(category_description, '') category_description
    </sql>

    <sql id="leftJoinTranslations">
        LEFT JOIN cms_article_category_i18n act ON ac.id = act.article_category_id
    </sql>

    <resultMap id="resultMapArticleCategory" type="java.util.LinkedHashMap">
        <id property="id" column="id" jdbcType="BIGINT" />
        <result property="article_category_id" column="article_category_id" javaType="java.lang.Long" />
        <result property="parent_id" column="parent_id" javaType="java.lang.Long" />
        <result property="category_name" column="category_name" />
        <result property="category_description" column="category_description" />
        <result property="sort" column="sort" javaType="java.lang.Integer" />
        <result property="status" column="status" javaType="java.lang.Integer" />
    </resultMap>

    <resultMap id="resultMapArticleCategoryDetail" type="java.util.LinkedHashMap">
        <id property="id" column="id" jdbcType="BIGINT" />
        <result property="article_category_id" column="article_category_id" javaType="java.lang.Long" />
        <result property="parent_id" column="parent_id" javaType="java.lang.Long" />
        <result property="sort" column="sort" javaType="java.lang.Integer" />
        <result property="status" column="status" javaType="java.lang.Integer" />
        <collection property="translations" javaType="java.util.List"
            select="queryTranslationsById" column="id">
            <result property="category_name" column="category_name" />
            <result property="category_description" column="category_description" />
        </collection>
    </resultMap>


    <!-- 查 列表 -->
    <select id="selectArticleCategoryList" resultMap="resultMapArticleCategory">
        SELECT <include refid="includeArticleCategory" /> FROM cms_article_category ac
        <include refid="leftJoinTranslations" />
        <where>
            act.language = #{lang}
            <if test="category_name != null and category_name != ''">
                AND act.category_name LIKE CONCAT('%', #{category_name}, '%')
            </if>
            <if test="status != null and status != ''">
                AND ac.status = #{status}
            </if>
        </where>
        ORDER BY ac.sort DESC
    </select>

    <!-- 查 详情 -->
    <select id="selectArticleCategoryDetail" resultMap="resultMapArticleCategoryDetail">
        SELECT <include refid="includeArticleCategoryDetail" />
        FROM cms_article_category ac
        <where>
            <if test="article_category_id != null and article_category_id != ''">
                AND ac.id = #{article_category_id}
            </if>
        </where>
    </select>

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

    <!-- 查 详情 (公共) (带翻译 Object) (关联查询) -->
<!--    <select id="queryArticleCategoryDetailPublic" resultMap="resultMapArticleCategory">-->
<!--        SELECT <include refid="includeArticleCategory" />, content-->
<!--        FROM cms_article_category a-->
<!--        <include refid="leftJoinCategory" />-->
<!--        <include refid="leftJoinUser" />-->
<!--        <include refid="leftJoinTranslations" />-->
<!--        WHERE a.uid = #{uid} AND act.language = #{lang}-->
<!--    </select>-->

</mapper>