代码之家 › 专栏 › 技术社区 › Shahood ul Hassan babadaba

如何从使用房间的子表中获取父外键的计数

android-room foreign-keys sqlite join android

Shahood ul Hassan babadaba · 技术社区 · 6 年前

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

}

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

我只是希望我能澄清我的问题。请提出符合我要求的问题。我不希望得到像Collections和Words这样的新pojo类形式的结果。

1 回复 | 直到 6 年前

Shahood ul Hassan babadaba 6 年前

@ColumnInfo(name = "word_count")
    public int wordCount;

Collection(String collTitle, int isFavorite) {
        this(UUID.randomUUID().toString(), collTitle, isFavorite);
    }

@Ignore
Collection(@NonNull String id, String collTitle, int isFavorite) {
        this.id = id;
        this.collTitle = collTitle;
        this.isFavorite = isFavorite;
   }

@Query("SELECT collections.id, collections.collTitle, collections.isFavorite, " +
            "COUNT(words.collId) as word_count " +
            "FROM collections " +
            "LEFT JOIN words " +
            "ON collections.id = words.collId " +
            "GROUP BY collections.id " +
            "ORDER BY collections.collTitle")
List<Collection> findAllCollsWithCount();

左联用正确的方式为我做了所有的把戏。

然而,目前,上述解决方案完全符合我的目的,所以这就是答案。它可能会帮助有同样问题的人。