Joining an Embedded Entity's row to POJO in Room Dao

Question

I'm querying for a POJO using Room Persistance Library (v2.0.0). I've all those entities in different tables (bookentries, categories,...). I'm trying to combine a BookEntryEntity with a Category that matches the BookEntry's category_id.

class BookEntry(
        @Embedded var entity: BookEntryEntity = BookEntryEntity(),
        @Embedded(prefix = 'cg_') var category: Category? = null,
        contacts: List<Contact.ContactEntity>? = null
) : Parcelable {

    @Relation(
            entity = Contact.ContactEntity::class,
            parentColumn = 'id',
            entityColumn = 'bookentry_id'
    )
    var embeddedContacts: List<Contact.ContactEntity>? = contacts

    //...

    @Entity(tableName = 'bookentries')
    data class BookEntryEntity(
            @PrimaryKey(autoGenerate = true) @ColumnInfo(name = 'id') var id: Long = 0,
            @ColumnInfo(name = 'title') var title: String = "",
            @ColumnInfo(name = 'date') var date: Date = Date(),
            @ColumnInfo(name = 'value') var value: Double = 0.0,
            @ColumnInfo(name = 'notes') var notes: String = "",
            @ColumnInfo(name = 'entrytype') var entryType: Int = Type.Earning,
            @ColumnInfo(name = Reference.CATEGORY_ID) var categoryId: Long? = null
    )

}

The Embedded Entity Category is not joined automatically.

@Entity(tableName = 'categories')
data class Category(@PrimaryKey(autoGenerate = true) @ColumnInfo(name = 'id') val id: Long = 0, @ColumnInfo(name = 'name') var name: String = "")

I tried to join it the following way:

@Dao
interface BookEntryDao {

    @Transaction
    @Query("SELECT * FROM bookentries INNER JOIN categories ON bookentries.cg_id = categories.id WHERE categories.id == bookentries.category_id ORDER BY bookentries.date DESC")
    fun getBookEntries(): LiveData<List<BookEntry>>
}

Unfortunately, I'm getting an error:

error: There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such column: bookentries.cg_id)

But if I remove the inner join, I receive the following warning:

warning: at.guger.moneybook.data.model.BookEntry has some fields [cg_id, cg_name] which are not returned by the query.

How to join the query correctly?

Answer 1

Try:

@Dao
interface BookEntryDao {

    @Transaction
    @Query("SELECT * FROM bookentries INNER JOIN categories ON bookentries.category_id = categories.id ORDER BY bookentries.date DESC")
    fun getBookEntries(): LiveData<List<BookEntry>>
}