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
