Room Library - relationship between tables having auto generated keys-CodePudding

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>
}

CodePudding user response：

Room supports this since version 2.4 as described in the official

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