room relations inserting deleting updating and sorting

Question

I have two issues both based around the same issue with room relations,

The first issue is with updating groups, I have many Sentences (POJO) and many groups (POJO), groups can have many sentences and sentences can have many groups (M:N relation) they are related by a GroupsWithSentences (POJO),

public class GroupsWithSentences implements Visitable {

    @Embedded
    public Group group;

    @Relation(
            parentColumn = "groupId",
            entity = Sentence.class,
            entityColumn = "sentenceId",
            associateBy = @Junction(value =
                    GroupIdsWithSentenceIdsJoin.class,
                    parentColumn = "groupId",
                    entityColumn = "sentenceId"
            )
    )
}

The sentences are shown in a RecyclerView. I can remove or move (more on move later) sentences in the RecyclerView and then when I'm happy I save the group, this is an insert with OnConflictStrategy.REPLACE, but when I save the group the sentences I've removed aren't removed from the group, I can see why this is and could manually fix it but it seems excessive to delete all my joins between the sentence and group and then insert them all again minus the ones that were removed especially for the ones that weren't removed (I don't want to remove each sentence in the join table when its removed from the RecyclerView as I'd like to be able to cancel any changes made) so my question is, is there a better way to update insert and delete relations in room? I'd ideally like to just have my GroupsWithSentences be an entity and if I update it, that would update my join table but apparently room doesn't like this.

My other issue is with the ordering, is there a way to give the relations an ORDER BY parameter for its query I've read that you cant but none of the questions are post Room v2, currently I'm saving a list of the order and using that after the query, otherwise the order is always different

The lack of code is for brevity I can add anything on request

Added group and sentence models, so much for brevity...

GROUP MODEL

@Entity
public class Group {

    @NotNull
    @PrimaryKey()
    @ColumnInfo(name = Constants.GROUP_ID, index = true)
    private String groupId;

    @ColumnInfo(name = Constants.GROUP_NAME)
    private String groupName;

    @ColumnInfo(name = Constants.SENTENCE_WORD_DESCRIPTION)
    private String sentenceWordDescription;

    @ColumnInfo(name = Constants.SENTENCE_WORD_TYPE)
    private String sentenceWordType;

    public Group() {

    }

    public String getSentenceWordDescription() {
        return sentenceWordDescription;
    }

    public void setSentenceWordDescription(String sentenceWordDescription) {
        this.sentenceWordDescription = sentenceWordDescription;
    }

    public String getSentenceWordType() {
        return sentenceWordType;
    }

    public void setSentenceWordType(String sentenceWordType) {
        this.sentenceWordType = sentenceWordType;
    }

    @Ignore
    public Group(@NotNull String groupId, String groupName, String sentenceWordType, String sentenceWordDescription) {
        this.groupId = groupId;
        this.groupName = groupName;
        this.sentenceWordType = sentenceWordType;
        this.sentenceWordDescription = sentenceWordDescription;
    }

    @NotNull
    public String getGroupId() {
        return groupId;
    }

    public void setGroupId(@NotNull String groupId) {
        this.groupId = groupId;
    }

    public String getGroupName() {
        return groupName;
    }

    public void setGroupName(String groupName) {
        this.groupName = groupName;
    }

    @Override
    public String toString() {
        return "Group{" +
                "groupId='" + groupId + '\'' +
                ", groupName='" + groupName + '\'' +
                ", sentenceWordDescription='" + sentenceWordDescription + '\'' +
                ", sentenceWordType='" + sentenceWordType + '\'' +
                '}';
    }
}

SENTENCE MODEL

@Entity
public class Sentence implements Parcelable, Comparable<Sentence>, Visitable {

    @NonNull
    @PrimaryKey()
    @ColumnInfo(name = Constants.SENTENCE_ID, index = true)
    private String sentenceId;

    @ColumnInfo(name = Constants.SENTENCES)
    @TypeConverters({StringListConverter.class})
    //If this is null we have a single word sentences
    private List<String> sentences;

    @ColumnInfo(name = Constants.TIME_STAMP)
    private String createdDate;

    @ColumnInfo(name = Constants.UPDATED_DATE)
    private String lastUpdatedDate;

    @ColumnInfo(name = Constants.SENTENCE_IMAGE_SMALL)
    @TypeConverters({StringListConverter.class})
    private List<String> smallImages;

    @ColumnInfo(name = Constants.SENTENCE_IMAGE_LARGE)
    @TypeConverters({StringListConverter.class})
    private List<String> largeImages;

    @ColumnInfo(name = Constants.SENTENCE_WORD_TYPE)
    private String sentenceWordType;

    @ColumnInfo(name = Constants.SENTENCE_IMAGE_TYPE)
    private String sentenceImageType;

    @ColumnInfo(name = Constants.SENTENCE_WORD_DESCRIPTION)
    private String sentenceWordDescription;

    @ColumnInfo(name = Constants.SENTENCE_WORD)
    private String sentenceWords;

    @ColumnInfo(name = Constants.SENTENCE_WORD_SOUND)
    private String sentenceWordSound;

    @ColumnInfo(name = Constants.SENTENCE_WORD_FAV)
    private boolean isFavourite;

    @ColumnInfo(name = Constants.SENTENCE_CLICKED)
    private int clicks;

    @ColumnInfo(name = Constants.SENTENCE_KEY_STAGE)
    private int keyStage;

    // TODO: change this to userEdited and add a firebase image url
    @ColumnInfo(name = Constants.SENTENCE_USER_ADDED)
    private boolean isUserAdded;

    @ColumnInfo(name = Constants.SENTENCE_PREDICTIONS)
    @TypeConverters({StringListConverter.class})
    private List<String> predictions;

    public Sentence() {

    }

    @Ignore
    public Sentence(@NotNull String sentenceId, List<String> sentences, String createdDate,
                    String lastUpdatedDate, List<String> smallImages, List<String> largeImages,
                    String sentenceWordType,
                    String sentenceImageType, String sentenceWordDescription, String sentenceWords,
                    String sentenceWordSound, boolean isFavourite,
                    int clicks, int keyStage, boolean isUserAdded, List<String> predictions) {
        this.sentenceId = sentenceId;
        this.sentences = sentences;
        this.createdDate = createdDate;
        this.lastUpdatedDate = lastUpdatedDate;
        this.smallImages = smallImages;
        this.largeImages = largeImages;
        this.sentenceWordType = sentenceWordType;
        this.sentenceImageType = sentenceImageType;
        this.sentenceWordDescription = sentenceWordDescription;
        this.sentenceWords = sentenceWords;
        this.sentenceWordSound = sentenceWordSound;
        this.isFavourite = isFavourite;
        this.clicks = clicks;
        this.keyStage = keyStage;
        this.isUserAdded = isUserAdded;
        this.predictions = predictions;
    }

    @Ignore
    public Sentence(Sentence thisSentence, Sentence otherSentence) {
        this.sentenceId = thisSentence.getSentenceId();
        this.createdDate = otherSentence.getCreatedDate();
        this.sentences = otherSentence.sentences;
        this.smallImages = thisSentence.getSmallImages();
        this.largeImages = thisSentence.getLargeImages();
        this.sentenceWordType = thisSentence.getSentenceWordType();
        this.sentenceImageType = thisSentence.getSentenceWordType();
        this.sentenceWordDescription = thisSentence.getSentenceWordDescription();
        this.sentenceWords = thisSentence.getSentenceWords();
        this.sentenceWordSound = thisSentence.getSentenceWordSound();
        this.isFavourite = thisSentence.isFavourite();
        this.clicks = thisSentence.getClicks();
        this.keyStage = thisSentence.getKeyStage();
        this.isUserAdded = thisSentence.isUserAdded();
        this.predictions = thisSentence.getPredictions();
        this.lastUpdatedDate = thisSentence.getLastUpdatedDate();
    }

    @Ignore
    public Sentence(SentenceGroup sentence, List<Sentence> sentenceList) {
        this.sentenceId = sentence.getSentenceId();
        this.createdDate = sentence.getCreatedDate();
        this.lastUpdatedDate = sentence.getLastUpdatedDate();
        this.smallImages = sentence.getSmallImages();
        this.largeImages = sentence.getLargeImages();
        this.sentenceWordType = sentence.getSentenceWordType();
        this.sentenceImageType = sentence.getSentenceImageType();
        this.sentenceWordDescription = sentence.getSentenceWordDescription();
        this.sentenceWords = sentence.getSentenceWords();
        this.sentenceWordSound = sentence.getSentenceWordSound();
        this.isFavourite = sentence.isFavourite();
        this.clicks = sentence.getClicks();
        this.keyStage = sentence.getKeyStage();
        this.isUserAdded = sentence.isUserAdded();
        this.predictions = sentence.getPredictions();
        List<String> idList = new ArrayList<>();
        if (sentenceList != null){
            for (Sentence s : sentenceList){
                idList.add(s.getSentenceId());
            }
        }
        this.sentences = idList;
    }

