如何在Room中处理嵌套关系以获取关联车型的车辆品牌列表

现有关联关系修正 #

你当前定义的ApplicationWithBrandAndModel关联字段存在错误：ApplicationItem实体中没有userId字段，仅定义了主键id，需要先确保BrandOfVehicleItem实体中存在关联ApplicationItem主键的外键字段（示例假设外键名为applicationId），修正后的关联代码如下：

@Entity(tableName = "application_table", indices = [Index(value = ["name"], unique = true)])
data class ApplicationItem(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id") val id: Int? = 0,
    @ColumnInfo(name = "name") val name: String
)

data class ApplicationWithBrandAndModel(
    @Embedded val application: ApplicationItem,
    @Relation(
        entity = BrandOfVehicleItem::class,
        parentColumn = "id", // 对应ApplicationItem的主键字段名
        entityColumn = "applicationId" // 对应BrandOfVehicleItem里关联Application的外键字段名
    )
    val brands: List<BrandWithModel>
)

data class BrandWithModel(
    @Embedded val brand: BrandOfVehicleItem,
    @Relation(
        parentColumn = "brandId", // 对应BrandOfVehicleItem的主键字段名，原代码的path字段无关联意义，需修正
        entityColumn = "brandCreatorId" // 对应ModelOfVehicleItem里关联Brand的外键字段名
    )
    val models: List<ModelOfVehicleItem>
)

查询关联了车型的品牌列表实现 #

方案1：嵌套查询（同时返回Application关联数据）

在DAO层定义查询方法获取ApplicationWithBrandAndModel后，过滤掉models为空的品牌即可：

@Dao
interface VehicleDao {
    // 查询指定id的Application及关联的所有品牌、车型
    @Transaction
    @Query("SELECT * FROM application_table WHERE id = :applicationId")
    suspend fun getApplicationWithBrands(applicationId: Int): ApplicationWithBrandAndModel

    // 外部调用过滤得到有车型的品牌
    suspend fun getBrandsWithModels(applicationId: Int): List<BrandOfVehicleItem> {
        val data = getApplicationWithBrands(applicationId)
        return data.brands.filter { it.models.isNotEmpty() }.map { it.brand }
    }
}

方案2：直接查询纯品牌列表

如果不需要嵌套Application数据，直接写关联查询过滤即可：

@Query("""
    SELECT DISTINCT b.* FROM brand_of_vehicle_table b
    INNER JOIN model_of_vehicle_table m ON b.brandId = m.brandCreatorId
    WHERE b.applicationId = :applicationId
""")
suspend fun getBrandsWithModelsOnly(applicationId: Int): List<BrandOfVehicleItem>

内容的提问来源于stack exchange，提问作者ms.dev