如何通过podcasts表的weight字段对podcasts_episodes表进行全文搜索加权？

你需要通过关联podcasts表获取对应播客的权重，以此替代剧集表自身的weight字段进行排序。以下是修改后的SQL查询及说明：

修改后的查询语句 #

SELECT 
    pe.*, 
    p.weight AS podcast_weight,
    MATCH(pe.name, pe.description) AGAINST('industrial') AS relevance
FROM 
    podcasts_episodes pe
INNER JOIN 
    podcasts p ON pe.podcast = p.id
WHERE 
    MATCH(pe.name, pe.description) AGAINST('industrial') 
ORDER BY 
    p.weight DESC, 
    relevance DESC 
LIMIT 10;

关键修改点 #

• 关联播客表：使用INNER JOIN将剧集表（别名pe）与播客表（别名p）通过pe.podcast = p.id关联，直接获取对应播客的权重
• 避免字段冲突：用pe.*代替*，同时将播客权重命名为podcast_weight，解决两张表都有weight字段的歧义问题
• 调整排序逻辑：将排序依据改为播客表的p.weight，确保高权重播客下的相关剧集优先展示，同权重播客内的剧集再按搜索相关性排序

可选优化（处理无对应播客的剧集） #

如果存在剧集未关联到播客的情况，可改用LEFT JOIN，并通过COALESCE给无播客的剧集设置默认权重：

SELECT 
    pe.*, 
    COALESCE(p.weight, 0) AS podcast_weight,
    MATCH(pe.name, pe.description) AGAINST('industrial') AS relevance
FROM 
    podcasts_episodes pe
LEFT JOIN 
    podcasts p ON pe.podcast = p.id
WHERE 
    MATCH(pe.name, pe.description) AGAINST('industrial') 
ORDER BY 
    podcast_weight DESC, 
    relevance DESC 
LIMIT 10;

修改后，搜索结果会严格按照「高权重播客优先，同播客内相关性优先」的逻辑排序，完全符合你预期的展示效果。

内容的提问来源于stack exchange，提问作者William Cross