如何过滤与Android Room DB的一对多关系 [英] How to filter in one to many relationship with android room db

问题描述

我有一个用户实体和一个记录实体.我想要一个列表，该列表显示所有用户及其记录列表，这些记录按记录的日期进行过滤.但是，我受困于无法根据条件过滤结果.

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   

推荐答案

让我们看看 @Relation 的工作方式.有两个阶段:

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

1. Room执行您输入 @Query 中的查询以获取用户.像往常一样，该查询不包含任何联接.此查询用于获取保留在主表中的那部分数据(在您的情况下为 User ).
2. 客房再执行一次查询.为此，它查看 @Relation 参数，并了解下一步应查询的表(在您的情况下为 Record )，以及与用户结果关联的条件是什么.重要的是，您不能介入这个查询过程.例如，您不能在 Record 的字段上设置过滤器.获取结果Room会将其转换为所需的格式(填充 Records 的列表).

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).

您可以选择:

1. 扭转关系以设置 Records 表上的过滤器(但如果没有List，您将获得平面数据).

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;
}

并将查询更改为:

@Transaction
@Query("SELECT * FROM record_table WHERE date=:date")
public LiveData<List<UserWithRecords>> getDailyRecord(String date);

1. 完全不要使用 @Relation ，像您尝试过的那样用Joins编写查询.但是，然后您应该在代码中手动将结果转换为所需的形式(循环结果和形成List).

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).

这篇关于如何过滤与Android Room DB的一对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！