Is there a way to control the order of child entity, when using one-to-many relationhips?

Question

According to https://developer.android.com/training/data-storage/room/relationships

We can have one-to-many relationships

public class UserWithPlaylists {
    @Embedded public User user;
    @Relation(
         parentColumn = "userId",
         entityColumn = "userCreatorId"
    )
    public List<Playlist> playlists;
}

@Transaction
@Query("SELECT * FROM User")
public List<UserWithPlaylists> getUsersWithPlaylists();

---

In both entity User and Playlist, we have added a column named sort_key

The purpose is, when we perform query, we can do the following

@Transaction
@Query("SELECT * FROM User order by sort_key")
public List<UserWithPlaylists> getUsersWithPlaylists();

We can control the order of List<UserWithPlaylists>.

But, how about List<Playlist> playlists child entity?

How can we define a custom query for child entity Playlist, so that we can have control over List<Playlist> playlists ordering?

Answer 1

How can we define a custom query for child entity Playlist, so that we can have control over List playlists ordering?

I'm afraid there is no out-of-box way here.

Using @Relation annotation all you have is:

• 5 annotation's parameters (associateBy, entity, entityColumn, parentColumn, projection. None of them has influence on order of child table's items)

• Under the Relation's hood Room uses two queries - first you explicitly write in your DAO

SELECT * FROM User order by sort_key

and another - for fetching data from the child table (based on type of query's result and on @Relation parameters):

SELECT * FROM PlayList WHERE userCreatorId IN (<List of users id from the first query>)

This query is autogenerated and you @Relation annotation has no options to change item's order in it.

Of course, after getting this "unordered" result you can add some post-processing to achieve what you want manually

Answer 2

I'm not sure It is possible to add @Query to a field but what i'm sure is that you can use a particular collection to make the order. These are the steps:

1. Make the Playlist entity emplements Comparable interface, then define the compareTo method following the sort_key attribute.

2. Instead of list in UserWithPlaylists entity, use SortedSet or TreeSet this particular collection returns items ordered using natural order or Comparator.