title: How to obtain count of parent foreign key from child table using Room
body: <p>I have a parent entity Collection:</p>
  
  <pre><code>@Entity(tableName = "collections")
  public class Collection implements Serializable {
  
      @PrimaryKey
      @NonNull
      public String id;
  
      public String collTitle;
  
      public int isFavorite;
  
      public int wordCount;
  
      Collection(String collTitle, int isFavorite, int wordCount) {
          this(UUID.randomUUID().toString(), collTitle, isFavorite, wordCount);
      }
  
      @Ignore
      Collection(@NonNull String id, String collTitle, int isFavorite, int wordCount) {
          this.id = id;
          this.collTitle = collTitle;
          this.isFavorite = isFavorite;
          this.wordCount = wordCount;
      }
  
  }
  </code></pre>
  
  <p>and a child entity Word:</p>
  
  <pre><code>@Entity(tableName = "words",
          foreignKeys = @ForeignKey(
                  entity = Collection.class,
                  parentColumns = "id",
                  childColumns = "collId",
                  onDelete = SET_DEFAULT),
          indices = @Index("collId"))
  public class Word implements Serializable {
  
      @PrimaryKey
      @NonNull
      public final String id;
  
      public String wordTitle;
  
      public String collId;
  
      public String pageNum;
  
      public int isFavorite;
  
      public String wordNotes;
  
      @Ignore
      Word(String wordTitle, String collId, String pageNum, int isFavorite, String wordNotes) {
          this(UUID.randomUUID().toString(), wordTitle, collId, pageNum, isFavorite, wordNotes);
      }
  
      Word(@NonNull String id, String wordTitle, String collId, String pageNum, int isFavorite,
           String wordNotes) {
          this.id = id;
          this.wordTitle = wordTitle;
          this.collId = collId;
          this.pageNum = pageNum;
          this.isFavorite = isFavorite;
          this.wordNotes = wordNotes;
      }
  }
  </code></pre>
  
  <p>At the time of inserting a new Collection, I give wordCount as 0 because every new collection would have 0 words associated with it. However, when inserting a new Word, I have to choose one collection from the defined collections and use its 'id' as 'collId' (Foreign Key).</p>
  
  <p>Now I need to retrieve a list of Collection objects with 'wordCount' field containing the actual number of words associated with each Collection object. As already mentioned, 'wordCount' field is assigned a value of 0 at the time of insertion of a Collection object.</p>
  
  <p>I just hope I made my question clear. Kindly suggest a query that serves my purpose. And I don't want to have the result in the form of a new POJO class like, for example, CollectionsAndWords.</p>
  
  <p>Any help would be a great help!</p>