Data class with 2 entites confuses columns

Question

I have two entities in my room database in an Android application. The concept is that every Site has many Groups.

@Entity(tableName = "sites")
data class Site(

    @ColumnInfo(name = "server_id")
    var serverId: Long,

    @PrimaryKey
    @ColumnInfo(name = "site_id")
    val siteId: Int,

    @ColumnInfo(name = "description", defaultValue = "")
    val description: String

)


@Entity(tableName = "groups")
data class Group(

    @ColumnInfo(name = "server_id")
    val serverId: Long,

    @PrimaryKey
    @ColumnInfo(name = "group_id")
    var groupId: Int,

    @ColumnInfo(name = "site_id")
    val siteId: Int,

    @ColumnInfo(name = "description", defaultValue = "")
    val description: String

)

So i want given a siteId and a groupId, two fetch from the Database a POJO called SiteWithGroup which is

data class SiteWithGroup(
    @Embedded
    val site: Site,

    @Relation(parentColumn = "site_id", entityColumn = "site_id", entity = Group::class)
    val group: Group
)

I created a MediatorLiveData which observes the siteId, groupId and when it has both it returns a query from db

private val siteGroupPair = MediatorLiveData<Pair<Int?, Int?>>().apply {
        addSource(siteId) {
            value = Pair(it, groupId.value)
        }
        addSource(groupId) {
            value = Pair(siteId.value, it)
        }
    }

    override val siteGroup: LiveData<SiteWithGroup>
        get() = Transformations.switchMap(siteGroupPair) {
            it?.let { pair ->
                if (pair.first != null && pair.second != null)
                    sitesRepository.siteWithGroup(pair.first!!, pair.second!!)
                else null
            }
        }



//Dao query
@Query("""Select * from groups
        inner join sites on groups.site_id = :siteId
        where sites.site_id = :siteId and groups.group_id = :groupId
    """)
    fun getByIdWithGroups(siteId: Int, groupId: Int): LiveData<SiteWithGroup>

If we suppose that the siteId, groupId = 1, 1 and i have the following entries in my db

Group entity
serverId, groupId, siteId, description
1         1        1       "Group1 description"
1         2        1       "Group2 description"

________________________________________________

Site entity
serverId, siteId, description
1         1       "Site1 description"

we expect from out result to be

SiteWithGroup(
   site=Site(serverId=1, siteId=1, description=Site1 description), 
   group=Group(serverId=1, groupId=2, siteId=1, description=Group2 description)
)

but instead we get this

SiteWithGroup(
   site=Site(serverId=1, siteId=1, description= !!!<<Group1 description>>!!!), 
   group=Group(serverId=1, groupId=<<2>>, siteId=1, description= <<Group2 description>>)
)

The site entity get the groups description and the wrong one!! Seems like the group contraint is not applied at all.How is this happening?? How can i fix it?? Thank you

However on my UI the result is

score 1 · Accepted Answer · answered Nov 22 '20 at 13:07

You are trying to mix join with @Relation, that causes unpredictable result.

Just keep in mind that with @Relation Room on the first step applies your given query to get data from the main entity Site (select * from sites ...), and on the second - launches one more query to attach second entity's Group data (roughly select * from groups where siteId IN <List of siteIds gotten after step1>). So in this second query there is no any condition on group_id anymore (and you have no option to set this condition there since query is autogenerated) and there could be any group picked up (any of two in your test case).

I guess to fix that you can replace entities Site and Group in your data class:

data class SiteWithGroup(
    @Embedded
    val group: Group,

    @Relation(parentColumn = "site_id", entityColumn = "site_id")
    val site: Site
)

and dao query shouldn't include any Join (it could but it would be really useless):

@Transaction
@Query("Select * from groups where site_id = :siteId and group_id = :groupId")
fun getByIdWithGroups(siteId: Int, groupId: Int): LiveData<SiteWithGroup>

That is correct. Thank you. So how can i learn what exactly is goin on underthe hood with Room annotations..? — james04, Nov 22 '20 at 14:24
My learning way was to analyse Java code Room generates for dao interface implementation. So Room's annotation processor looks through annotations, generates Java class with the same name as dao interface but with suffix '_Impl (at generated Java folder)'. You can find there implementation for each dao method and to understand Room's logic — sergiy tikhonov, Nov 22 '20 at 14:48

Data class with 2 entites confuses columns

1 Answers1