Select 3 entities at once from Room Database with @Relation

Question

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?

MikeT · Accepted Answer · 2021-03-28T19:27:05.083

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 be LiveData<List<ServerSiteWithGroup>>.

Perahps try this First BUT consider the Even More section that has been added at the end of the answer. .

How can i solve that problem?

You don't mention/specify what the actual problem is. However, below is a solution based upon the information you have provided. (but for conveience not utilsing 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

@Entity(tableName = "servers")
data class Server(

    @PrimaryKey(autoGenerate = true)
    @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
)

Primarykey has been defined.
autogenerate and Long? allows id's to be generated.

Site

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

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "site_id")
    val siteId: Long?,

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

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

Group

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

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "group_id")
    var groupId: Long?,

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

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

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

Alldao Dao's to allow insertion and extraction at various levels and notably using @relation and JOIN's.

interface AllDao {
    @Insert
    fun insertServer(server: Server): Long
    @Insert
    fun insertSite(site: Site): Long
    @Insert
    fun insertGroup(group: Group): Long
    @Query("SELECT * FROM servers")
    fun getAllServers(): List<Server>
    @Query("SELECT * FROM sites")
    fun getAllSites(): List<Site>
    @Query("SELECT * FROM groups")
    fun getAllGroups(): List<Group>
    @Transaction
    @Query("SELECT * FROM sites")
    fun getAllSitesWithGroups(): List<SiteWithGroup>
    @Transaction
    @Query("SELECT * FROM servers")
    fun getAllServersWithSitesWithGroups(): List<ServerWithSiteWithGroup>
    @Transaction
    @Query("SELECT * FROM servers INNER JOIN sites ON servers.server_id = sites.server_id INNER JOIN groups on sites.site_id = groups.site_id")
    fun getAllServersWithSitesWithGroupsUsingJoin(): List<ServerWithSiteWithGroup>

    @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): List<ServerWithSiteWithGroup>
}

Note the last two @Query's before your original query similar BUT a subtle difference (see Result)
Note you query just returns List rather than LiveData<List>

SiteWithGroup POJO relationship for group with it's parent site (a site can have many groups)

data class SiteWithGroup(
    @Embedded
    var site: Site,

    @Relation(entity = Group::class, entityColumn = "site_id", parentColumn = "site_id")
    var groups: List<Group>
)

Although not necessary in this case I prefer to code the entity (class)

ServerWithSiteWithGroup

data class ServerWithSiteWithGroup(
    @Embedded
    var server: Server,

    @Relation(entity = Site::class, entityColumn = "server_id",parentColumn = "server_id")
    var siteWithGroup: List<SiteWithGroup>
)

Note I've used a different name to the original as I believe that it's more descriptive of the underlying POJO.

MyDatabse the @Database abstract class that ties the Entities and Dao(s) (for convenience just the one)

@Database(entities = arrayOf(Server::class,Site::class,Group::class),version = 1)
abstract class MyDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao
}

MainActivity for demo/bervity/convenience runs on main thread

