如何在Sphinx查询中排除包含特定country_id的article_id？

索引数据示例 #

需求 #

需要获取完全不包含country_id=1的article_id，预期结果为2和3。但当前简单查询仅能排除article_id=5，返回1、2、3、4，无法满足需求（Sphinx不支持连接和子查询）。

简化后的Sphinx索引配置 #

sql_query_pre = SET @a := 0;

sql_query = \
SELECT \
@a := @a + 1 AS id, \
ase.id AS search_id, \
ase.name, \
ase.article_id, \
af.country_id \
FROM articles_search ase \
LEFT JOIN articles a ON a.id = ase.article_id \
LEFT JOIN affiliation_article aa ON aa.article_id = ase.article_id \
LEFT JOIN affiliations af ON aa.affiliation_id = af.id;

sql_attr_bigint=country_id
sql_attr_bigint=article_id

当前使用的查询语句 #

SELECT article_id, WEIGHT() w FROM articles WHERE MATCH('(@name nasicon)') AND country_id != 9 GROUP BY article_id ORDER BY w DESC LIMIT 0, 15 OPTION max_matches = 200

解决方案 #

方法1：修改索引结构，预计算标记字段 #

在索引构建阶段，直接为每个article_id标记是否包含country_id=1，查询时可直接过滤。

修改sql_query，添加计算字段has_country_1：

sql_query_pre = SET @a := 0;

sql_query = \
SELECT \
@a := @a + 1 AS id, \
ase.id AS search_id, \
ase.name, \
ase.article_id, \
af.country_id, \
MAX(CASE WHEN af.country_id = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY ase.article_id) AS has_country_1 \
FROM articles_search ase \
LEFT JOIN articles a ON a.id = ase.article_id \
LEFT JOIN affiliation_article aa ON aa.article_id = ase.article_id \
LEFT JOIN affiliations af ON aa.affiliation_id = af.id;

sql_attr_bigint=country_id
sql_attr_bigint=article_id
sql_attr_uint=has_country_1  -- 添加该属性

查询时添加has_country_1 = 0条件：

SELECT article_id, WEIGHT() w FROM articles WHERE MATCH('(@name nasicon)') AND has_country_1 = 0 GROUP BY article_id ORDER BY w DESC LIMIT 0, 15 OPTION max_matches = 200

方法2：使用Sphinx分组过滤功能（无需修改索引） #

利用Sphinx的GROUPBY ... HAVING语法，结合聚合函数过滤：

SELECT article_id, WEIGHT() w, SUM(IF(country_id=1,1,0)) as cnt_c1 
FROM articles 
WHERE MATCH('(@name nasicon)') 
GROUP BY article_id 
HAVING cnt_c1 = 0 
ORDER BY w DESC 
LIMIT 0, 15 OPTION max_matches = 200

通过SUM(IF(country_id=1,1,0))统计每个article_id下country_id=1的出现次数，HAVING子句仅保留次数为0的分组，即可得到完全不含country_id=1的article_id。

内容的提问来源于stack exchange，提问作者Andrey Zagoruyko