如何在Room多表关联查询中处理可空关联字段的空值校验问题

问题原因 #

你当前使用的是JOIN（内连接）关联from_word_map2、to_word_map2对应的word表，内连接要求关联的两个表必须存在匹配的键值才会返回记录。当from_word_map2或to_word_map2为null时，没有匹配的word表记录，整条数据都会被过滤，所以只有两个可空参数都不为空时才能查到数据。

解决方案 #

将两个可空外键对应的内连接改为LEFT JOIN（左连接）即可：左连接会保留左表word_translate_map的所有记录，即使右表word没有匹配的记录，对应的查询字段会自动返回null，完全符合你的需求。

修改后的查询代码 #

@Query(
        "SELECT " +
                "f.word AS from_word, " +
                "f.word_id as from_word_id, " +
                "f2.word AS from_word2, " +
                "f2.word_id as from_word_id2, " +
                "fl.language AS from_language, " +
                "fl.language_id AS from_language_id, " +
                "t.word AS to_word, " +
                "t.word_id AS to_word_id, " +
                "t2.word AS to_word2, " +
                "t2.word_id as to_word_id2, " +
                "tl.language AS to_language, " +
                "tl.language_id AS to_language_id, " +
                "time, " +
                "date, " +
                "day_of_week AS dayOfWeek, " +
                "country, " +
                "city, " +
                "bookmarked " +
                "FROM word_translate_map " +
                "JOIN word AS f ON from_word_map = f.word_id " +
                "JOIN word AS t ON to_word_map = t.word_id " +
                // 可空外键改用LEFT JOIN
                "LEFT JOIN word AS f2 ON from_word_map2 = f2.word_id " +
                "LEFT JOIN word AS t2 ON to_word_map2 = t2.word_id " +
                "JOIN language AS fl ON f.language_map = fl.language_id " +
                "JOIN language AS tl ON t.language_map = tl.language_id "
    )
abstract fun getAllTranslatedWords(): LiveData<List<TranslatedWord>>

验证说明 #

修改后，from_word_map2为null时返回的from_word2自动为null，to_word_map2为null时返回的to_word2自动为null，两个字段都不为空时返回对应匹配的word值，和你定义的TranslatedWord类的可空字段完全兼容，不需要额外做空值处理。

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