基于RoomDB实现通用黑名单规则的Feed过滤方案问询

问题背景 #

正在构建支持用户通过排除规则定制Feed内容的系统，涉及表结构如下：

涉及表结构 #

Feed表提供需从Post表获取的ID列表，渲染Post时会附加其所有Tag。

早期黑名单标签过滤实现 #

早期仅支持Tag黑名单过滤，通过关联查询实现：

@Query("
SELECT DISTINCT Feed.postId FROM Feed
LEFT JOIN (
    SELECT DISTINCT Tag.parentPostId as postId FROM Tag
    INNER JOIN BlacklistedTag ON Tag.contents = BlacklistedTag.contents
    INNER JOIN Feed ON Tag.parentPostId = Feed.postId
    WHERE Feed.feedId = :feedId
    ORDER BY datetime(Feed.date) DESC
) AS BlockedIds ON Feed.postId = BlockedIds.postId
WHERE Feed.feedId = :feedId
AND BlockedIds.postId IS NULL
ORDER BY datetime(date) DESC
LIMIT :pageSize
OFFSET :offset
")
fun getFilteredPostIdsByPage(
    pageSize : Int = 48,
    offset : Int = 0,
    feedId : Int = ContentFeedIds.Home
) : List<Long>

需求升级：通用多规则过滤 #

现在需要让BlacklistRule支持多表多字段的灵活过滤，比如：

• 过滤contents为FOO的Tag对应的Post
• 过滤title包含BAR的Post

核心需求是将所有规则匹配到的postId收集为黑名单集合，再用该集合过滤Feed内容。

解决方案 #

思路1：代码层动态生成SQL（需防注入） #

通过代码遍历所有规则，拼接子查询并合并结果，再执行最终过滤查询。这种方式可控性强，适配Room的参数绑定机制。

实现步骤

1. 查询所有BlacklistRule记录
2. 遍历每条规则，根据表名和字段名生成对应的子查询，必须用参数占位符避免SQL注入
3. 用UNION ALL合并所有子查询，得到完整的黑名单ID集合
4. 关联Feed表过滤出不在黑名单内的Post ID

示例代码：

// 业务层实现逻辑
fun getFilteredPostIdsWithRules(feedId: Int, pageSize: Int, offset: Int): List<Long> {
    val rules = blacklistRuleDao.getAllRules()
    if (rules.isEmpty()) {
        // 无规则时直接返回原始Feed数据
        return feedDao.getOriginalPostIdsByPage(pageSize, offset, feedId)
    }

    val subqueries = mutableListOf<String>()
    val args = mutableListOf<String>()
    rules.forEach { rule ->
        when (rule.tableName) {
            "Tag" -> {
                subqueries.add("SELECT DISTINCT parentPostId AS postId FROM Tag WHERE ${rule.fieldName} LIKE ?")
                args.add("%${rule.contents}%")
            }
            "Post" -> {
                subqueries.add("SELECT DISTINCT id AS postId FROM Post WHERE ${rule.fieldName} LIKE ?")
                args.add("%${rule.contents}%")
            }
            // 可扩展其他表的规则处理逻辑
        }
    }

    val blockedIdsQuery = subqueries.joinToString(" UNION ALL ")
    val finalQuery = """
        SELECT DISTINCT Feed.postId FROM Feed
        LEFT JOIN ($blockedIdsQuery) AS BlockedIds ON Feed.postId = BlockedIds.postId
        WHERE Feed.feedId = ?
        AND BlockedIds.postId IS NULL
        ORDER BY datetime(date) DESC
        LIMIT ? OFFSET ?
    """.trimIndent()

    // 使用Room的RawQuery执行动态拼接的SQL
    val query = SimpleSQLiteQuery(
        finalQuery,
        args.toTypedArray() + arrayOf(feedId.toString(), pageSize.toString(), offset.toString())
    )
    return feedDao.getFilteredPostIdsRaw(query)
}

// 对应的Dao方法定义
@RawQuery
fun getFilteredPostIdsRaw(query: SupportSQLiteQuery): List<Long>

// 无规则时的基础查询Dao方法
@Query("
SELECT postId FROM Feed
WHERE feedId = :feedId
ORDER BY datetime(date) DESC
LIMIT :pageSize OFFSET :offset
")
fun getOriginalPostIdsByPage(pageSize: Int, offset: Int, feedId: Int): List<Long>

思路2：纯SQL层实现（可选） #

如果希望完全在SQL层处理，可以利用SQLite的递归CTE或动态SQL生成，但Room对纯动态SQL支持有限，不如代码层拼接灵活可控，仅适合规则固定的场景。

优化建议

• 严格防SQL注入：禁止直接将规则内容拼接到SQL字符串，必须使用Room的参数占位符绑定
• 性能优化：
  • 对Tag.contents、Post.title等常用过滤字段添加索引，提升子查询速度
  • 可缓存黑名单ID集合，定期更新，减少重复查询开销
• 规则校验：添加BlacklistRule时校验tableName和fieldName的合法性，避免无效规则导致查询错误

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