Room - Incorrect result from query in 1-N relation when not filtering by id

Question

I have the following structure to define meetings with friends. First i have my Friend entity

@Entity(tableName = Friend.TABLE_NAME)
data class Friend(
    @PrimaryKey(autoGenerate = true)
    val id: Int? = null,
    val alias: String? = null
) {
    companion object {
        const val TABLE_NAME = "friends"
    }
}

Also have my Meeting entity

@Entity(tableName = Meeting.TABLE_NAME)
data class Meeting(
    @PrimaryKey(autoGenerate = true)
    val id: Int?,
    val friendId: Int?,
    val date: LocalDateTime?
) {
    companion object {
        const val TABLE_NAME = "meetings"
    }
}

and then a helper class to relate them:

data class FriendMeetings(
    @Embedded
    val friend: Friend= Friend(),
    @Relation(
        parentColumn = "id",
        entityColumn = "friendId"
    )
    val meetings: List<Meeting>
)

Also defined a type converter for the LocalDateTime field

class AppConverters {
    @TypeConverter
    fun fromLocalDateTime(date: LocalDateTime?): Long? =
        date?.toMillis()

    @TypeConverter
    fun toLocalDateTime(millisSinceEpoch: Long?): LocalDateTime? =
        if (millisSinceEpoch == null) {
            null
        } else LocalDateTime.ofInstant(
            Instant.ofEpochMilli(millisSinceEpoch),
            ZoneId.systemDefault()
        )
}

When i ask in the dao for the list of all friends it works charmly

@Query("SELECT * FROM friends ORDER BY id")
fun getAll(): List<FriendMeetings>

If i ask in the dao for the meetings with a friend in an given date it works charmly

@Query("SELECT * FROM friends INNER JOIN meetings ON friends.id = meetings.friendId WHERE friends.id = :id AND meetings.date BETWEEN :startOfDay AND :endOfDay")
fun getMeetingsWithFriendBetweenDates(id: Long, startOfDay: Long, endOfDay: Long): FriendMeetings?

but if i try to get all meetings in a given date the results are not correct

@Query("SELECT * FROM friends INNER JOIN meetings ON friends.id = meetings.friendId WHERE meetings.date BETWEEN :startOfDay AND :endOfDay")
fun getMeetingsBetweenDates(startOfDay: Long, endOfDay: Long): List<FriendMeetings>?

In this case i get the correct number of friends but the friend data is repeated all over while the meetings are correct. I just thought that, as the meetings in a day wouldnt be many, i could just load the meetings and then for each one load the friend but seems a little bit nasty but i cant figure out whats wrong here

Answer 1

When an @Relation is acted upon by Room, it will return ALL children of the respective parent and in an arbitrary order . That is the @Query is only used to ascertain the parents, the underlying @Relations are built by an underlying query that takes no parameters.

What you need to do is bypass/override Room's convenience handling. This can be achieved by using a function with a body that controls both factors:-

1. the retrieval of the parents, and
2. the retrieval of the children

So I believe that your solution for ALL Friends but with Meetings BETWEEN a provided start and end date could be to have the following functions in an @Dao interface or abstract class:-

@Query("SELECT * FROM friends ORDER BY id")
fun getAllFriends(): List<Friend>
@Query("SELECT * FROM meetings WHERE meetings.friendId=:friendId AND meetings.date BETWEEN :startOfDay: Long AND :endOfDay")
fun getMeetingsForAFriendBetweenDates(friendId: Int?, startOfDay: Long, endOfDay: Long): List<Meeting>

@Transaction
@Query("")
fun getFriendWithMeetingsBetweenDates(id: Long, startOfDay: Long, endOfDay: Long): List<FriendMeetings> {
    val rv = ArrayList<FriendMeetings>()
    for(f in getAllFriends()) {
        rv.add(FriendMeetings(f,getMeetingsForAFriendBetweenDates(f.id,startOfDay,endOfDay)))
    }
    return rv
}

• The @Query("") is to get Room to apply the @Transaction to the function

You could utilise your existing getAll function and just lose/discard the meetings (or programmatically extract the required meetings (noting that ALL meetings per a parent would exist)). e.g.

@Transaction
@Query("")
fun getFriendWithMeetingsBetweenDatesV2(id: Long,startOfDay: Long,endOfDay: Long): List<FriendMeetings> {
    val rv = ArrayList<FriendMeetings>()
    for (f in getAll()) {
        rv.add(FriendMeetings(f.friend,getMeetingsForAFriendBetweenDates(f.friend.id,startOfDay,endOfDay)))
    }
    return rv
}

• i.e. you would still need to bypass/override the convenience handling and have the second query and the extract function
• this method is less efficient as you are effectively retrieving the children twice. Hence, the first method is recommended.