Kotlin Room one to many relationship how to order by SQL?-CodePudding

I have two @Entitys:

User.kt

@Entity
    data class User(
        @PrimaryKey val userId: Long,
        val name: String,
        val age: Int
    )

Playlist.kt

    @Entity
    data class Playlist(
        val userCreatorId: Long,
        val playlistName: String,
        @ColumnInfo(name = "created_at") val createdAt: Long
    ) {
        @PrimaryKey(autoGenerate = true)  @ColumnInfo(name = "play_list_id") var playlistId: Long = 0
    }

Added one to many relationship by @Relation

 data class UserWithPlaylists(
        @Embedded val user: User,
        @Relation(
              parentColumn = "userId",
              entityColumn = "userCreatorId"
        )
        val playlists: List<Playlist>
    )

Then in Dao:

@Transaction
    @Query("SELECT * FROM User")
    fun getUsersWithPlaylists(): List<UserWithPlaylists>

All works good but how to sort UserWithPlaylists by createAt?

What's the default order of the fun getUsersWithPlaylists(): List<UserWithPlaylists>. I know sort the result List<UserWithPlaylists> sortedByDescending or sortedBy works. How to achieve in Dao layer by SQL? Thanks.

CodePudding user response：

What's the default order of the fun getUsersWithPlaylists(): List.

I believe that it's by the primary key in ascending order.

How to achieve in Dao layer by SQL?

Using @Relation you can't. @Relation invokes an underlying query that retrieves the related items.

You can, however bypass @Relation by extracting the parent(s) via a query and then invoking your own sub query to get the children.

Here's a simple example of bypassing the @Relation allowing for the PlayList to be sorted by playlistName, then created_at and the 4 permutations.

Note that an abstract class rather then interface is used, allowing the non abstract function to be utilised.

The Dao (allDao):-

@Dao
abstract class AllDao {

    @Insert
    abstract fun insert(user: User): Long
    @Insert
    abstract fun insert(playlist: Playlist): Long

    @Query("SELECT * FROM playlist WHERE userCreatorId=:userCreatorId ORDER BY playlistName ASC, created_at ASC")
    abstract fun getPlayListsAscendingCreateAtAscending(userCreatorId: Long): List<Playlist>
    @Query("SELECT * FROM playlist WHERE userCreatorId=:userCreatorId ORDER BY playlistName DESC, created_at ASC")
    abstract fun getPlayListsDescendingCreatedAtAscending(userCreatorId: Long): List<Playlist>
    @Query("SELECT * FROM playlist WHERE userCreatorId=:userCreatorId ORDER BY playlistName ASC, created_at DESC")
    abstract fun getPlayListsAscendingCreatedAtDescending(userCreatorId: Long): List<Playlist>
    @Query("SELECT * FROM playlist WHERE userCreatorId=:userCreatorId ORDER BY playlistName DESC, created_at DESC")
    abstract fun getPlayListsDescendingCreatedAtDescending(userCreatorId: Long): List<Playlist>

    @Transaction
    @Query("")
    fun getPlaylistWithSortedUsers(sortPlayListAscending: Boolean, sortCreatedByAscending: Boolean): List<UserWithPlaylists> {
        val rv: ArrayList<UserWithPlaylists> = arrayListOf()
        val userList = getUserList()
        for (u: User in userList) {
            if (sortPlayListAscending && sortCreatedByAscending) {
                rv.add(UserWithPlaylists(u,getPlayListsAscendingCreateAtAscending(u.userId!!)))
            }
            if (!sortPlayListAscending && sortCreatedByAscending){
                rv.add(UserWithPlaylists(u,getPlayListsDescendingCreatedAtAscending(u.userId!!)))
            }
            if (sortPlayListAscending && ! sortCreatedByAscending) {
                rv.add(UserWithPlaylists(u,getPlayListsAscendingCreatedAtDescending(u.userId!!)))
            }
            if (!sortPlayListAscending && !sortCreatedByAscending) {
                rv.add(UserWithPlaylists(u,getPlayListsDescendingCreatedAtDescending(u.userId!!)))
            }
        }
        return rv
    }
}

Note that this avoids the complexities of using a single query that includes CASE WHEN THEN ELSE END construct(s) for applying the sort order, by utilising 4 simple underlying queries.
the dummy @Query("") is just to allow the @Transaction annotation

Working Example/Demo