class MainActivity : AppCompatActivity() {
    val TAG = "MYDBINFO"
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        val db = Room.databaseBuilder(applicationContext,MyDatabase::class.java,"MyDatabase")
            .allowMainThreadQueries()
            .build()
        val allDao = db.getAllDao()
        val s1 = Server(null,1,2,3)
        val s2 = Server(null,4,5,6)
        allDao.insertServer(s1)
        allDao.insertServer(s2)
        val site1 = Site(null,1,"Site1 - Server1")
        val site2 = Site(null,1,"Site2 - Server1")
        val site3 = Site(null,2,"Site3 - Server2")
        val site4 = Site(null,2,"Site4 - Server2")
        val site5 = Site(null,1,"Site5 - Server 1")
        allDao.insertSite(site1)
        allDao.insertSite(site2)
        allDao.insertSite(site3)
        allDao.insertSite(site4)
        allDao.insertSite(site5)
        val g1 = Group(null,1,1,"Group1 - Site1 (impicitly Server 1)")
        val g2 = Group(null,10,1,"Group2!!! - Site1 (impicitly Server 1)") // OOOPS no server with ID 10
        val g2_2 = Group(null,1,1,"Group2_2 - Site1 (impicitly Server 1)")
        val g3 = Group(null,2,2,"Group3 - Site2 (impicitly Server 1)")
        allDao.insertGroup(g1)
        allDao.insertGroup(g2)
        allDao.insertGroup(g2_2)
        allDao.insertGroup(g3)
        val servers = allDao.getAllServers()
        Log.d(TAG,"Server List")
        for (s: Server in servers) {
            Log.d(TAG,"\tServerID = " + s.serverId + " CompanyID =" + s.companyId + " AccountID = " + s.accountId + " DeviceID = " + s.deviceId)
        }
        val sites = allDao.getAllSites()
        Log.d(TAG,"Site List")
        for (si: Site in sites) {
            Log.d(TAG,"\tSiteID = " + si.siteId + " Description = " + si.description + " Server ID = " + si.serverId)
        }
        val groups = allDao.getAllGroups()
        for(g: Group in groups) {
            Log.d(TAG,"\tGroup ID = " + g.groupId + " Description = " + g.description + " ServerID = " + g.serverId + " SiteID = " + g.siteId)
        }
        val sitesWithGroupsList = allDao.getAllSitesWithGroups()
        Log.d(TAG,"Site With Groups List")
        for(swgl: SiteWithGroup in sitesWithGroupsList) {
            Log.d(TAG,"\tSiteID = " + swgl.site.siteId + " ServerID = " + swgl.site.serverId + " Description " + swgl.site.description)
            for(grp: Group in swgl.groups) {
                Log.d(TAG,"\t\tGroup ID = " + grp.groupId + " SiteID = " + grp.siteId + " Description = " + grp.description + " ServerID = " + grp.serverId)
            }
        }
        Log.d(TAG,"****Servers with Sites with Groups using @Relation")
        val swswg = allDao.getAllServersWithSitesWithGroups()
        for(s: ServerWithSiteWithGroup in swswg) {
            Log.d(TAG,"ServerID is " + s.server.serverId)
            Log.d(TAG,"Sites in Server =" + s.siteWithGroup.size)
            for(swg: SiteWithGroup in s.siteWithGroup) {
                Log.d(TAG,"\tSite is " + swg.site.description)
                Log.d(TAG,"\tGroups in Site =" + swg.groups.size)
                for (g: Group in swg.groups) {
                    Log.d(TAG,"\t\tGroup is " + g.description)
                }
            }
        }
        Log.d(TAG,"****Servers with Sites with Groups using Joins")
        val swswg2 = allDao.getAllServersWithSitesWithGroupsUsingJoin()
        for(s: ServerWithSiteWithGroup in swswg2) {
            Log.d(TAG,"ServerID is " + s.server.serverId)
            Log.d(TAG,"Sites in Server =" + s.siteWithGroup.size)
            for(swg: SiteWithGroup in s.siteWithGroup) {
                Log.d(TAG,"\tSite is " + swg.site.description)
                Log.d(TAG,"\tGroups in Site =" + swg.groups.size)
                for (g: Group in swg.groups) {
                    Log.d(TAG,"\t\tGroup is " + g.description)
                }
            }
        }

        // ORIGINAL PROBLEM QUERY 
        Log.d(TAG,"****Servers With Sites With Groups using joins and where clauses (problem query)")
        val swswgsel = allDao.getSiteWithGroup(1,1,1)
        for (swswg3: ServerWithSiteWithGroup in swswgsel) {
            Log.d(TAG,"ServerID is " + swswg3.server.serverId)
            Log.d(TAG,"Sites in Server =" + swswg3.siteWithGroup.size)
            for(swg: SiteWithGroup in swswg3.siteWithGroup) {
                Log.d(TAG,"\tSite is " + swg.site.description)
                Log.d(TAG,"\tGroups in Site =" + swg.groups.size)
                for (g: Group in swg.groups) {
                    Log.d(TAG,"\t\tGroup is " + g.description)
                }
            }
        }
    }
}

The above flows through the code sequentially
Logging allows the results to be monitored easily.
designed to be run once (running more than once may cause some confusion)
for convenience LiveData has been utilised.

Results The log when run initially

Your original query is at the end of the log

:-

03-28 16:56:24.018 D/MYDBINFO: Server List
03-28 16:56:24.018 D/MYDBINFO: ServerID = 1 DeviceID = 2 AccountID = 1 CompanyID = 3
03-28 16:56:24.018 D/MYDBINFO: ServerID = 2 DeviceID = 5 AccountID = 4 CompanyID = 6

