CODEWITHSUNDEEP
androidandroid-room
olivejp
olivejp

Reputation: 929

Using Room's @Relation with ORDER BY

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.

Upvotes: 27

Views: 11923

Answers (5)

Jraco11
Jraco11

Reputation: 4586

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;
       }
    }

Upvotes: 12

Herry
Herry

Reputation: 7087

Here is working solution to apply sorting with Relation in room v1.0.0.

I have written in Kotlin

@Query("SELECT * FROM colis INNER JOIN step ON colis.idColis= step.idColis ORDER BY date DESC")
fun getAllColisWithSteps():Flowable<List<ColisWithSteps>>

Here is Java version:

@Query("SELECT * FROM colis INNER JOIN step ON colis.idColis= step.idColis ORDER BY date DESC")
public List<ColisWithSteps> getAllColisWithSteps()

Updated ColisWithSteps class:

public class ColisWithSteps {
        @Embedded
        public ColisEntity colisEntity;

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

Upvotes: 0

Kraigolas
Kraigolas

Reputation: 5590

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.

Upvotes: 3

Yhondri
Yhondri

Reputation: 944

I had the same problem, here is a solution: 

@Query("SELECT * FROM step INNER JOIN colis ON step.idColis = colis.idColis ORDER BY date DESC")
fun getAllColisWithSteps():LiveData<List<ColisWithSteps>>

Here is an example that explain what INNER JOIN does:

To query data from multiple tables, you use INNER JOIN clause. The INNER JOIN clause combines columns from correlated tables.

Suppose you have two tables: A and B.

A has a1, a2, and f columns. B has b1, b2, and f column. The A table links to the B table using a foreign key column named f. For each row in the A table, the INNER JOIN clause compares the value of the f column with the value of the f column in the B table. If the value of the f column in the A table equals the value of the f column in the B table, it combines data from a1, a2, b1, b2, columns and includes this row in the result set.

https://www.sqlitetutorial.net/sqlite-inner-join/

Upvotes: -1

Danail Alexiev
Danail Alexiev

Reputation: 7772

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.

Upvotes: 1

Related Questions