Android Room fetch data with where cross reference data class has a third-party element

Question

Got a problem, where i have a third-party field in a cross reference class. I have a Project entity data class:

@Entity(tableName = "projects")
data class Project(

        @PrimaryKey(autoGenerate = true)
        val projectId: Int = 0,

        var title: String,

        var details: String,

        var date: Date,

        var price: Int
)

Record entity data class:

@Entity(tableName = "records")
data class Record (

        @PrimaryKey(autoGenerate = true)
        val recordId: Int,

        var title: String,

        var details: String,

        var price: Int,

        )

Cross reference entity data class

@Entity(
        tableName = "cross_ref",
        primaryKeys = ["projectId", "recordId"],
        foreignKeys = [
                ForeignKey(
                        entity = Project::class,
                        parentColumns = ["projectId"],
                        childColumns = ["projectId"]
                ),
                ForeignKey(
                        entity = Record::class,
                        parentColumns = ["recordId"],
                        childColumns = ["recordId"]
                )
        ]
)
data class ProjectRecordCrossRef(

        val projectId: Int,

        @ColumnInfo(index = true)
        val recordId: Int,

        val quantity: Int
)

and a POJO class

data class ProjectWithRecords(
        @Embedded
        val project: Project,

        @Relation(
                parentColumn = "projectId",
                entityColumn = "recordId",
                associateBy = Junction(ProjectRecordCrossRef::class)
        )
        val records: MutableList<Record>,

        @Relation(
                entity = ProjectRecordCrossRef::class,
                parentColumn = "projectId",
                entityColumn = "quantity"
        )
        val quantities: MutableList<Int> = arrayListOf()
)

Also I have a Dao class

@Dao
interface CrossRefDao {

    @Transaction
    @Query("SELECT * FROM projects WHERE projectId = :projectId")
    fun getProjectWithRecords(projectId: Int): LiveData<ProjectWithRecords>

    @Query("SELECT * FROM cross_ref WHERE projectId =:projectId")
    suspend fun getAllByProjectId(projectId: Int) : List<ProjectRecordCrossRef>

    @Query("SELECT * FROM cross_ref")
    suspend fun getAllCrosses(): List<ProjectRecordCrossRef>

    @Query("SELECT * FROM cross_ref WHERE projectId =:projectId AND recordId =:recordId")
    suspend fun getAllByProjectAndRecordIds(projectId: Int, recordId: Int) : List<ProjectRecordCrossRef>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(crossRef: ProjectRecordCrossRef)

    @Delete
    suspend fun delete(crossRefs: List<ProjectRecordCrossRef>)
}

The problem is: I can't get quantities from Dao in a POJO

So, how can i get list of third-party values from cross reference?

ProjectWithRecords has Project, list of records, list of quantities of records.

May be I should make it another way?

Answer 1

In the ProjectWithRecords POJO, the relationship will (should) retrieve a ProjectRecordCrossRef object so you can get a List and THEN get the quantity(ies).

However, additionally you are saying that the relationship is between the Project and the quantity (may be lucky to get a match may not BUT you would only get quantities that match the projectId).

Consider the following:-

data class ProjectWithRecords(
    @Embedded
    val project: Project,

    @Relation(
        parentColumn = "projectId",
        entityColumn = "recordId",
        associateBy = Junction(ProjectRecordCrossRef::class)
    )
    val records: MutableList<Record>,

    @Relation(
        entity = ProjectRecordCrossRef::class,
        parentColumn = "projectId",
        entityColumn = "projectId" //<<<<<
    )
    //val quantities: MutableList<Int> = arrayListOf()
    val projectRecordCrossRef: List<ProjectRecordCrossRef>
)

Utilising your code provided (slightly modified for convenience), the modified POJO (as above) and the following test/demo in an Activity:-

    dao = db.getCrossRefDao()

    val p1Id = dao.insert(Project(0,"Project1","blah for project1","2021-01-01",111))
    val p2Id = dao.insert(Project(0,"Project2","blah for project2","2021-03-03",222))
    val r1Id = dao.insert(Record(0,"Record1","detail for record 1",10))
    val r2Id = dao.insert(Record(0,"Record2","details for record 2",20))
    val r3Id = dao.insert(Record(0,"Record3","details for record 3",30))
    val r4Id = dao.insert(Record(0,"Record4","details for record 4",40))
    dao.insert(ProjectRecordCrossRef(p1Id,r3Id,10000))
    dao.insert(ProjectRecordCrossRef(p1Id,r2Id,20000))
    dao.insert(ProjectRecordCrossRef(p1Id,r1Id,30000))
    dao.insert(ProjectRecordCrossRef(p2Id,r1Id,40000))
    dao.insert(ProjectRecordCrossRef(p2Id,r4Id,50000))

    val pwr = dao.getProjectWithRecords(1)
    Log.d("PRINFO","Project is ${pwr.project.title}")
    for (prcr: ProjectRecordCrossRef in pwr.projectRecordCrossRef)
        Log.d("PRINFO","\tQuantity is ${prcr.quantity}" )

When run the result in the log is :-

D/PRINFO: Project is Project1
D/PRINFO:   Quantity is 30000
D/PRINFO:   Quantity is 20000
D/PRINFO:   Quantity is 10000