03-28 16:56:24.019 D/MYDBINFO: Site List
03-28 16:56:24.019 D/MYDBINFO:  SiteID = 1 Description = Site1 - Server1 ServerID = 1
03-28 16:56:24.019 D/MYDBINFO:  SiteID = 2 Description = Site2 - Server1 ServerID = 1
03-28 16:56:24.019 D/MYDBINFO:  SiteID = 3 Description = Site3 - Server2 ServerID = 2
03-28 16:56:24.019 D/MYDBINFO:  SiteID = 4 Description = Site4 - Server2 ServerID = 2
03-28 16:56:24.019 D/MYDBINFO:  SiteID = 5 Description = Site5 - Server 1 ServerID = 1

03-28 16:56:24.021 D/MYDBINFO: Group List
03-28 16:56:24.022 D/MYDBINFO:      GroupID = 1 Description = Group1 - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 1(May not be correct)
03-28 16:56:24.022 D/MYDBINFO:      GroupID = 2 Description = Group2!!! - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 10(May not be correct)
03-28 16:56:24.022 D/MYDBINFO:      GroupID = 3 Description = Group2_2 - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 1(May not be correct)
03-28 16:56:24.022 D/MYDBINFO:      GroupID = 4 Description = Group3 - Site2 (impicitly Server 1)Parent SiteID = 2 Parent ServerID = 2(May not be correct)

03-28 16:56:24.024 D/MYDBINFO: Site With Groups List
03-28 16:56:24.024 D/MYDBINFO:  SiteID = 1 Description = Site1 - Server1 ServerID = 1
03-28 16:56:24.024 D/MYDBINFO:      GroupID = 1 Description = Group1 - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 1(May not be correct)
03-28 16:56:24.024 D/MYDBINFO:      GroupID = 2 Description = Group2!!! - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 10(May not be correct)
03-28 16:56:24.024 D/MYDBINFO:      GroupID = 3 Description = Group2_2 - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 1(May not be correct)
03-28 16:56:24.024 D/MYDBINFO:  SiteID = 2 Description = Site2 - Server1 ServerID = 1
03-28 16:56:24.024 D/MYDBINFO:      GroupID = 4 Description = Group3 - Site2 (impicitly Server 1)Parent SiteID = 2 Parent ServerID = 2(May not be correct)
03-28 16:56:24.024 D/MYDBINFO:  SiteID = 3 Description = Site3 - Server2 ServerID = 2
03-28 16:56:24.024 D/MYDBINFO:  SiteID = 4 Description = Site4 - Server2 ServerID = 2
03-28 16:56:24.025 D/MYDBINFO:  SiteID = 5 Description = Site5 - Server 1 ServerID = 1

03-28 16:56:24.025 D/MYDBINFO: ****Servers with Sites with Groups using @Relation
03-28 16:56:24.027 D/MYDBINFO: ServerID = 1 DeviceID = 2 AccountID = 1 CompanyID = 3
03-28 16:56:24.028 D/MYDBINFO: Sites in Server =3
03-28 16:56:24.028 D/MYDBINFO:  SiteID = 1 Description = Site1 - Server1 ServerID = 1
03-28 16:56:24.028 D/MYDBINFO:  Groups in Site =3
03-28 16:56:24.028 D/MYDBINFO:      GroupID = 1 Description = Group1 - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 1(May not be correct)
03-28 16:56:24.028 D/MYDBINFO:      GroupID = 2 Description = Group2!!! - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 10(May not be correct)
03-28 16:56:24.028 D/MYDBINFO:      GroupID = 3 Description = Group2_2 - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 1(May not be correct)
03-28 16:56:24.028 D/MYDBINFO:  SiteID = 2 Description = Site2 - Server1 ServerID = 1
03-28 16:56:24.028 D/MYDBINFO:  Groups in Site =1
03-28 16:56:24.028 D/MYDBINFO:      GroupID = 4 Description = Group3 - Site2 (impicitly Server 1)Parent SiteID = 2 Parent ServerID = 2(May not be correct)
03-28 16:56:24.028 D/MYDBINFO:  SiteID = 5 Description = Site5 - Server 1 ServerID = 1
03-28 16:56:24.028 D/MYDBINFO:  Groups in Site =0
03-28 16:56:24.028 D/MYDBINFO: ServerID = 2 DeviceID = 5 AccountID = 4 CompanyID = 6
03-28 16:56:24.028 D/MYDBINFO: Sites in Server =2
03-28 16:56:24.028 D/MYDBINFO:  SiteID = 3 Description = Site3 - Server2 ServerID = 2
03-28 16:56:24.028 D/MYDBINFO:  Groups in Site =0
03-28 16:56:24.028 D/MYDBINFO:  SiteID = 4 Description = Site4 - Server2 ServerID = 2
03-28 16:56:24.028 D/MYDBINFO:  Groups in Site =0

