use orderBy to get embedded relation-defined table from Android Room

Question

For this transaction Query in Android Room :

    @Transaction
    @Query("SELECT * FROM TPAGroup WHERE zuid=:zuid ORDER BY `index`")
    fun getGroupWithSecretsForZuid(zuid: String): List<TPAGroupWithSecrets>

and this as my data class :

data class TPAGroupWithSecrets(
        @Embedded val group: TPAGroup,
        @Relation(
                parentColumn = "groupId",
                entityColumn = "groupId"
        )
        var secrets: MutableList<TPASecrets>

)

I get the TPAGroup in the right order , but TPASecrets have not been ordered ! How can i get both of them in right order , ordered by their index ( which is a column common to both tables ) ?

Answer 1

When @Relation is used, Room gets the related objects, as you have found, without any specific order (the order will likely be by the primary key but that depends upon SQLite's query optimiser).

If you need them ordered you can either

1. sort the returned collection or
2. you can effectively override/bypass the @Relation processing that Room implements.
3. use a single query that orders accordingly and then builds the result from the cartesian product (see bottom for a partial example)

Here's a Working Example of 2

TPAGroup (made up)

@Entity
data class TPAGroup(
    @PrimaryKey
    val groupId: Long? = null,
    val zuid: String,
    val index: Long,
)

TPASecrets (made up)

@Entity
data class TPASecrets(
    @PrimaryKey
    val secretId: Long? = null,
    val groupId: Long,
    val index: Long
)

TPAGroupWithSecrets (uncanged)

data class TPAGroupWithSecrets(
    @Embedded val group: TPAGroup,
    @Relation(
        parentColumn = "groupId",
        entityColumn = "groupId"
    )
    var secrets: MutableList<TPASecrets>
)

An @Dao annotated class

@Dao
interface AllDAO {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(tpaGroup: TPAGroup): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(tpaSecrets: TPASecrets): Long
    @Query("SELECT * FROM TPASecrets WHERE groupId=:groupId ORDER BY `index`;")
    fun getRelatedSecrets(groupId: Long): MutableList<TPASecrets>
    @Query("SELECT * FROM TPAGroup WHERE zuid=:zuid ORDER BY `index`;")
    fun getGroupsForZuid(zuid: String): MutableList<TPAGroup>

    @Transaction
    @Query("")
    fun getGroupWithSecretsForZuid(zuid: String): List<TPAGroupWithSecrets> {
        val rv = ArrayList<TPAGroupWithSecrets>()
        for(t in getGroupsForZuid(zuid)) {
            rv.add(TPAGroupWithSecrets(t,getRelatedSecrets(t.groupId!!)))
        }
        // rv.sortBy { .... }
        return rv
    }
}

• Note the @Query's and especially the last which bypasses Rooms @Relation handling (i.e. the TPAGroupWithSecrets are built outside of room)

an @Database annotated class to tie all the Room stuff together TheDatabase

@Database(entities = [TPAGroup::class,TPASecrets::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,"the_database.db")
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

• set to run on the main thread for convenience and brevity

Finally putting it into action in an Activity:-

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()

        dao.insert(TPAGroup(groupId = 1,zuid = "Group1", index = 10))
        dao.insert(TPAGroup(groupId = 2, zuid = "Group1", index = 9))
        dao.insert(TPAGroup(groupId = 3, zuid = "Group1", index = 11))

        dao.insert(TPASecrets(1000,1,5))
        dao.insert(TPASecrets(1010,groupId = 1, index = 4))
        dao.insert(TPASecrets(1020,1,3))
        dao.insert(TPASecrets(2000,2,5))
        dao.insert(TPASecrets(2010,2,6))
        dao.insert(TPASecrets(2020,2,7))
        dao.insert(TPASecrets(2030,2,1))
        dao.insert(TPASecrets(2040,2,2))
        dao.insert(TPASecrets(2050,2,3))
        dao.insert(TPASecrets(3000,3,1))
        dao.insert(TPASecrets(3010,3,0))

        for(tgws in dao.getGroupWithSecretsForZuid("Group1")) {
            Log.d("DBINFO","TPAGroup is ${tgws.group.groupId} Index is ${tgws.group.index}. It has ${tgws.secrets.size} Secrets, they are :-")
            for (s in tgws.secrets) {
                Log.d("DBINFO","\tSecret is ${s.secretId} Index is ${s.index}")
            }
        }
    }
}

The result output to the log (noting that the data has been purposefully inserted to demonstrate sorting):-

2022-04-13 21:37:29.220 D/DBINFO: TPAGroup is 2 Index is 9. It has 6 Secrets, they are :-
2022-04-13 21:37:29.220 D/DBINFO:   Secret is 2030 Index is 1
2022-04-13 21:37:29.220 D/DBINFO:   Secret is 2040 Index is 2
2022-04-13 21:37:29.220 D/DBINFO:   Secret is 2050 Index is 3
2022-04-13 21:37:29.220 D/DBINFO:   Secret is 2000 Index is 5
2022-04-13 21:37:29.220 D/DBINFO:   Secret is 2010 Index is 6
2022-04-13 21:37:29.220 D/DBINFO:   Secret is 2020 Index is 7
2022-04-13 21:37:29.221 D/DBINFO: TPAGroup is 1 Index is 10. It has 3 Secrets, they are :-
2022-04-13 21:37:29.221 D/DBINFO:   Secret is 1020 Index is 3
2022-04-13 21:37:29.221 D/DBINFO:   Secret is 1010 Index is 4
2022-04-13 21:37:29.221 D/DBINFO:   Secret is 1000 Index is 5
2022-04-13 21:37:29.221 D/DBINFO: TPAGroup is 3 Index is 11. It has 2 Secrets, they are :-
2022-04-13 21:37:29.221 D/DBINFO:   Secret is 3010 Index is 0
2022-04-13 21:37:29.221 D/DBINFO:   Secret is 3000 Index is 1

• So TPAGroups are sorted according to the value if the Index (2 with index 9 is first, 3 with index 10 2nd and 3 with index 11 3rd)
• You can easily see that the Secrets are ordered according to thier index rather than their primary key secrteId

---

Partial Example of option 3

A query such as

    SELECT * FROM TPAGroup JOIN TPASecrets ON TPASecrets.groupid = TPAGroup.groupid ORDER BY TPAGroup.`index` ASC, TPASecrets.`index`;

Would produce data (using the data loaded by the working example):-

You would then need to have a POJO to receive the data. However there's an issue with duplicate columns names index and groupid so the query is more complicated requiring aliases (AS) e.g. you could use

SELECT TPAGroup.*, TPASecrets.secretId, TPASecrets.`index` AS secretIndex FROM TPAGroup JOIN TPASecrets ON TPASecrets.groupid = TPAGroup.groupid ORDER BY TPAGroup.`index` ASC, TPASecrets.`index`;

So the duplicated groupid (which would always have the same value in both) is dropped from TPASecrets and the TPASecrets column is aliased/renamed as secretsIndex. Obviously the POJO would have to cater for this.

You then have to build each TPAGroup with it's TPASecrets by looping through the results.

Not done/shown as most tend to opt for option 1 or 2 and tend to baulk at option 3. However, option 3 is probably the more efficient as there is just the single query (no need for @Transaction).