Android Room embedded relationship query results

Question

I am currently trying to get results from a query of nested relationships in Room. Here Are my classes/database entities involved:

@Entity
data class PrayerRequestEntity(
    var title: String,
    var details: String,
    var category: String,
    var isAnswered: Boolean,
    var prayerCount: Int,
    var dateCreated: Date,
    var dateUpdated: Date
) {
    @PrimaryKey(autoGenerate = true)
    var prayerRequestId: Int = 0
}

@Entity
data class PrayerPlanEntity(
    var title: String,
    var sessionCount: Int,
    var dateCreated: Date,
    var dateUpdated: Date
) {
    @PrimaryKey(autoGenerate = true)
    var planId: Int = 0
}


@Entity(primaryKeys = ["planId", "prayerRequestId"])
data class PrayerPlanPrayerRequestJoinEntity(
    val planId: Int,
    val prayerRequestId: Int
)

I am trying to get a list of PrayerPlanEtities along with the PrayerRequestEntities associated with each and have no problem using the following return:

data class PrayerPlanPrayerRequestsJoin(
    @Embedded
    val prayerPlan: PrayerPlanEntity,

    @Relation(
        parentColumn = "planId",
        entityColumn = "prayerRequestId",
        associateBy = Junction(PrayerPlanPrayerRequestJoinEntity::class)
    )
    val prayers: List<PrayerPlanEntity>
)

I need to also query the relationships of PrayerRequestEntity in that same query so Imodify the aforementioned class like so:

data class PrayerPlanPrayerRequestsJoin(
    @Embedded
    val prayerPlan: PrayerPlanEntity,

    @Relation(
        parentColumn = "planId",
        entityColumn = "prayerRequestId",
        associateBy = Junction(PrayerPlanPrayerRequestJoinEntity::class)
    )
    val prayers: List<PrayerRequestJoin>
)

PrayerRequestJoin is a previous relationship I created which works perfectly fine on it own and looks like so:

data class PrayerRequestJoin(
    @Embedded
    val prayerRequest: PrayerRequestEntity,

    @Relation(
        parentColumn = "prayerRequestId",
        entityColumn = "prayerRequestCategoryId",
        associateBy = Junction(PrayerRequestCategoryJoinEntity::class)
    )
    val category: PrayerRequestCategoryEntity,

    @Relation(
        parentColumn = "prayerRequestId",
        entityColumn = "photoId",
        associateBy = Junction(PrayerRequestPhotoJoinEntity::class)
    )
    val photos: List<PhotoEntity>,

    @Relation(
        parentColumn = "prayerRequestId",
        entityColumn = "noteId",
        associateBy = Junction(PrayerRequestNoteJoinEntity::class)
    )
    val notes: List<NoteEntity>
)

But now I am getting two build errors an Android Studio:

error: constructor PrayerPlanPrayerRequestsJoin in class PrayerPlanPrayerRequestsJoin cannot be applied to given types; _item = new PrayerPlanPrayerRequestsJoin(); ^ required: PrayerPlanEntity,List found: no arguments reason: actual and formal argument lists differ in length

and also:

error: prayerPlan has private access in PrayerPlanPrayerRequestsJoin _item.prayerPlan = _tmpPrayerPlan;

Can anyone provide some insight on what may be causing this issue?

Answer 1

I believe that your issue is with the @Query's rather than with the relationships.

Upon closer inspection the messages shows java code e.g. termination with ;'s.

Therefore the issue is within the generated java and it would appear (from creating a working example) that the issue is in the code underlying the class or classes annotated with @Dao

• i.e. _item does not appear in the java for the class annotated with @Database. Whilst _item = new .... appears for each @Query annotated function. e.g.

• for a Query using SELECT * FROM PrayerPlanPrayerRequestJoinEntity then the code includes _item = new PrayerPlanPrayerRequestJoinEntity(_tmpPlanId,_tmpPrayerRequestId);

• for a Query using "SELECT * FROM prayerRequestEntity" then the code includes _item = new PrayerRequestJoin(_tmpPrayerRequest,_tmpCategory_1,_tmpPhotosCollection_1,_tmpNotesCollection_1);

As can be seen these are used to build the final result. The first example being the closest from the working example but there is no issue.

As such I believe that the issue is not with the relationships (working example runs OK) but the issue is with the @Query annotated functions.

I would suggest commenting them all out and adding each until you find the culprit.

The working example

Note that this example introduces/uses some shortcuts so the code differs a little.

