I have a database in my application that has 3 tables Server, Site, Group
@Entity(tableName = "servers")
data class Server(
@ColumnInfo(name = "server_id")
var serverId: Long,
@ColumnInfo(name = "account_id")
var accountId: Int,
@ColumnInfo(name = "device_id")
var deviceId: Int,
@ColumnInfo(name = "company_id")
var companyId: Int
.......
@Entity(tableName = "sites")
data class Site(
@ColumnInfo(name = "server_id")
var serverId: Long,
@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,
@ColumnInfo(name = "group_id")
var groupId: Int,
@ColumnInfo(name = "site_id")
val siteId: Int,
@ColumnInfo(name = "description", defaultValue = "")
val description: String
......
So as we can see for every server we have sites and each site has several groups. Now i have made a POJO called ServerSiteWithGroup which contains a Server and another POJO SiteWithGroup
data class ServerSiteWithGroup(
@Embedded
val server: Server,
@Relation(parentColumn = "server_id", entityColumn = "server_id")
val siteWithGroup: SiteWithGroup
)
data class SiteWithGroup(
@Embedded
val group: Group,
@Relation(parentColumn = "site_id", entityColumn = "site_id")
val site: Site
)
So given all that i want to make one query to the Room Database and get the Server, Site, Group objects given the serverId, siteId, groupId
I have tried this but it does not work
@Transaction
@Query("Select * from groups
inner join servers on groups.server_id = servers.server_id
where groups.server_id = :serverId
and groups.site_id = :siteId
and groups.group_id = :groupId")
fun getSiteWithGroup(serverId: Long, siteId: Int, groupId: Int): LiveData<ServerSiteWithGroup>
How can i solve that problem?
First you don't appear to have defined any PRIMARY keys using the @PrimaryKey annotation and this would result in a compilation error.
Second the @Query should not return a
LiveData<ServerSiteWithGroup>BUT should return an array of LiveData's that themselves are (I believe) arrays of ServerSiteWithGroup so I believe should instead beLiveData<List<ServerSiteWithGroup>>.LiveData)In the Group entity you have the ServerId, this is unecessary as the Site that is a parent of the Group has the Server as it's parent. This isn't an error but is unnecessary.
Although not required in theory, an id can be Long, as such I'd always recommend using a Long rather than an Int for relationships.
Example Here's a working example based upon your code.
Server
Primarykeyhas been defined.autogenerateandLong?allows id's to be generated.Site
Group
Alldao Dao's to allow insertion and extraction at various levels and notably using
@relationandJOIN's.@Query's before your original query similar BUT a subtle difference (see Result)SiteWithGroup POJO relationship for group with it's parent site (a site can have many groups)
ServerWithSiteWithGroup
MyDatabse the @Database abstract class that ties the Entities and Dao(s) (for convenience just the one)
MainActivity for demo/bervity/convenience runs on main thread
Results The log when run initially
:-
Extra You may wish to consider the following which uses what appear to be the correct relationships :-
This actually achieves the same result for the same test case anyway :-
Even More
It might be that you are NOT expecting the results but instead are expecting a single Server/Site/Group when specifiying all three arguments. If that is so then I believe that using
@Relationis not the way to go. Rather that a POJO without the relation is the way to go.Consider the SQL in the later queries for example :-
With the above data (note
sites.description AS site_description, groups.description AS group_descriptionare to disambiguate/distinguish the same column names from separate tables).You might well expect (when args are 1,1,1) :-
As logs above that is NOT what Room serves out.
However consider the POJO :-
Serveras well).Then consider the Dao :-
AltServerSiteGroupinstead ofList<AltServerSiteGroup>could be used if only 1 value being returned is possible (would be the case if ID's were primary keys).So by adding the following to MainActivity :-
Then the Result from the section would be :-
i.e. the single Server/Site/group that matches the selection criteria.
You could add functions to AltServerSiteGroup to return the extracted Server/Site/Group objects but note that these would differ from the complete/full objects as they would only have the single Server-Site-Group e.g. :-
Explanation
In short Room will build the complete objects according to the
@Relationand thus add the extra unwanted sites and groups.If you check out the code for the Dao's in the Java(generated) (using the Android View in the project window), noting that the file name is suffixed with _Impl (so for
AllDao, as used above, in the generated java it isAllDao_Impl), you will see what room does and that the code built forgetAltis a lot shorter than the code forgetSiteWithGroup.