03-28 16:56:24.028 D/MYDBINFO: ****Servers with Sites with Groups using Joins
03-28 16:56:24.029 D/MYDBINFO: ServerID = 1 DeviceID = 2 AccountID = 1 CompanyID = 3
03-28 16:56:24.029 D/MYDBINFO: Sites in Server =3
03-28 16:56:24.029 D/MYDBINFO:  SiteID = 1 Description = Site1 - Server1 ServerID = 1
03-28 16:56:24.029 D/MYDBINFO:  Groups in Site =3
03-28 16:56:24.029 D/MYDBINFO:      GroupID = 1 Description = Group1 - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 1(May not be correct)
03-28 16:56:24.029 D/MYDBINFO:      GroupID = 2 Description = Group2!!! - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 10(May not be correct)
03-28 16:56:24.029 D/MYDBINFO:      GroupID = 3 Description = Group2_2 - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 1(May not be correct)
03-28 16:56:24.029 D/MYDBINFO:  SiteID = 2 Description = Site2 - Server1 ServerID = 1
03-28 16:56:24.029 D/MYDBINFO:  Groups in Site =1
03-28 16:56:24.029 D/MYDBINFO:      GroupID = 4 Description = Group3 - Site2 (impicitly Server 1)Parent SiteID = 2 Parent ServerID = 2(May not be correct)
03-28 16:56:24.029 D/MYDBINFO:  SiteID = 5 Description = Site5 - Server 1 ServerID = 1
03-28 16:56:24.029 D/MYDBINFO:  Groups in Site =0
03-28 16:56:24.029 D/MYDBINFO: ServerID = 10 DeviceID = 2 AccountID = 1 CompanyID = 3
03-28 16:56:24.029 D/MYDBINFO: Sites in Server =0
03-28 16:56:24.029 D/MYDBINFO: ServerID = 1 DeviceID = 2 AccountID = 1 CompanyID = 3
03-28 16:56:24.029 D/MYDBINFO: Sites in Server =3
03-28 16:56:24.030 D/MYDBINFO:  SiteID = 1 Description = Site1 - Server1 ServerID = 1
03-28 16:56:24.030 D/MYDBINFO:  Groups in Site =3
03-28 16:56:24.030 D/MYDBINFO:      GroupID = 1 Description = Group1 - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 1(May not be correct)
03-28 16:56:24.030 D/MYDBINFO:      GroupID = 2 Description = Group2!!! - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 10(May not be correct)
03-28 16:56:24.030 D/MYDBINFO:      GroupID = 3 Description = Group2_2 - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 1(May not be correct)
03-28 16:56:24.030 D/MYDBINFO:  SiteID = 2 Description = Site2 - Server1 ServerID = 1
03-28 16:56:24.030 D/MYDBINFO:  Groups in Site =1
03-28 16:56:24.030 D/MYDBINFO:      GroupID = 4 Description = Group3 - Site2 (impicitly Server 1)Parent SiteID = 2 Parent ServerID = 2(May not be correct)
03-28 16:56:24.030 D/MYDBINFO:  SiteID = 5 Description = Site5 - Server 1 ServerID = 1
03-28 16:56:24.030 D/MYDBINFO:  Groups in Site =0
03-28 16:56:24.030 D/MYDBINFO: ServerID = 2 DeviceID = 2 AccountID = 1 CompanyID = 3
03-28 16:56:24.030 D/MYDBINFO: Sites in Server =2
03-28 16:56:24.030 D/MYDBINFO:  SiteID = 3 Description = Site3 - Server2 ServerID = 2
03-28 16:56:24.030 D/MYDBINFO:  Groups in Site =0
03-28 16:56:24.030 D/MYDBINFO:  SiteID = 4 Description = Site4 - Server2 ServerID = 2
03-28 16:56:24.030 D/MYDBINFO:  Groups in Site =0

