title: How to filter in one to many relationship with android room db
body: <p>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.</p>
  <pre><code>def room_version = &quot;2.2.5&quot;
  implementation &quot;androidx.room:room-runtime:$room_version&quot;
  </code></pre>
  <pre><code>@Entity(tableName = &quot;user_table&quot;)
  public class User {
      @PrimaryKey public long id;
      public String name;
  }
  
  @Entity(tableName = &quot;record_table&quot;)
  public class Record {
      @PrimaryKey @ColumnInfo(name = &quot;record_id&quot;)public long recordId;
      @ColumnInfo(name = &quot;user_id&quot;) public long userId;
      public String date;
      @ColumnInfo(name = &quot;action&quot;)
      public String action;
  }
  
  public class UserWithRecords {
      @Embedded
      protected User user;
      @Relation(
              parentColumn = &quot;id&quot;,
              entity = Record.class,
              entityColumn = &quot;user_id&quot;
      )
      protected List&lt;Record&gt; records;
  }
  
  </code></pre>
  <p>And here's all of the query I tried:</p>
  <pre><code>//in Dao  
  //first try:
  @Transaction
  @Query(&quot;SELECT * FROM user_table LEFT JOIN record_table ON id = user_id WHERE date=:date&quot;)
  public LiveData&lt;List&lt;UserWithRecords&gt;&gt; getDailyRecord(String date);
  
  //second try:
  @Transaction
  @Query(&quot;SELECT * FROM user_table LEFT JOIN (SELECT * FROM record_table WHERE date=:date) ON id = user_id&quot;)
  public LiveData&lt;List&lt;UserWithRecords&gt;&gt; getDailyRecord(String date);
  
  //third try:
  @Transaction
  @Query(&quot;SELECT * FROM user_table INNER JOIN record_table ON id = user_id WHERE date=:date&quot;)
  public LiveData&lt;List&lt;UserWithRecords&gt;&gt; getDailyRecord(String date);
  
  </code></pre>
  <pre><code>&lt;User&gt;          &lt;Record&gt;
   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|-------------------------------------&gt;3rd query don't have this row 
  </code></pre>
  <p>how do I get something like with this? Is it possible to do it with one query?</p>
  <pre><code>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   
  </code></pre>