How to filter in one to many relationship with android room db

user_14451581 0
Viewed 1243

I have a User Entity and a Record entity. I want to get a list that shows all users and their record list filtered by record's date. However, I got stuck with not being able to filter the result with conditions.

def room_version = "2.2.5"
implementation "androidx.room:room-runtime:$room_version"

@Entity(tableName = "user_table")
public class User {
    @PrimaryKey public long id;
    public String name;
}

@Entity(tableName = "record_table")
public class Record {
    @PrimaryKey @ColumnInfo(name = "record_id")public long recordId;
    @ColumnInfo(name = "user_id") public long userId;
    public String date;
    @ColumnInfo(name = "action")
    public String action;
}

public class UserWithRecords {
    @Embedded
    protected User user;
    @Relation(
            parentColumn = "id",
            entity = Record.class,
            entityColumn = "user_id"
    )
    protected List<Record> records;
}

And here's all of the query I tried:

//in Dao  
//first try:
@Transaction
@Query("SELECT * FROM user_table LEFT JOIN record_table ON id = user_id WHERE date=:date")
public LiveData<List<UserWithRecords>> getDailyRecord(String date);

//second try:
@Transaction
@Query("SELECT * FROM user_table LEFT JOIN (SELECT * FROM record_table WHERE date=:date) ON id = user_id")
public LiveData<List<UserWithRecords>> getDailyRecord(String date);

//third try:
@Transaction
@Query("SELECT * FROM user_table INNER JOIN record_table ON id = user_id WHERE date=:date")
public LiveData<List<UserWithRecords>> getDailyRecord(String date);


<User>          <Record>
 id | name     record_id | user_id | action | date
------------   ----------------------------------------
 1  | Alice         1    | 1       | walk   |2020-10-05
 2  | Ben           2    | 1       | jog    |2020-10-05
 3  | Chris         3    | 2       | bike   |2020-10-05
                    4    | 1       | walk   |2020-10-14
                    5    | 2       | jog    |2020-10-14

filtering by 2020-10-05 and get results with all queries:
id | name | record_id | action | date
----------------------------------
 1 | Alice|   1       | walk   | 2020-10-05
 1 | Alice|   2       | jog    | 2020-10-05
 1 | Alice|   4       | walk   | 2020-10-14
 2 | Ben  |   3       | bike   | 2020-10-05
 2 | Ben  |   5       | jog    | 2020-10-14
 3 | Chris|------------------------------------->3rd query don't have this row

how do I get something like with this? Is it possible to do it with one query?

id | name | record_id | action | date
-------------------------------------------
 1 | Alice|   1       | walk   | 2020-10-05
 1 | Alice|   2       | jog    | 2020-10-05
 2 | Ben  |   3       | bike   | 2020-10-05
android-room android-sqlite android
1 Answers

Let's look at how @Relation works. There are two stages:

  1. Room executes query you've put in @Query to get users. As usual that query doesn't include any Joins. This query is used to get that part of data that are persisted in the main table (User in your case).
  2. Room executes one more query. For that it looks at @Relation parameters and understands what table it should query next (Record in your case) and what should be join-condition with user's result. It's important that you have no way to intrude into this query-making process. You can't to set filter on Record's fields, for example. Getting result Room transforms it in needed format (fills List of Records).

You have choices:

  1. To turn around relation to set filters on Records table (but with that you'll get flat data without List).
public class UserWithRecords {
    @Embedded
    protected Record record;
    @Relation(
            parentColumn = "user_id",
            entity = User.class,
            entityColumn = "id"
    )
    protected User user;
}

and to change query to:

@Transaction
@Query("SELECT * FROM record_table WHERE date=:date")
public LiveData<List<UserWithRecords>> getDailyRecord(String date);
  1. Not to use @Relation at all, write query with Joins like that you've tried. But then you should manually transform result to needed form in code (looping result and forming List).
user_127184140
Related