Android Room单查询实现多表模糊搜索的方案咨询

问题1：能否通过SQLite查询搭配自定义POJO实现该需求？ #

可以实现，核心是借助SQLite的UNION ALL运算符拼接三张表的查询结果，只需先定义一个统一结构的返回POJO即可：

1. 首先定义通用结果类，区分不同实体类型

enum class SearchResultType {
    PET, OWNER, HOUSEHOLD
}

data class SearchResult(
    val type: SearchResultType,
    val id: String,
    val name: String,
    // 其他需要返回的公共字段可按需补充
)

2. 在Dao层编写联合查询语句

@Query("""
    SELECT :petType AS type, id, name FROM Pet WHERE name LIKE '%' || :keyword || '%'
    UNION ALL
    SELECT :ownerType AS type, id, name FROM Owner WHERE name LIKE '%' || :keyword || '%'
    UNION ALL
    SELECT :householdType AS type, id, name FROM Household WHERE name LIKE '%' || :keyword || '%'
    ORDER BY name
""")
fun searchByName(
    keyword: String, 
    petType: Int = SearchResultType.PET.ordinal, 
    ownerType: Int = SearchResultType.OWNER.ordinal, 
    householdType: Int = SearchResultType.HOUSEHOLD.ordinal
): Flow<List<SearchResult>>

如果需要返回更多实体字段，只要保证三个子查询返回的字段数量、类型完全对应即可，不需要的字段可以填NULL占位。

问题2：是否需要分别对三张表执行查询，之后在Repository/UseCase层借助Flow合并结果？ #

两种方案都可落地，没有强制要求，可以根据业务场景选择：

• 优先选SQL UNION方案的场景：
  • 数据量较大，追求更高查询性能（单次数据库IO开销远低于多次IO）
  • 需要对合并后的整体结果做统一排序、分页等操作
  • 不同实体需要返回的字段差异很小
• 优先选分表查询+Flow合并方案的场景：
  • 不同实体的字段差异极大，强行用UNION会导致POJO存在大量无用空字段
  • 后续可能频繁扩展搜索数据源（比如后续要加商品、订单等表一起搜索），分开维护查询逻辑更灵活
  • 部分表的查询存在特殊过滤逻辑，写在SQL中会导致语句过于复杂难以维护

分表查询+Flow合并的示例代码如下：

// Dao层定义三个独立查询
@Query("SELECT * FROM Pet WHERE name LIKE '%' || :keyword || '%'")
fun searchPets(keyword: String): Flow<List<Pet>>

@Query("SELECT * FROM Owner WHERE name LIKE '%' || :keyword || '%'")
fun searchOwners(keyword: String): Flow<List<Owner>>

@Query("SELECT * FROM Household WHERE name LIKE '%' || :keyword || '%'")
fun searchHouseholds(keyword: String): Flow<List<Household>>

// Repository层合并结果
fun searchAll(keyword: String): Flow<List<SearchResult>> {
    return combine(
        searchPets(keyword),
        searchOwners(keyword),
        searchHouseholds(keyword)
    ) { pets, owners, households ->
        buildList {
            addAll(pets.map { SearchResult(SearchResultType.PET, it.id, it.name) })
            addAll(owners.map { SearchResult(SearchResultType.OWNER, it.id, it.name) })
            addAll(households.map { SearchResult(SearchResultType.HOUSEHOLD, it.id, it.name) })
        }.sortedBy { it.name }
    }
}

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