<?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.api.app.dao.homePage.AppSearchDao">

    <select id="searchActivity" parameterType="java.lang.String" resultType="com.api.vo.app.AppActivityVo">
        select * from sys_activity_management where is_delete = 1 and title like concat('%',#{searchName},'%')  order by create_date DESC limit 3
    </select>
    <select id="searchGambit" parameterType="java.lang.String" resultType="com.api.vo.app.AppGambitVo">
         select sg2.*,(case c.activity_num is null when true then 0 else c.activity_num end) as activity_num from sys_gambit as sg2 LEFT JOIN (
                    select a.id,(a.sum2+b.count2) as activity_num from (
                        select sg.id,(case SUM(sgt.likes) is null when true then 0 else SUM(sgt.likes) end) as sum2 from sys_gambit_theme as sgt,sys_gambit as sg where sgt.gambit_id = sg.id and sgt.is_delete = 1 group by sg.id
                    ) as a LEFT JOIN (
                        select sg.id,(case count(*) is null when true then 0 else count(*) end) as count2 from sys_gambit_theme_comment as sgtc,sys_gambit as sg where sgtc.gambit_id = sg.id  and sgtc.is_delete = 1  group by sg.id
                    ) as b on a.id = b.id
                ) as c on c.id = sg2.id where sg2.is_public = 1 and sg2.is_delete = 1 and sg2.is_rating = 1 and sg2.title like concat('%',#{searchName},'%') ORDER BY c.activity_num DESC limit 3
    </select>
</mapper>