CODEWITHSUNDEEP
androidsqlkotlinandroid-room
Thomas Cook
Thomas Cook

Reputation: 4853

RoomDB SQL Query / data modelling problem

I'm using RoomDB to store entities in my app, so far I've only needed relatively trivial queries such as SELECT * FROM table and DELETE * FROM table as well as slightly more complex things such as a join of 2 tables to return composite models, like SELECT * from tableA INNER JOIN ON tableB tableA.tableBId = tableB.tableBId.

So far, so good. Now, I have something I've not dealt with before, and I'm not sure whether I'm A) modelling my data wrong (so no query will be possible to create these models) or B) I don't know parts of SQL that are required to make this work.

I'm far from a SQL expert, but have used it on and off for a few years.

Below are the simplest example of the models (marked with @Entity such that RoomDB tables are generated) of what I'm trying to achieve:

data class FooPopulated(
    val fooId: Long,
    @Embedded
    val barOne: Bar,
    @Embedded
    val barTwo: Bar,
    @Embedded
    val barThree: Bar
)

@Entity(primaryKeys = ["fooId"])
data class FooUnpopulated(
    val fooId: Long,
    val barOneId: Long,
    val barTwoId: Long,
    val barThreeId: Long
)

@Entity(primaryKeys = ["barId"])
data class Bar(
    val barId: Long,
    val name: String
)

So there will be 2 tables; Bar and FooUnpopulated and I want to run a query that will "join" the FooUnpopulated table entries with the Bar table entries, in such a way that I get back a collection of FooPopulated.

I am stumped, can anyone advise? Is my FooPopulated model just "wrong" in that it doesn't define a valid relationship which can be satisfied by a SQL query, or, am I missing that magic SQL keywords/understanding to make this happen?

Upvotes: 0

Views: 35

Answers (1)

Ravi Kumar
Ravi Kumar

Reputation: 4508

You can use one to one relation and can define FooPopulated like:

data class FooPopulated{
 @Embeded
 val fooUnpopulated: FooUnpopulated,
 @Relation(parentColumn = "barOneId", childColumn = "barId")
 val barOne: Bar,
 @Relation( parentColumn = "barTwoId", childColumn = "barId")
 val barTwo: Bar,
 @Relation( parentColumn = "barThreeId", childColumn = "barId")
 val barThree: Bar
}

Now you can define a function in your DAO to get this object:

@Transaction
@Query("YOUR QUERY HERE")
fun getFooPopulated(): List<FooPopulated>

More details here: https://developer.android.com/training/data-storage/room/relationships#one-to-one

Upvotes: 1

Related Questions