Room Library - relationship between tables having auto generated keys

Question

The examples I came across were forming relations without primary key, my database schema is as follow.

I have primary key in both tables, and I'm attempting to create a DAO method that will join both user and department tables to display a record.

Question, how to create a DAO method to access the record joined by the department information. I prefer to do without creating more data classes if possible. for instance using Map<User, Department>. If there's none then I will accept other solutions.

User Table

+----+----------+----------+---------------+
| id | username |   name   | department_id |
+----+----------+----------+---------------+
|  1 | johndoe  | John Doe |             3 |
|  2 | janedoe  | Jane Doe |             4 |
+----+----------+----------+---------------+

User data class

@Entity(tableName = "user")
data class User(
    @PrimaryKey (autoGenerate = true) var id: Long,
    var username: String,
    var name: String,
    var department_id: Long)

Department Table

+----+----------------+
| id |      name      |
+----+----------------+
|  1 | Sales          |
|  2 | Account        |
|  3 | Human Resource |
|  4 | Marketing      |
+----+----------------+

Department data class

@Entity(tableName = "department")
data class Department(
    @PrimaryKey(autoGenerate = true) var id: Long,
    var name: String)

---

My attempt at DAO

@Dao
interface UserDAO {

    @Query("SELECT user.*, department.name AS 'department_name' FROM user " +
            "INNER JOIN department ON user.department_id = department.id " +
            "WHERE user.id = :id")
    fun findById(id: Long): Map<User, Department>
}

---

Edit

Will it work if Department is composed inside User? If that's the case then how the query inside DAO would look like?

@Entity(tableName = "user")
data class User(
    @PrimaryKey (autoGenerate = true) var id: Long,
    var username: String,
    var name: String,
    @Embedded var department: Department)

data class Department(
    @PrimaryKey(autoGenerate = true)
    var id: Long,
    var name: String? = null): Parcelable

DAO attempt

@Query("SELECT * FROM user " +
    "INNER JOIN department d ON department.id = d.id " +
    "WHERE id = :id")
fun findById(id: Long): Map<User, Department>

Answer 1

Your code works as it is BUT due to ambiguous column names (id and name) values are not as expected.

Here's the result (noting that User id's are 500 and 501 instead of 1 and 2 to better highlight the issue) when debugging, with a breakpoint at a suitable place and when using:-

findById(500)

then the debugger shows :-

That is that although John's department id is 3, it is instead 500 in the Department itself, likewise the name of the department is John Doe not the expected *Human Resource.

This is because Room doesn't know which id is for what and likewise name.

The fix is to use unique column names. e.g. with:-

@Entity(tableName = "department")
data class Department(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "dept_id")
    var id: Long,
    @ColumnInfo(name = "dept_name")
    var name: String
 )

• i.e. the columns in the table, but not in the Department have been changed. You could just change the name of the fields to be unique names.

Obviously if the column names are changed then the SQL has to be changed accordingly, so :-

@Query("SELECT * FROM user " +
        "INNER JOIN department ON department_Id = dept_id " +
        "WHERE id = :id")
fun findById(id: Long): Map<User, Department>

• as the column names now have no ambiguities then there is no need to use table names to differentiate (you still can. Without the table banes the SQL can be more concise.

Now Debug shows:-

Additional re Edit

Will it work if Department is composed inside User?

If you use @Embedded within an @Entity annotated class that is included in the entities parameter of the @Database annotated class then the resultant table has columns as per the embedded class.

If the embedded class was previously a table then in respect of the relationship that doesn't exist and that previous table may well be defunct.

There is an issue with the embedded Department in that you will have 2 columns named id

From a database perspective, if what was a 1 to many relationship then the same data (e.g. Human Resource) will be repeated and is contrary to normalisation.

In regard to the query

@Query("SELECT * FROM user " +
    "INNER JOIN department d ON department.id = d.id " +
    "WHERE id = :id")
fun findById(id: Long): Map<User, Department>

Then this will not work as there will be no department table (as according to your code the Department class is not annotated with @Entity).