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
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:-
So I believe that your solution for ALL Friend
s but with Meeting
s 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
}
@Query("")
is to get Room to apply the @Transaction
to the functionYou 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
}