Here's a demonstration of the above:-

    db = TheDatabase.getInstance(this)
    dao = db.getAllDao()

    val user1Id = dao.insert(User(name = "User1", age = 20))
    val user2Id = dao.insert(User(name = "Fred", age = 19))
    val user3Id = dao.insert(User(name = "Mary", age = 21))

    dao.insert(playlist = Playlist(user1Id,"M",System.currentTimeMillis()))
    dao.insert(playlist = Playlist(user1Id,"N",System.currentTimeMillis()))
    dao.insert(playlist = Playlist(user1Id,"A",System.currentTimeMillis()))
    dao.insert(playlist = Playlist(user1Id,"Z",System.currentTimeMillis()))
    dao.insert(playlist = Playlist(user1Id,"B",System.currentTimeMillis()))
    dao.insert(playlist = Playlist(user1Id,"B",System.currentTimeMillis()))
    dao.insert(playlist = Playlist(user1Id,"B",System.currentTimeMillis()))

    dao.insert(playlist = Playlist(user2Id,"1",System.currentTimeMillis()))
    dao.insert(playlist = Playlist(user2Id,"2",System.currentTimeMillis()))
    dao.insert(playlist = Playlist(user2Id,"3",System.currentTimeMillis()))
    dao.insert(playlist = Playlist(user2Id,"9",System.currentTimeMillis()))
    dao.insert(playlist = Playlist(user2Id,"8",System.currentTimeMillis()))
    dao.insert(playlist = Playlist(user2Id,"3",System.currentTimeMillis()))
    dao.insert(playlist = Playlist(user2Id,"3",System.currentTimeMillis()))



    for(u: UserWithPlaylists in dao.getPlaylistWithSortedUsers(true,false)) {
        Log.d("DBINFO","User is ${u.user.name}")
        for(p: Playlist in u.playlists) {
            Log.d("DBINFO","\tPlaylist is ${p.playlistName} crtd ${p.createdAt}")
        }
    }
    for(u: UserWithPlaylists in dao.getPlaylistWithSortedUsers(false,true)) {
        Log.d("DBINFO","User is ${u.user.name}")
        for(p: Playlist in u.playlists) {
            Log.d("DBINFO","\tPlaylist is ${p.playlistName} crtd ${p.createdAt}")
        }
    }

Which results in the following in the log :-

2021-11-25 09:20:34.717 D/DBINFO: User is User1
2021-11-25 09:20:34.717 D/DBINFO:   Playlist is A crtd 1637792434681
2021-11-25 09:20:34.717 D/DBINFO:   Playlist is B crtd 1637792434694
2021-11-25 09:20:34.717 D/DBINFO:   Playlist is B crtd 1637792434692
2021-11-25 09:20:34.718 D/DBINFO:   Playlist is B crtd 1637792434687
2021-11-25 09:20:34.718 D/DBINFO:   Playlist is M crtd 1637792434678
2021-11-25 09:20:34.718 D/DBINFO:   Playlist is N crtd 1637792434680
2021-11-25 09:20:34.718 D/DBINFO:   Playlist is Z crtd 1637792434684
2021-11-25 09:20:34.718 D/DBINFO: User is Fred
2021-11-25 09:20:34.718 D/DBINFO:   Playlist is 1 crtd 1637792434695
2021-11-25 09:20:34.718 D/DBINFO:   Playlist is 2 crtd 1637792434697
2021-11-25 09:20:34.718 D/DBINFO:   Playlist is 3 crtd 1637792434707
2021-11-25 09:20:34.718 D/DBINFO:   Playlist is 3 crtd 1637792434705
2021-11-25 09:20:34.718 D/DBINFO:   Playlist is 3 crtd 1637792434698
2021-11-25 09:20:34.718 D/DBINFO:   Playlist is 8 crtd 1637792434703
2021-11-25 09:20:34.718 D/DBINFO:   Playlist is 9 crtd 1637792434701
2021-11-25 09:20:34.718 D/DBINFO: User is Mary


2021-11-25 09:20:34.723 D/DBINFO: User is User1
2021-11-25 09:20:34.723 D/DBINFO:   Playlist is Z crtd 1637792434684
2021-11-25 09:20:34.723 D/DBINFO:   Playlist is N crtd 1637792434680
2021-11-25 09:20:34.723 D/DBINFO:   Playlist is M crtd 1637792434678
2021-11-25 09:20:34.723 D/DBINFO:   Playlist is B crtd 1637792434687
2021-11-25 09:20:34.723 D/DBINFO:   Playlist is B crtd 1637792434692
2021-11-25 09:20:34.723 D/DBINFO:   Playlist is B crtd 1637792434694
2021-11-25 09:20:34.723 D/DBINFO:   Playlist is A crtd 1637792434681
2021-11-25 09:20:34.723 D/DBINFO: User is Fred
2021-11-25 09:20:34.723 D/DBINFO:   Playlist is 9 crtd 1637792434701
2021-11-25 09:20:34.723 D/DBINFO:   Playlist is 8 crtd 1637792434703
2021-11-25 09:20:34.723 D/DBINFO:   Playlist is 3 crtd 1637792434698
2021-11-25 09:20:34.724 D/DBINFO:   Playlist is 3 crtd 1637792434705
2021-11-25 09:20:34.724 D/DBINFO:   Playlist is 3 crtd 1637792434707
2021-11-25 09:20:34.724 D/DBINFO:   Playlist is 2 crtd 1637792434697
2021-11-25 09:20:34.724 D/DBINFO:   Playlist is 1 crtd 1637792434695
2021-11-25 09:20:34.724 D/DBINFO: User is Mary