    @Ignore
    public Sentence(List<String> cardImageSmall, List<String> cardImageLarge,Sentence card) {
        this.sentenceId = card.getSentenceId();
        this.smallImages = cardImageSmall;
        this.largeImages = cardImageLarge;
        this.sentences = card.getSentences();
        this.sentenceWordType = card.getSentenceWordType();
        this.sentenceWordDescription = card.getSentenceWordDescription();
        this.sentenceWords = card.getSentenceWords();
        this.sentenceWordSound = card.getSentenceWordSound();
        this.isFavourite = card.isFavourite();
        this.clicks = card.getClicks();
        this.keyStage = card.getKeyStage();
        this.predictions = card.getPredictions();
        this.isUserAdded = card.isUserAdded();
        this.createdDate = card.getCreatedDate();
        this.lastUpdatedDate = card.getLastUpdatedDate();
    }

    protected Sentence(Parcel in) {
        sentenceId = Objects.requireNonNull(in.readString());
        sentences = in.createStringArrayList();
        createdDate = in.readString();
        smallImages = in.createStringArrayList();
        largeImages = in.createStringArrayList();
        sentenceWordType = in.readString();
        sentenceImageType = in.readString();
        sentenceWordDescription = in.readString();
        sentenceWords = in.readString();
        sentenceWordSound = in.readString();
        isFavourite = in.readByte() != 0;
        clicks = in.readInt();
        keyStage = in.readInt();
        isUserAdded = in.readByte() != 0;
        predictions = in.createStringArrayList();
        lastUpdatedDate = in.readString();
    }

    @Override
    public void writeToParcel(Parcel dest, int flags) {
        dest.writeString(sentenceId);
        dest.writeStringList(sentences);
        dest.writeString(createdDate);
        dest.writeString(lastUpdatedDate);
        dest.writeStringList(smallImages);
        dest.writeStringList(largeImages);
        dest.writeString(sentenceWordType);
        dest.writeString(sentenceImageType);
        dest.writeString(sentenceWordDescription);
        dest.writeString(sentenceWords);
        dest.writeString(sentenceWordSound);
        dest.writeByte((byte) (isFavourite ? 1 : 0));
        dest.writeInt(clicks);
        dest.writeInt(keyStage);
        dest.writeByte((byte) (isUserAdded ? 1 : 0));
        dest.writeStringList(predictions);
    }


    public static Creator<Sentence> getCREATOR() {
        return CREATOR;
    }

    @Override
    public int describeContents() {
        return 0;
    }

    public static final Creator<Sentence> CREATOR = new Creator<Sentence>() {
        @Override
        public Sentence createFromParcel(Parcel in) {
            return new Sentence(in);
        }

        @Override
        public Sentence[] newArray(int size) {
            return new Sentence[size];
        }

    };

    public String getSentenceId() {
        return sentenceId;
    }

    public void setSentenceId(String sentenceId) {
        this.sentenceId = sentenceId;
    }

    public List<String> getSentences() {
        return sentences;
    }

    public void setSentences(List<String> sentences) {
        this.sentences = sentences;
    }

    public String getCreatedDate() {
        return createdDate;
    }

    public void setCreatedDate(String createdDate) {
        this.createdDate = createdDate;
    }

    public String getLastUpdatedDate() {
        return lastUpdatedDate;
    }

    public void setLastUpdatedDate(String lastUpdatedDate) {
        this.lastUpdatedDate = lastUpdatedDate;
    }

    public List<String> getSmallImages() {
        return smallImages;
    }

    public void setSmallImages(List<String> smallImages) {
        this.smallImages = smallImages;
    }

    public List<String> getLargeImages() {
        return largeImages;
    }

    public void setLargeImages(List<String> largeImages) {
        this.largeImages = largeImages;
    }


    public String getSentenceWordType() {
        return sentenceWordType;
    }

    public void setSentenceWordType(String sentenceWordType) {
        this.sentenceWordType = sentenceWordType;
    }

    public String getSentenceWordDescription() {
        return sentenceWordDescription;
    }

    public void setSentenceWordDescription(String sentenceWordDescription) {
        this.sentenceWordDescription = sentenceWordDescription;
    }

    public String getSentenceWords() {
        return sentenceWords;
    }

    public void setSentenceWords(String sentenceWords) {
        this.sentenceWords = sentenceWords;
    }

    public String getSentenceWordSound() {
        return sentenceWordSound;
    }

