记一次搜索框模糊查询的SQL语句

业务需求

在小程序的首页上方搜索框中进行模糊搜索，条件为 小组名称、小组标签、活动名称、活动标签

定义的返回值VO

import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.io.Serializable;
/**
 * <p>
 * 搜索框模糊搜索返回Vo
 * </p>
 *
 * @author LiuWenCheng
 * @since 2024-05-10
 */
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("group_posts")
@ApiModel(value = "首页搜索框", description = "首页搜索框")
public class SearchActivityGroupsSearchVo implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "类型")
    private String type;

    @ApiModelProperty(value = "id")
    private Integer id;


    @ApiModelProperty(value = "名称")
    private String name;

    @ApiModelProperty(value = "创建时间")
    private String date;

}

SQL语句

SELECT activity_id          AS ID,
       book_name            AS 名称,
       'activity'           AS 类型,
       activity_cteate_time AS 创建时间

FROM activity
WHERE book_name LIKE '%123%'
   OR activity_tags LIKE '%123%'

UNION ALL

SELECT group_id   AS ID,
       group_name AS 名称,
       'groups'   AS 类型,
       created_at AS 创建时间
FROM `groups`
WHERE group_name LIKE '%123%'
   OR group_tags LIKE '%123%'
GROUP BY ID, 名称, 类型, 创建时间
ORDER BY 创建时间 DESC;

XML文件语句

   <!--    根据关键字搜索小组、活动-->
    <select id="searchKeyword" resultType="com.lwc.vo.SearchActivityGroupsSearchVo">

        SELECT activity_id AS id, book_name AS name, 'activity' AS type, activity_cteate_time AS date
        FROM activity
        WHERE book_name LIKE CONCAT('%'
            , #{keyword}
            , '%')
           OR activity_tags LIKE CONCAT('%'
            , #{keyword}
            , '%')

        UNION ALL

        SELECT group_id AS id, group_name AS name, 'groups' AS type, created_at AS date
        FROM `groups`
        WHERE group_name LIKE CONCAT('%'
            , #{keyword}
            , '%')
           OR group_tags LIKE CONCAT('%'
            , #{keyword}
            , '%')
        GROUP BY type, id, name
        ORDER BY date DESC

    </select>