Android Room Multimap issue for the same column names

Question

As stated in official documentation, it's preferable to use the Multimap return type for the Android Room database.
With the next very simple example, it's not working correctly!

@Entity
data class User(@PrimaryKey(autoGenerate = true) val _id: Long = 0, val name: String)

@Entity
data class Book(@PrimaryKey(autoGenerate = true) val _id: Long = 0, val bookName: String, val userId: Long)

(I believe a loooot of the developers have the _id primary key in their tables)

Now, in the Dao class:

@Query(
    "SELECT * FROM user " +
        "JOIN book ON user._id = book.userId"
)
fun allUserBooks(): Flow<Map<User, List<Book>>>

The database tables:

Finally, when I run the above query, here is what I get:

While it should have 2 entries, as there are 2 users in the corresponding table.

PS. I'm using the latest Room version at this point, Version 2.4.0-beta02.

PPS. The issue is in how UserDao_Impl.java is being generated:
all the _id columns have the same index there.

Is there a chance to do something here? (instead of switching to the intermediate data classes).

Answer 1

all the _id columns have the same index there. Is there a chance to do something here?

Yes, use unique column names e.g.

@Entity
data class User(@PrimaryKey(autoGenerate = true) val userid: Long = 0, val name: String)

@Entity
data class Book(@PrimaryKey(autoGenerate = true) valbookid: Long = 0, val bookName: String, val useridmap: Long) 

• as used in the example below.

or

@Entity
data class User(@PrimaryKey(autoGenerate = true) @ColumnInfo(name="userid")val _id: Long = 0, val name: String)

@Entity
data class Book(@PrimaryKey(autoGenerate = true) @ColumnInfo(name="bookid")val _id: Long = 0, val bookName: String, val @ColumnInfo(name="userid_map")userId: Long)

Otherwise, as you may have noticed, Room uses the value of the last found column with the duplicated name and the User's _id is the value of the Book's _id column.

Using the above and replicating your data using :-

    db = TheDatabase.getInstance(this)
    dao = db.getAllDao()

    var currentUserId = dao.insert(User(name = "Eugene"))
    dao.insert(Book(bookName = "Eugene's book #1", useridmap = currentUserId))
    dao.insert(Book(bookName = "Eugene's book #2", useridmap = currentUserId))
    dao.insert(Book(bookName = "Eugene's book #3", useridmap = currentUserId))
    currentUserId = dao.insert(User(name = "notEugene"))
    dao.insert(Book(bookName = "not Eugene's book #4", useridmap = currentUserId))
    dao.insert(Book(bookName = "not Eugene's book #5", useridmap = currentUserId))

    var mapping = dao.allUserBooks() //<<<<<<<<<< BREAKPOINT HERE
    for(m: Map.Entry<User,List<Book>> in mapping) {

    }

• for convenience and brevity a Flow hasn't been used and the above was run on the main thread.

Then the result is what I believe you are expecting :-

Additional

What if we already have the database structure with a lot of "_id" fields?

Then you have some decisions to make.

You could

• do a migration to rename columns to avoid the ambiguous/duplicate column names.
• use alternative POJO's in conjunction with changing the extract output column names accordingly

e.g. have :-

data class Alt_User(val userId: Long, val name: String)

and

data class Alt_Book (val bookId: Long, val bookName: String, val user_id: Long)

along with :-

@Query("SELECT user._id AS userId, user.name, book._id AS bookId, bookName, user_id  " +
        "FROM user JOIN book ON user._id = book.user_id")
fun allUserBooksAlt(): Map<Alt_User, List<Alt_Book>>

• so user._id is output with the name as per the Alt_User POJO
• other columns output specifically (although you could use * as per allUserBookAlt2)

:-

@Query("SELECT *, user._id AS userId, book._id AS bookId " +
        "FROM user JOIN book ON user._id = book.user_id")
fun allUserBooksAlt2(): Map<Alt_User, List<Alt_Book>>

• same as allUserBooksAlt but also has the extra columns
• you would get a warning warning: The query returns some columns [_id, _id] which are not used by any of [a.a.so70190116kotlinroomambiguouscolumnsfromdocs.Alt_User, a.a.so70190116kotlinroomambiguouscolumnsfromdocs.Alt_Book]. You can use @ColumnInfo annotation on the fields to specify the mapping. You can annotate the method with @RewriteQueriesToDropUnusedColumns to direct Room to rewrite your query to avoid fetching unused columns. You can suppress this warning by annotating the method with @SuppressWarnings(RoomWarnings.CURSOR_MISMATCH). Columns returned by the query: _id, name, _id, bookName, user_id, userId, bookId. public abstract java.util.Map<a.a.so70190116kotlinroomambiguouscolumnsfromdocs.Alt_User, java.util.List<a.a.so70190116kotlinroomambiguouscolumnsfromdocs.Alt_Book>> allUserBooksAlt2();
  • Due to Note that Room will not rewrite the query if it has multiple columns that have the same name as it does not yet have a way to distinguish which one is necessary. the @RewriteQueriesToDropUnusedColumns doesn't do away with the warning.

if using :-

    var mapping = dao.allUserBooksAlt() //<<<<<<<<<< BREAKPOINT HERE
    for(m: Map.Entry<Alt_User,List<Alt_Book>> in mapping) {
    }

Would result in :-

• possibly other options.

However, I'd suggest fixing the issue once and for all by using a migration to rename columns to all have unique names. e.g.