Deconflict column names in "join" entity

Question

I have 2 entities:

@Entity(tableName = "author")
data class Author(
   @PrimaryKey
   @ColumnInfo(name = "id")
   val id: String,

   @ColumnInfo(name = "name")
   val name: String
)

data class Book(
   @ColumnInfo(name = "id")
   val id: String,

   @ColumnInfo(name = "title")
   val title: String,

   @ColumnInfo(name = "author_id")
   var authorId: String
)

And I would like to join them in a query:

@Query("SELECT * FROM book JOIN author ON author.id = book.author_id AND author.id = :authorId WHERE book.id = :bookId")
fun item(authorId: String, bookId: String): LiveData<BookWithAuthor>

Into this entity:

@Entity
data class BookWithAuthor(
        @Relation(parentColumn = "author_id", entityColumn = "id")
        val author: Author,

        @Embedded
        val book: Book
)

However when I do that I get back a BookWithAuthor object in which the author.id and book.id are the same id, in this case they are both the author's id. How do I deconflict the "id" property in the entities in the "join" object?

Answer 1

You can use the @Embedded's prefix to disambiguate the names.

e.g. use :-

@Embedded(prefix="book_")
val book: Book

along with :-

@Query("SELECT author.*, book.id AS book_id, book.title AS book_title, book.author_id AS book_author_id FROM book JOIN author ON author.id = book.author_id AND author.id = :authorId WHERE book.id = :bookId")

• Note the above is in-principle code, it has not been tested or run.

You would then change BookWithAuthor to use the prefixed column so :-

@Entity /* not an Entity i.e. Entity = table this just needs to be a POJO */
data class BookWithAuthor(
    @Embedded(prefix = "book_")
    val book: Book,
    /* with (makes more sense to have parent followed by child) */
    @Relation(/*entity = Author::class,*/ parentColumn = "book_author_id", entityColumn = "id")
    val author: Author
)

---

However, your comment it assumes that all book ids are unique. In my case I could potentially have duplicate book ids for different authors.

appears to not fit in with the table/entities (schema) you have coded. i.e.

1. Author entity is fine.
2. Book though does not have @Entity annotation, nor does it have the obligatory @PrimaryKey annotation if it is an Entity defined in the entities=[....] lis. The assumption made is that the id is/would be the primary key and annotated accordingly and thus unique.
3. BookWithAuthor You will see that BookWithAuthor has been commented with Not an Entity (table). You cannot have the @Relationship annotation in an Entity that is defined as an Entity to the database (i.e. one of the classes in the entities=[....] list of the @Database annotation).

So unless the primary key of the Book entity/table is not the id or that the authorid is a list of authors then a Book can have only one author. As such it would appear that you only need @Query("SELECT * FROM book WHERE id=:bookId"): LiveData<BookWithAuthor>

• if not then coding @Relation will basically ignore your JOIN and select ALL authors but then only pick the first which would be an arbitrary author to complete the author. That is @Relation works by obtaining the parent(s) and then build it's own underlying query to access ALL children of the parent. So whatever Query you supply it ONLY uses this to ascertain the parents.

I suspect that what you want is that a book can have a number of authors and that an author can be an author of a number of books. In this scenario you would typically use a mapping table (can be called other names such as link, reference, associative .... ). If this is the case and you can't ascertain how to create the mapping table via room, then you could ask another question in that regard.

Answer 2

I think the problem here is defining the relation.

My understanding is this is a one to many relationship: One Author (parent) has zero or more Books (entities).

What your @Relation defines is a 1:1 relationship.

If what you want eventually is a BookWithAuthor, why not embedd the Author in Book directly? You would then have the following tables:

@Entity(tableName = "author")
data class Author(
   @PrimaryKey
   @ColumnInfo(name = "author_id")
   val id: String,

   @ColumnInfo(name = "name")
   val name: String
)

@Entity(tableName = "BookWithAuthor")
data class Book(
   @PrimaryKey
   @ColumnInfo(name = "id")
   val id: String,

   @ColumnInfo(name = "book_id")
   val id: String,

   @ColumnInfo(name = "title")
   val title: String,

   @Embedded
   val author: Author   
)

And your query can look like this:

@Query("SELECT * FROM BookWithAuthor WHERE book_id = :bookId AND author_id = :authorId")
fun item(authorId: String, bookId: String): LiveData<BookWithAuthor>

• After embedding, Book takes the same columns of Author with their exact names. So we need to at least rename either of the id columns to resolve ambuiguity.
• Since book ids can be duplicate, we need to introduce a new column as the PrimaryKey for Book.