Skip to content
Advertisement

How to query and filter relation entity in room database

For example:

User:

Post:

Data

if i use SELECT * FROM user i got the desire data(a user and all posts), but how can i filter the post for a specific type, like WHERE post.type = 'sth' is that possible?

Advertisement

Answer

but how can i filter the post for a specific type,

It depends on exactly what you want to filter. You may want Data objects that match the filter but with all the posts (irrespactive of thetype), in which case you could use:-

  • where you would use something like var mylist = yourdao.getAllDataFiltered("sth")

However, as the id columns of post and user are both named id then the ambiguity interferes (the user id becomes the post id and thus no underlying post objects are extracted).

If you change PostEntity to be :-

Then the ambiguity is removed and the Data objects returned include ALL of the posts for the respective Users that have a post type of sth.

If you only want Data objects to be returned that only have the filtered posts, then you have to bypass Room’s technique of returning FULL/COMPLETE related objects.

If you make the @Dao class an abstract class rather than an interface then you with an @Query such as :-

along with a function such as :-

This filters the Data objects return but then discards the entire list of PostEntity (i.e. every post irrespective of the filter) and then applies the filtered posts.

If you wanted all Users but with only posts that match (and therefore potentially an empty list of posts) then you could have a function such as :-

i.e. no filtering is applied to the users but only to the posts.

Using the above then (noting the use use of the changed column name (postid instead of id)) then consider the following (a pretty standard @Database class has been used):-

The the results output to the log are:-

No filtering at all:-

Only the User’s are filtered as Room gets all the posts for the users :-

Fully filtered :-

All users but with filtered posts

If the PostEntity column is changed back to being id then the results are

:-

  • i.e. Note how the user is the post’s id and hence no underlying posts.
  • you could use @Embedded(prefix = "a_suitable_prefix"). However, you then have to alter the column names of the User (the prefixed table) in the query using AS, far simpler to use a non-ambiguous column name.
  • the 4th, returning all users but with only the filtered posts is not affected as it doesn’t use the Data POJO which is where the ambiguity results in the user id being the post id.
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement