Room @Relation with composite Primary Key

Question

my question is an extension of this question (also mine :) ) -> Room composite Primary Key link to Foreign Key So, if I have this class:

public class FoodWithIngredients extends Food{

    @Relation(parentColumn = "id", entityColumn = "food_id", entity = 
    Ingredient.class)
    private List<Ingredient> mIngredients;

}

But the PrimaryKey of "Food" table is composite (primaryKeys = {"id", "language_id"}).

How I can make the @Relation returns records where "parentColumn = {"id", "language_id"}, entityColumn = {"food_id", food_language_id}" ?

Answer 1

The annotation @Relation still doesn't offer support for composite primary keys.

The easiest way to fetch data querying multiple tables keeping the tables clean is with the @Embedded annotation. If you don't mind going dirty you could add an extra field where you concat the fields of the primary key the use @Relation over that field, with all the risks of maintaining the fields and potential wrong comparations over it's data. May be worth, dunno looks bad idea to me.

So the clean solution. Provided the next tables.

//Multiple Staff and Machine can participate on a WorkOrder and they do hours of work related to it

@Entity
data class Staff(
        @PrimaryKey val jdeNumber: String,
        val idNfc: String,
        val staffDescription: String,
        val triadorNumber: String,
        val approverId: Int)

@Entity(primaryKeys = ["machineId"])
data class Machine(
        val machineId: String,
        val machineNumber: String,
        val machineDescription: String,
        val machineNumberAux: String,
        val manufacturer: String,
        val model: String,
        val productionNumber: String,
        val hasHours: Boolean)

//A WorkOrder may have staff, machine or both
@Entity
data class WorkOrder(
        @PrimaryKey val woId: String,
        val date: Long,
        val comments: String = "",
        val userId: String,
        val partStatus: Int
)

//Embedded annotation creates all the fields from the entity inside these tables and add to the field name a prefix, then when we join tables we have no name conflict
@Entity(
        primaryKeys = ["woIdStaff", "wo_jdeNumber"],
        foreignKeys = [
                ForeignKey(entity = WorkOrder::class,
                        parentColumns = ["woId"],
                        childColumns = ["woIdStaff"],
                        onUpdate = ForeignKey.CASCADE,
                        onDelete = ForeignKey.RESTRICT)]
)
data class WorkOrderStaff(
        val woIdStaff: String,
        @Embedded(prefix = "wo_")
        val staff: Staff,
        val hourFrom: Long,
        val hourTo: Long,
        val hoursUsed: Long
)

@Entity(
        primaryKeys = ["woIdMachine", "wo_machineId"],
        foreignKeys = [
                ForeignKey(entity = WorkOrder::class,
                        parentColumns = ["woId"],
                        childColumns = ["woIdMachine"],
                        onUpdate = ForeignKey.CASCADE,
                        onDelete = ForeignKey.RESTRICT)]
)
data class WorkOrderMachine(
        val woIdMachine: String,
        @Embedded(prefix = "wo_")
        val machine: Machine,
        val hourFromMachine: Long,
        val hourToMachine: Long,
        val hoursUsedMachine: Long
)

//Important this entity is the one that maps from JOIN queries
data class FullWorkOrder(
        @Embedded
        val workOrder: WorkOrder
        @Embedded
        val staff: WorkOrderStaff?
        @Embedded
        val machine: WorkOrderMachine?
)

Then we want to query all the workOrders joined with the staff and machines that worked in them and the hours worked for each. So we write a query in our Dao.

@Query("select * from WorkOrder LEFT JOIN WorkOrderStaff ON woId = woIdStaff LEFT JOIN WorkOrderMachine ON woId = woIdMachine")
abstract fun getAllFullWorkOrders(): List<FullWorkOrder>

This mapping to the entity FullWorkOrder behaves like a Db query on a table visualization when you test a SQL, you have to map this so you don't duplicate rows of data or asign incorrectly data, depending the complexity of the joins. I recommend moving the data to key-value maps then join all togheter filtering duplicated keys. In this case we would be mapping to the entity we use on the UI -> DomainWorkOrder.

data class DomainWorkOrder(
    val id: String,
    .
    .
    .
    val staffList: List<StaffRow>
    val machineList: List<MachineRow>
)

I've taken out of the example the real complexity of the tables I'm using that's why you don't see any composite LEFT JOIN on the SQL. I have 8 tables anexed to WorkOrder(1-n) and 2 of those nest inside them 1-n relations down the road. I assure this would do the trick for most cases, just be careful if you try to join to the entity FullWorkOrder the Staff table to have the most recent data, I have a bad experience with that.

I know it's not pure but the schema is respected and the Query / mapping process do not require a lot of work and maintenance. Hope it helps!!

Answer 2

I have found some workaround. But it will effect on performance I suppose. You need to add special getter to your relation field which will filter the results with other parts of composite primary key.

In your case it will looks like:

public class FoodWithIngredients {

    @Embedded
    private Food food;

    @Relation(parentColumn = "id", entityColumn = "food_id", entity = 
    Ingredient.class)
    private List<Ingredient> mIngredients;

    public List<Ingredient> getIngredients() {
        List<Ingredient> result = List<Ingredient>();

        for (ingredient in mIngredients) {
            if (ingredient.foodLanguageId == food.languageId) {
                result.add(ingredient);   
            }
        }

        return result;
    }

}

Answer 3

Starting in Room 2.4.0-alpha04, you can write DAO methods returning a Map<Entity, List<Relation>> for 1:N relations. That enables you to write JOIN queries specifying how the relation shall be fetched, optionally specifying WHERE and ORDER BY clauses.

Source: https://issuetracker.google.com/issues/64247765