03-28 16:56:24.030 D/MYDBINFO: ****Servers With Sites With Groups using joins and where claues (problem query)
03-28 16:56:24.031 D/MYDBINFO: ServerID = 1 DeviceID = 2 AccountID = 1 CompanyID = 3
03-28 16:56:24.031 D/MYDBINFO: Sites in Server =3
03-28 16:56:24.031 D/MYDBINFO:  SiteID = 1 Description = Site1 - Server1 ServerID = 1
03-28 16:56:24.031 D/MYDBINFO:  Groups in Site = 3
03-28 16:56:24.031 D/MYDBINFO:      GroupID = 1 Description = Group1 - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 1(May not be correct)
03-28 16:56:24.031 D/MYDBINFO:      GroupID = 2 Description = Group2!!! - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 10(May not be correct)
03-28 16:56:24.031 D/MYDBINFO:      GroupID = 3 Description = Group2_2 - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 1(May not be correct)
03-28 16:56:24.031 D/MYDBINFO:  SiteID = 2 Description = Site2 - Server1 ServerID = 1
03-28 16:56:24.032 D/MYDBINFO:  Groups in Site = 1
03-28 16:56:24.032 D/MYDBINFO:      GroupID = 4 Description = Group3 - Site2 (impicitly Server 1)Parent SiteID = 2 Parent ServerID = 2(May not be correct)
03-28 16:56:24.032 D/MYDBINFO:  SiteID = 5 Description = Site5 - Server 1 ServerID = 1
03-28 16:56:24.032 D/MYDBINFO:  Groups in Site = 0

Extra You may wish to consider the following which uses what appear to be the correct relationships :-

@Transaction
@Query("SELECT * FROM servers " +
        "JOIN sites ON sites.server_id = servers.server_id " +
        "JOIN groups ON groups.site_id = sites.site_id " +
        "WHERE servers.server_id = :serverId AND sites.site_id = :siteId AND groups.group_id = :groupId")
fun getSuggested(serverId: Long, siteId: Long, groupId: Long): List<ServerWithSiteWithGroup>

This actually achieves the same result for the same test case anyway :-

03-28 16:56:24.032 D/MYDBINFO: ????Servers With Sites With Groups and WHERE clause (suggested)
03-28 16:56:24.033 D/MYDBINFO: ServerID = 1 DeviceID = 2 AccountID = 1 CompanyID = 3
03-28 16:56:24.033 D/MYDBINFO: Sites in Server = 3
03-28 16:56:24.033 D/MYDBINFO:  SiteID = 1 Description = Site1 - Server1 ServerID = 1
03-28 16:56:24.033 D/MYDBINFO:  Groups in Site = 3
03-28 16:56:24.033 D/MYDBINFO:      GroupID = 1 Description = Group1 - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 1(May not be correct)
03-28 16:56:24.033 D/MYDBINFO:      GroupID = 2 Description = Group2!!! - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 10(May not be correct)
03-28 16:56:24.033 D/MYDBINFO:      GroupID = 3 Description = Group2_2 - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 1(May not be correct)
03-28 16:56:24.033 D/MYDBINFO:  SiteID = 2 Description = Site2 - Server1 ServerID = 1
03-28 16:56:24.033 D/MYDBINFO:  Groups in Site = 1
03-28 16:56:24.033 D/MYDBINFO:      GroupID = 4 Description = Group3 - Site2 (impicitly Server 1)Parent SiteID = 2 Parent ServerID = 2(May not be correct)
03-28 16:56:24.033 D/MYDBINFO:  SiteID = 5 Description = Site5 - Server 1 ServerID = 1
03-28 16:56:24.033 D/MYDBINFO:  Groups in Site = 0

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 @Relation is 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 :-

SELECT *,sites.description AS site_description, groups.description AS group_description FROM groups 
    INNER JOIN sites ON sites.site_id = groups.site_id 
    INNER JOIN servers ON servers.server_id = sites.server_id
    WHERE servers.server_id = 1 AND sites.site_id = 1 AND groups.group_id = 1;

