Using Room's @Relation with ORDER BY

Question

I try the Room library, which is a very impressive one by the way !

So I have two @Entity and one POJO which is composed of the two entities.

My first entity:

@Entity(tableName = "colis")
public class ColisEntity {
    @PrimaryKey
    private String idColis;
    private String label;
}  

My second entity with a foreign key:

@Entity(tableName = "step",
    foreignKeys = @ForeignKey(
        entity = ColisEntity.class,
        parentColumns = "idColis",
        childColumns = "idColis",
        onDelete = CASCADE
    )
)
public class StepEntity {
    @PrimaryKey(autoGenerate = true)
    private Integer idStep;
    private String idColis;
    private Long date;
}

My POJO with @Relation:

public class ColisWithSteps {
    @Embedded
    public ColisEntity colisEntity;

    @Relation(parentColumn = "idColis", entityColumn = "idColis")
    public List<StepEntity> stepEntityList;
}

All of this stuff works fine with my @Dao and Repositories.

But I want my @Relation List<StepEntity> to be ORDERED BY date and I don't want StepEntity to implement Comparable and make a Collections.sort().

Cause I think the sort should be done by the Database and not after the query.

Any idea ?

Thanks.

Answer 1

I know OP said no to Collection.Sort() but given the circumstances it seems to be the cleanest approach. As I said above I think this is better to avoid having to do separate queries every time the data needs to be accessed. First implement Comparable.

public class StepEntity implements Comparable<StepEntity >{
    @PrimaryKey(autoGenerate = true)
    private Integer idStep;
    private String idColis;
    private Long date;

   @Override
    public int compareTo(@NonNull StepEntity stepEntity ) {
        int dateOther = stepEntity.date;

        //ascending order
        return this.date - dateOther;

       //descending order
       //return dateOther - this.date;
    }
}

And now to use it add a method wrapper in your existing @Relation POJO

public class ColisWithSteps {
        @Embedded
        public ColisEntity colisEntity;

        @Relation(parentColumn = "idColis", entityColumn = "idColis")
        public List<StepEntity> stepEntityList;

        //add getter method
        public List<StepEntity> getSortedStepEntityList(){
           Collections.sort(stepEntityList);
           return stepEntityList;
       }
    }

Answer 2

The documentation for Relationships gives a hint as to how it works when it says

This method requires Room to run two queries, so add the @Transaction annotation to this method to ensure that the whole operation is performed atomically.

This effectively gives away the entire process behind what the relation is doing. You'll have to write a query for each Entity, followed by a single Transaction query, but the end result is indistinguishable (at least as far as I can see) from what you would get from using a relation.

The (Kotlin) code for you is as follows:

@Query("SELECT * FROM colis WHERE idColis = :id")
fun getColisEntity(id : Int) : 

@Query("SELECT * FROM step WHERE idColis = :id ORDER BY date")
fun getStepEntities(id : Int) : List<StepEntity> 

@Transaction
fun getColisWithSteps(id : Int) : ColisWithSteps{
    return ColisWithSteps(getColisEntity(id), getStepEntities(id))                      
}

getColisWithSteps(id : Int) will return exactly what you are looking for, and the result is identical to what Relation would give you, except with more freedom for ordering.

Answer 3

I don't think there is a built-in way to do this in the current version of Room.

I can suggest two possible solutions.

1. Instead of a POJO with @Relation, just use two separate queries to get your objects. That way you can order your StepEntity instances exactly the way you like. When you get the ColisEntity and all ordered corresponding StepEntity records, you can construct a ColisWithSteps object in your repo layer and return it.
2. You can create a database view that orders StepEntity records in the desired order and then use this answer Views in Room to be able to use it.

I think that option 1 is the best in your case - yes, it will involve using two queries, but at least it won't break your DB migrations and you will be able to use Room to your advantage.