Room using the @Relation annotation - one to many relationship where clause child relationship

Question

Using the @Relation annotation. I can query a one to many relationship using the following:

 @Dao
 public interface PostDao {
        @Query("SELECT * FROM post")
        List<PostWithComments> getPostWithComments();
 }

Here are the entities

@Entity
public class Post {
    @PrimrayKey
    private int id;
    private String title;
    private String content;
}

@Entity
public class Comment {
    @PrimrayKey
    private int id;
    private int post_id;
    private String content;
    private String status;
}


public class PostWithComments {
    @Embedded
    public Post post;

    @Relation(parentColumn = "id", entityColumn = "post_id", entity = Comment.class)
    public List<Comment> comments;

}

I would like to get all posts that have a comment with status = approved but I'm not exactly sure how room handles this. I tried the following:

 @Dao
 public interface PostDao {
        @Query("SELECT * FROM post INNER JOIN comment ON post.id = comment.post_id WHERE comment.status = 'approved'")
        List<PostWithComments> getPostWithComments();
 }

I got duplicates in the results. Each post is there multiple times in List<PostWithComments> results.

Update:

After reading the generated code at PostDao_Impl.java it seems that Room is doing a sub query to fetch the relation.

First, it executes the query in the @Query annotation from the getPostWithComments method, and then it generates a sub query for the relation to populate List<Comment>

SELECT id, post_id, title, content FROM comment WHERE post_id IN ( and some other logic, and there doesn't seem to be a way to modify the generated sub query.

Is there another way to do this?

Answer 1

With @Relation, you can use @DatabaseView

@DatabaseView("SELECT * FROM comments WHERE status = 'approved'")
public class ApprovedComment {
  @Embedded
  Comment comment;
}

PostWithComments class

public class PostWithComments {
    @Embedded
    public Post post;

    @Relation(parentColumn = "id", entityColumn = "post_id", entity = ApprovedComment.class)
    public List<ApprovedComment> comments;

}

DAO

@Dao
public interface PostWithCommentsDao {
       @Query("SELECT * FROM post")
       List<PostWithComments> getPostWithComments();
}

You also need to update your Database class that extends RoomDatabase and you may need to update the version.

@Database(entities = {Post.class, Comment.class}, views = {ApprovedComment.class}, version = 1)
public abstract class MyDatabase extends RoomDatabase