DBflow make simple relation ship between 2 table

Question

DBFlow for android doesn't have good document and after reading more information about how can I make simple relationship between two tables, I'm unable to do that.

I have 2 table as:

MainCategories and SubCategories

In SubCategories I have channelId which is stored in MainCategories, MainCategories has more data on SubCategories table, (one to many)

now I'm trying to implementing this relation ship:

My code is not correct and its like with the library documentation

MainCategories:

@Table(database = AppDatabase.class)
public class ChannelContentCategories extends BaseModel {
    @PrimaryKey(autoincrement = true)
    private int id;

    @Column
    private int channelId;

    @Column
    private String title;

    List<ChannelSubContentCategories> subContentCategories;

    @OneToMany(methods = {OneToMany.Method.ALL})
    public List<ChannelSubContentCategories> getMyChannelSubContentCategoriess() {
        ...
    }

    ...
}

SubCategories:

@Table(database = AppDatabase.class)
public class ChannelSubContentCategories extends BaseModel {
    @PrimaryKey(autoincrement = true)
    private int id;

    @Column
    private int categoryId;

    @Column
    private int parentId;

    @Column
    private String title;

    ...
}

how can I make simple relationship between channelId on ChannelContentCategories table with parentId on ChannelSubContentCategories table?

thanks in advance

Answer 1

The problem here is that DBFlow supports ForeignKey reference only to PrimaryKey column of another table. Therefore, you have a 3 different ways to achieve your relationship:

• Move your @PrimaryKey in ChannelContentCategories from id to channelId
• Make parentId be referenced to id instead of channelId, therefore no need for channelId to be PrimaryKey
• Or have multiple primary keys in the table. But in this case you can't define your id primary key as autoincremented

So, I've ended up with the 3rd way, and look what we have:

ChannelContentCategories.java

@Table(database = AppDatabase.class)
public class ChannelContentCategories extends BaseModel {
    @PrimaryKey
    @Column
    public int id;

    @PrimaryKey
    @Column
    public int channelId;

    @Column
    public String title;

    public List<ChannelSubContentCategories> subContentCategories;

    @OneToMany(methods = {OneToMany.Method.ALL}, variableName = "subContentCategories") // this should be equal to the field name, that contains children
    public List<ChannelSubContentCategories> getMyChannelSubContentCategories() {
        if (subContentCategories == null || subContentCategories.isEmpty()) {
            subContentCategories = SQLite.select()
                    .from(ChannelSubContentCategories.class)
                    .where(ChannelSubContentCategories_Table.parentId.eq(channelId))
                    .queryList();
        }
        return subContentCategories;
    }
    ...
} 

ChannelSubContentCategories.java

@Table(database = AppDatabase.class)
public class ChannelSubContentCategories extends BaseModel {
    @PrimaryKey(autoincrement = true)
    @Column
    public int id;

    @Column
    public int categoryId;

    @Column
    @ForeignKey(tableClass = ChannelContentCategories.class,
            references = @ForeignKeyReference(columnName = "parentId", foreignKeyColumnName = "channelId"))
    public int parentId;

    @Column
    public String title;
    ...
} 

I've tested it and it works. Feel free to choose the most suitable way for you.