通过关键词来推荐话题

1、需求介绍：通过关键词来推荐话题

算法：

- 基于正文文字 + 资源名 + 套系书名，推荐话题

- 排序：

1）按话题匹配次数排序

2）话题匹配次数一致，记录数高的话题排在前面

3）匹配话题结束后，展示热门话题

 

2、涉及到的表格

====阅读话题关键字表（kid_reading_topic_keyword）
id - ID
type - 类型（1正文/2书名/3套系书名）
keyword - 关键字
topic_id - 话题ID

====临时表（kid_temp_data）
id - ID
data - 数据

====话题（kid_edu_resource_topic）
id - ID
title - 标题
reading_record_punch_user_count 阅读记录打卡孩子数
...

 

3、具体实现

// serviceImpl
private List<IdName> getRecommendTopicForReading(String content, String resourceName, String taoxiName, int offset, int limit) {

boolean hasContent = !TextUtil.isNullOrWhiteSpace(content);
        boolean hasResource = !TextUtil.isNullOrWhiteSpace(resourceName);
        boolean hasTaoxi = !TextUtil.isNullOrWhiteSpace(taoxiName);
        
        if (!hasContent && !hasResource && !hasTaoxi) return getHotPunchTopic(offset, limit, 2);
        
        long tempDataId1 = 0;
        long tempDataId2 = 0;
        long tempDataId3 = 0;
        KidTempData tempData;
        try {
            if (hasContent) {
                tempData = new KidTempData();
                tempData.setData(content);
                tempDataMapper.insertSelective(tempData);
                tempDataId1 = tempData.getId();
            }
            
            if (hasResource) {
                tempData = new KidTempData();
                tempData.setData(resourceName);
                tempDataMapper.insertSelective(tempData);
                tempDataId2 = tempData.getId();
            }
            
            if (hasTaoxi) {
                tempData = new KidTempData();
                tempData.setData(taoxiName);
                tempDataMapper.insertSelective(tempData);
                tempDataId3 = tempData.getId();
            }

            return readingRecordV2Mapper.getRecommendTopic(tempDataId1, tempDataId2, tempDataId3, offset, limit);
        } finally {
            if (tempDataId1!=0) tempDataMapper.deleteByPrimaryKey(tempDataId1);
            if (tempDataId2!=0) tempDataMapper.deleteByPrimaryKey(tempDataId2);
            if (tempDataId3!=0) tempDataMapper.deleteByPrimaryKey(tempDataId3);
        }
    }

<select id="getRecommendTopic" resultType="xhs.appApi.defineClass.IdName">
(
SELECT
    a.id pk,
    a.title name,
    a.count,
    COUNT(*) matchCount
FROM
    (
    SELECT
        t.id,
        t.title,
        t.reading_record_punch_user_count count
    FROM
        kid_temp_data d, kid_reading_topic_keyword k, kid_edu_resource_topic t
    WHERE
        d.id=#{id1}
        AND LOCATE(k.keyword, d.data)>0 AND k.type=1
        AND t.id=k.topic_id AND t.`status`=1
    UNION ALL
    SELECT
        t.id,
        t.title,
        t.reading_record_punch_user_count
    FROM
        kid_temp_data d, kid_reading_topic_keyword k, kid_edu_resource_topic t
    WHERE
        d.id=#{id2}
        AND LOCATE(k.keyword, d.data)>0 AND k.type=2
        AND t.id=k.topic_id AND t.`status`=1
    UNION ALL
    SELECT
        t.id,
        t.title,
        t.reading_record_punch_user_count
    FROM
        kid_temp_data d, kid_reading_topic_keyword k, kid_edu_resource_topic t
    WHERE
        d.id=#{id3}
        AND LOCATE(k.keyword, d.data)>0 AND k.type=3
        AND t.id=k.topic_id AND t.`status`=1
    ) a
GROUP BY a.id
)
UNION
(
SELECT
    t.id pk,
    t.title name,
    t.reading_record_punch_user_count count,
    0
FROM
    kid_edu_resource_topic t
WHERE
    t.reading_record_punch_user_count>0
)
ORDER BY matchCount DESC, count DESC
LIMIT ${offset}, ${limit}   
  </select>