如何通过Room/SQL查询构建复合数据类FavoriteRoute

1. 调整复合数据类FavoriteRoute #

由于要映射两次关联Airport表的查询结果，需要给每个Airport字段添加@Embedded并指定前缀，让Room能区分两个Airport的列：

data class FavoriteRoute(
    @Embedded(prefix = "departure_") val departure: Airport,
    @Embedded(prefix = "destination_") val destination: Airport
)

2. 在Dao中编写@Query查询语句 #

通过两次JOIN关联Airport表，分别匹配出发地和目的地的IATA代码，同时给查询结果的列加上对应前缀，和FavoriteRoute中的@Embedded前缀对应：

按Favorite ID查询 #

@Dao
interface FavoriteDao {
    @Query("""
        SELECT 
            a1.id AS departure_id,
            a1.iata_code AS departure_iata_code,
            a1.name AS departure_name,
            a1.passengers AS departure_passengers,
            a2.id AS destination_id,
            a2.iata_code AS destination_iata_code,
            a2.name AS destination_name,
            a2.passengers AS destination_passengers
        FROM favorite f
        JOIN airport a1 ON f.departure_iata_code = a1.iata_code
        JOIN airport a2 ON f.destination_iata_code = a2.iata_code
        WHERE f.id = :favoriteId
    """)
    suspend fun getFavoriteRouteById(favoriteId: Int): FavoriteRoute
}

直接通过IATA代码查询（适配传入Favorite对象的场景） #

如果需要直接用传入的Favorite对象的code查询，可以使用这个方法：

@Query("""
    SELECT 
        a1.id AS departure_id,
        a1.iata_code AS departure_iata_code,
        a1.name AS departure_name,
        a1.passengers AS departure_passengers,
        a2.id AS destination_id,
        a2.iata_code AS destination_iata_code,
        a2.name AS destination_name,
        a2.passengers AS destination_passengers
    FROM airport a1, airport a2
    WHERE a1.iata_code = :departureCode 
      AND a2.iata_code = :destinationCode
""")
suspend fun getFavoriteRouteByCodes(departureCode: String, destinationCode: String): FavoriteRoute

3. 实现转换函数 #

基于Dao方法，实现接收Favorite参数的转换逻辑：

// 假设已通过依赖注入获取favoriteDao实例
suspend fun convertFavoriteToRoute(favorite: Favorite): FavoriteRoute {
    return favoriteDao.getFavoriteRouteByCodes(favorite.departureCode, favorite.destinationCode)
}

4. 处理Flow数据流 #

如果需要监听数据变化并返回Flow，只需修改Dao方法的返回类型：

@Query("""
    SELECT 
        a1.id AS departure_id,
        a1.iata_code AS departure_iata_code,
        a1.name AS departure_name,
        a1.passengers AS departure_passengers,
        a2.id AS destination_id,
        a2.iata_code AS destination_iata_code,
        a2.name AS destination_name,
        a2.passengers AS destination_passengers
    FROM favorite f
    JOIN airport a1 ON f.departure_iata_code = a1.iata_code
    JOIN airport a2 ON f.destination_iata_code = a2.iata_code
    WHERE f.id = :favoriteId
""")
fun getFavoriteRouteByIdAsFlow(favoriteId: Int): Flow<FavoriteRoute>

关于@Relation的问题说明 #

你之前用@Relation遇到问题，是因为@Relation默认只能基于单一外键关联同一张表，无法直接给关联表起别名来区分两次关联的场景。而直接用@Query写JOIN语句可以灵活处理这种多次关联同一张表的需求。

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