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

user_72437 0
Viewed 946

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?

android-room android java
3 Answers

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

user_127184140

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.

user_79241100

You can implement this functionality in two ways :

1) You could use the Hibernate-specific @Sort annotation to perform in-memory (i.e. not in the database) sorting, using natural sorting or sorting using a Comparator you supply. For example, assume I have an MyComparator helper class that implements Comparator. Helper class can help you sort by sort_key. You could change Playlist collection like this:

@org.hibernate.annotations.Sort(type = SortType.COMPARATOR, comparator = MyComparator)
public Set<Playlist> playlists;

@Sort will perform sorting in-memory once the collection has been retrieved from the database.

2) You can use hibernate's @OrderBy annotation, which lets you specify a SQL fragment describing how to perform the sort. 

@org.hibernate.annotations.OrderBy("sort_key")
 public Set<Playlist> playlists;

Hope that helps..!!

user_51448290
Related