    public void setSentenceWordSound(String sentenceWordSound) {
        this.sentenceWordSound = sentenceWordSound;
    }

    public boolean isFavourite() {
        return isFavourite;
    }

    public void setFavourite(boolean isFavourite) {
        this.isFavourite = isFavourite;
    }

    public int getClicks() {
        return clicks;
    }

    public void setClicks(int clicks) {
        this.clicks = clicks;
    }

    public int getKeyStage() {
        return keyStage;
    }

    public void setKeyStage(int keyStage) {
        this.keyStage = keyStage;
    }

    public boolean isUserAdded() {
        return isUserAdded;
    }

    public void setUserAdded(boolean isUserAdded) {
        this.isUserAdded = isUserAdded;
    }

    public List<String> getPredictions() {
        return predictions;
    }

    public void setPredictions(List<String> predictions) {
        this.predictions = predictions;
    }

    public String getSentenceImageType() {
        return sentenceImageType;
    }

    public void setSentenceImageType(String sentenceImageType) {
        this.sentenceImageType = sentenceImageType;
    }

    @Override
    public int compareTo(Sentence o) {
      Date date = DateStringConverter.getDateFromString(this.getSentenceId());
      Date date2 = DateStringConverter.getDateFromString(o.getSentenceId());
        Log.d("PreviousSentence", "return " + date.compareTo(date2));
      return date.compareTo(date2);
    }

    @Override
    public String toString() {
        return "Sentence{" +
            "sentenceId='" + sentenceId + '\'' +
            ", sentences=" + sentences +
            ", createdDate='" + createdDate + '\'' +
            ", lastUpdatedDate='" + lastUpdatedDate + '\'' +
            ", smallImages=" + smallImages +
            ", largeImages=" + largeImages +
            ", sentenceWordType='" + sentenceWordType + '\'' +
            ", sentenceImageType='" + sentenceImageType + '\'' +
            ", sentenceWordDescription='" + sentenceWordDescription + '\'' +
            ", sentenceWords='" + sentenceWords + '\'' +
            ", sentenceWordSound='" + sentenceWordSound + '\'' +
            ", isFavourite=" + isFavourite +
            ", clicks=" + clicks +
            ", keyStage=" + keyStage +
            ", isUserAdded=" + isUserAdded +
            ", predictions=" + predictions +
            '}';
    }

    @Override
    public int type(TypeFactory typeFactory) {
        return typeFactory.type(this);
    }

    @Override
    public String id() {
        return sentenceId;
    }
}

JOIN MODEL

@Entity(primaryKeys = {"groupId", "sentenceId"})
public class GroupIdsWithSentenceIdsJoin {

    @NotNull
    @ColumnInfo(name = "groupId", index = true)
    private String groupId;

    @NotNull
    @ColumnInfo(name = "sentenceId", index = true)
    private String sentenceId;

    @Ignore
    public GroupIdsWithSentenceIdsJoin(@NotNull String groupId, @NotNull String sentenceId) {
        this.groupId = groupId;
        this.sentenceId = sentenceId;
    }

    public GroupIdsWithSentenceIdsJoin() {
    }

    public String getGroupId() {
        return groupId;
    }

    public void setGroupId(String groupId) {
        this.groupId = groupId;
    }

    public String getSentenceId() {
        return sentenceId;
    }

    public void setSentenceId(String sentenceId) {
        this.sentenceId = sentenceId;
    }
}

Answer 1

You should define Foreign Key relation between your Join Model and the two other models per this official guide.

You can then specify Update and Delete strategies in Foreign Key definitions which in your case should be CASCADE

The entity annotation of your Join Model should be something like this

@Entity(primaryKeys = {"groupId", "sentenceId"},
        foreignKeys = {
            @ForeignKey(entity = Group.class,
                        parentColumns = "id",
                        childColumns = "groupId",
                        onDelete = ForeignKey.CASCADE,
                        onUpdate = ForeignKey.CASCADE),
            @ForeignKey(entity = Sentence.class,
                        parentColumns = "id",
                        childColumns = "sentenceId",
                        onDelete = ForeignKey.CASCADE,
                        onUpdate = ForeignKey.CASCADE)
            })

For sorting you should write queries in your Dao's. You can't use @Relation in an @Entity according to this doc

Note that @Relation annotation can be used only in POJO classes, an Entity class cannot have relations. This is a design decision to avoid common pitfalls in Entity setups. You can read more about it in the main Room documentation. When loading data, you can simply work around this limitation by creating POJO classes that extend the Entity.