With the above data (note sites.description AS site_description, groups.description AS group_description are 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 :-

class AltServerSiteGroup {
    var server_id: Long = 0
    var device_id: Long = 0
    var account_id: Long = 0
    var company_id: Long = 0
    var site_id: Long = -1
    var site_description: String = ""
    var group_id: Long = -1
    var group_description: String = ""
}

Note I changed to using Long instead of Int (in Server as well).

Then consider the Dao :-

@Transaction
@Query("SELECT *, sites.description AS site_description, groups.description AS group_description FROM groups " +
        "INNER JOIN sites ON sites.site_id = groups.site_id " +
        "INNER JOIN servers ON servers.server_id = sites.server_id " +
        "WHERE servers.server_id = :serverId AND sites.site_id = :siteId AND groups.group_id = :groupId")
fun getAlt(serverId: Long, siteId: Long, groupId: Long): List<AltServerSiteGroup>

Just AltServerSiteGroup instead of List<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 :-

   Log.d(TAG, "Alternative Perhaps what is wanted")
    val alt = allDao.getAlt(1,1,1)
    for(s: AltServerSiteGroup in alt) {
        logserver(Server(s.server_id,s.account_id,s.device_id,s.company_id))
        logsite(Site(s.site_id,s.server_id,s.site_description))
        loggroup(Group(s.group_id,s.server_id,s.site_id,s.group_description))
    }

Functions added to log the Entitities and hence logserver(Server(....)) etc

Then the Result from the section would be :-

03-28 19:10:41.930 D/MYDBINFO: Alternative Perhaps what is wanted
03-28 19:10:41.931 D/MYDBINFO: ServerID = 1 DeviceID = 2 AccountID = 1 CompanyID = 3
03-28 19:10:41.931 D/MYDBINFO:  SiteID = 1 Description = Site1 - Server1 ServerID = 1
03-28 19:10:41.931 D/MYDBINFO:      GroupID = 1 Description = Group1 - Site1 (impicitly Server 1)Parent SiteID = 1 Parent ServerID = 1(May not be correct)

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. :-

class AltServerSiteGroup {
    var server_id: Long = 0
    var device_id: Long = 0
    var account_id: Long = 0
    var company_id: Long = 0
    var site_id: Long = -1
    var site_description: String = ""
    var group_id: Long = -1
    var group_description: String = ""

    fun getServer(): Server {
        return Server(server_id,account_id,device_id,company_id)
    }

    fun getSite(): Site {
        return Site(site_id,server_id,site_description)
    }

    fun getGroup(): Group {
        return Group(group_id,server_id,site_id,group_description)
    }
}

Explanation
In short Room will build the complete objects according to the @Relation and 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 is AllDao_Impl), you will see what room does and that the code built for getAlt is a lot shorter than the code for getSiteWithGroup.

That seems a very analytical answer and i surely will go down and study that. Thank you in advance for sharing your knowledge. I will be back when i find out what fits better for my needs! — james04, Mar 28 '21 at 09:45
Mike...i dont understand. In the last Query "allDao.getSiteWithGroup(1,1,1)" since ask for specific siteId and groupId, we can clearly see that in the ForLoop you LOG more than one sites. We dont want that.. — james04, Mar 28 '21 at 14:10
@james04 see **Even More**, at the bottom, and the query `getAlt(1,1,1)` which returns just the 1 server-site-group via the POJO `AltServerSiteGroup` which doesn't use @Relation but just has members (vars) for the required columns. — MikeT, Mar 28 '21 at 18:57
Yeah, i am aware of this solution. However the point was to do this with @Relation and 3 entities. I posted kinda of the same question to the IssueTracker on Google and they also said that it cant be done! — james04, Mar 28 '21 at 21:10
@james04 from https://developer.android.com/reference/androidx/room/Relation *A convenience annotation which can be used in a POJO to automatically fetch relation entities. When the POJO is returned from a query, **all of its relations are also fetched by Room.*** — MikeT, Mar 28 '21 at 23:04
I have seen. that. Thank you. However it seems that it does not work in all the cases! I guess i have to implement the custom POJO solution — james04, Mar 29 '21 at 08:05

Select 3 entities at once from Room Database with @Relation

1 Answers1