• the code is run on the main thread so uses .allowMainThreadQueries
• Long's are used instead of Ints for id's (they should really always be longs as they can be signed 64 bit)
• Autogenerate has not been used for classes that have been guessed such as NoteEntity (autogenerate (which equates to AUTOINCREMENT) is actually not recommended by SQLite themselves The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed. https://sqlite.org/autoinc.html)
• TypeConverters have been avoided

The code:-

NoteEntity made up just to test, so very simple

@Entity
data class NoteEntity(
    @PrimaryKey
    var noteId: Long? = null,
    var noteText: String = ""
)

PhotoEntity made up ....

@Entity
data class PhotoEntity(
    @PrimaryKey
    var photoId: Long? = null,
    var photoImage: String = "unknown"
)

PrayerPlanEntity

@Entity
data class PrayerPlanEntity(
    var title: String,
    var sessionCount: Int,
    var dateCreated: String, /* changed for brevity */
    var dateUpdated: String /* changed for brevity */
) {
    @PrimaryKey(autoGenerate = true)
    var planId: Long = 0
}

PrayerPlanPrayerRequestJoinEntity

@Entity(primaryKeys = ["planId", "prayerRequestId"])
data class PrayerPlanPrayerRequestJoinEntity(
    val planId: Long,
    val prayerRequestId: Long
)

PrayerPlanPrayerRequestsJoin assume the 2nd is as it is now

data class PrayerPlanPrayerRequestsJoin(

    /*
    @Embedded
    val prayerPlan: PrayerPlanEntity,

    @Relation(
        parentColumn = "planId",
        entityColumn = "prayerRequestId",
        associateBy = Junction(PrayerPlanPrayerRequestJoinEntity::class)
    )
    val prayers: List<PrayerPlanEntity>
    */

    @Embedded
    val prayerPlan: PrayerPlanEntity,

    @Relation(
        parentColumn = "planId",
        entityColumn = "prayerRequestId",
        associateBy = Junction(PrayerPlanPrayerRequestJoinEntity::class)
    )
    val prayers: List<PrayerRequestJoin>
)

PrayerRequestCategoryEntity made up ....

@Entity
data class PrayerRequestCategoryEntity(
    @PrimaryKey
    var prayerRequestCategoryId: Long? = null,
    var categoryName: String
)

PrayerRequestCategoryJoinEntity made up ....

@Entity(primaryKeys = ["prayerRequestId", "prayerRequestCategoryId"])
data class PrayerRequestCategoryJoinEntity(

    val prayerRequestId: Long,
    val prayerRequestCategoryId: Long
)

PrayerRequestEntity

@Entity
data class PrayerRequestEntity(
    var title: String,
    var details: String,
    var category: String,
    var isAnswered: Boolean,
    var prayerCount: Int,
    var dateCreated: String, /* changed for brevity */
    var dateUpdated: String /* changed for brevity */
) {
    @PrimaryKey(autoGenerate = true)
    var prayerRequestId: Long = 0
}

PrayerRequestJoin

data class PrayerRequestJoin(
    @Embedded
    val prayerRequest: PrayerRequestEntity,

    @Relation(
        parentColumn = "prayerRequestId",
        entityColumn = "prayerRequestCategoryId",
        associateBy = Junction(PrayerRequestCategoryJoinEntity::class)
    )
    val category: PrayerRequestCategoryEntity,

    @Relation(
        parentColumn = "prayerRequestId",
        entityColumn = "photoId",
        associateBy = Junction(PrayerRequestPhotoJoinEntity::class)
    )
    val photos: List<PhotoEntity>,

    @Relation(
        parentColumn = "prayerRequestId",
        entityColumn = "noteId",
        associateBy = Junction(PrayerRequestNoteJoinEntity::class)
    )
    val notes: List<NoteEntity>
)

PrayerRequestNoteJoinEntity made up

@Entity(primaryKeys = ["prayerRequestId", "noteId"])
data class PrayerRequestNoteJoinEntity(
    val prayerRequestId: Long,
    val noteId: Long
)

PrayerRequestPhotoJoinEntity made up ....

@Entity(primaryKeys = ["prayerRequestId", "photoId"])
data class PrayerRequestPhotoJoinEntity(
    val prayerRequestId: Long,
    val photoId: Long
)

AllDAO made up

@Dao
abstract class AllDAO {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(photoEntity: PhotoEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(noteEntity: NoteEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(prayerPlanEntity: PrayerPlanEntity): Long
    @Insert(onConflict =  OnConflictStrategy.IGNORE)
    abstract fun insert(prayerRequestCategoryEntity: PrayerRequestCategoryEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(prayerRequestEntity: PrayerRequestEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(prayerPlanPrayerRequestJoinEntity: PrayerPlanPrayerRequestJoinEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(prayerRequestCategoryJoinEntity: PrayerRequestCategoryJoinEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(prayerRequestNoteJoinEntity: PrayerRequestNoteJoinEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(prayerRequestPhotoJoinEntity: PrayerRequestPhotoJoinEntity): Long

    @Query("SELECT * FROM prayerRequestEntity")
    abstract fun getAllPrayerRequests(): List<PrayerRequestEntity>
    @Query("SELECT * FROM prayerPlanEntity")
    abstract fun getAllPrayerPlans(): List<PrayerPlanEntity>
    @Query("SELECT * FROM PrayerPlanPrayerRequestJoinEntity")
    abstract fun getAllPrayerPlanRequestJoins(): List<PrayerPlanPrayerRequestJoinEntity>
    @Query("SELECT * FROM prayerRequestEntity")
    abstract fun getAllPrayerRequestsWithCategoryNotesAndPhotos(): List<PrayerRequestJoin>

}

TheDatabase made up

@Database(entities = [
    PhotoEntity::class,
    NoteEntity::class,
    PrayerRequestEntity::class,
    PrayerPlanEntity::class,
    PrayerRequestCategoryEntity::class,
    PrayerRequestCategoryJoinEntity::class,
    PrayerRequestNoteJoinEntity::class,
    PrayerRequestPhotoJoinEntity::class,
    PrayerPlanPrayerRequestJoinEntity::class]
    , version = 1,
    exportSchema = false
)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDAO(): AllDAO

    companion object {
        private var instance: TheDatabase? = null
        fun getInstance(context: Context): TheDatabase {
            if (instance == null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,"prayer.db")
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

Last but not least an activity to do a simple test of the related data via the getAllPrayerRequestsWithCategoryNotesAndPhotos query:-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDAO
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

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

        val n1id = dao.insert(NoteEntity(noteText = "Note1"))
        val n2id = dao.insert(NoteEntity(noteText = "Note2"))
        val n3id = dao.insert(NoteEntity(noteText = "Note3"))

        val photo1id = dao.insert(PhotoEntity(photoImage = "photo1"))
        val photo2id = dao.insert(PhotoEntity(photoImage = "photo2"))
        val photo3id = dao.insert(PhotoEntity(photoImage = "photo3"))

        val pp1id = dao.insert(PrayerPlanEntity(title = "PP01",10,"today","never"))

        val pcat01id = dao.insert(PrayerRequestCategoryEntity(categoryName = "CAT01"))
        val pcat02id = dao.insert(PrayerRequestCategoryEntity(categoryName = "CAT02"))
        val pcat03id = dao.insert(PrayerRequestCategoryEntity(categoryName = "CAT03"))

        val pr01id =dao.insert(PrayerRequestEntity("PR01","details01","cat01",false,10,"today","never"))

        dao.insert(prayerPlanPrayerRequestJoinEntity = PrayerPlanPrayerRequestJoinEntity(prayerRequestId = pr01id, planId = pp1id))

        dao.insert(PrayerRequestPhotoJoinEntity(pr01id,photo1id))
        dao.insert(PrayerRequestPhotoJoinEntity(pr01id,photo2id))
        dao.insert(PrayerRequestPhotoJoinEntity(pr01id,photo3id))
        dao.insert(PrayerRequestCategoryJoinEntity(pr01id,n1id))
        dao.insert(PrayerRequestNoteJoinEntity(pr01id,n2id))
        dao.insert(PrayerRequestNoteJoinEntity(pr01id,n3id))
        dao.insert(PrayerRequestCategoryJoinEntity(pr01id,pcat01id))

        /* Extract the Data and output to the log */ 
        for (prj: PrayerRequestJoin in dao.getAllPrayerRequestsWithCategoryNotesAndPhotos()) {
            Log.d("DBINFO","Prayer Request = ${prj.prayerRequest.details} \n\tRequestCategory = ${prj.category.categoryName} ID is ${prj.category.prayerRequestCategoryId}")
            for (photos: PhotoEntity in prj.photos) {
                Log.d("DBINFO","\tPhoto = ${photos.photoImage} ID = ${photos.photoId}")
            }
            for (notes: NoteEntity in prj.notes) {
                Log.d("DBINFO","\tNote = ${notes.noteText} ID = ${notes.noteId}")
            }
        }
    }
}

Finally the result from the Log:-

2022-03-24 12:52:35.758 D/DBINFO: Prayer Request = details01 
        RequestCategory = CAT01 ID is 1
2022-03-24 12:52:35.758 D/DBINFO:   Photo = photo1 ID = 1
2022-03-24 12:52:35.758 D/DBINFO:   Photo = photo2 ID = 2
2022-03-24 12:52:35.758 D/DBINFO:   Photo = photo3 ID = 3
2022-03-24 12:52:35.758 D/DBINFO:   Note = Note2 ID = 2
2022-03-24 12:52:35.759 D/DBINFO:   Note = Note3 ID = 3