Room many to many relation is not working

Question

I followed the same tutorial from Android and created a many to many relation in my Room tables. But I am getting a strange behavior which is working till the first step, but not working for the relations.

Mostly I created some tables and trying to get data till two levels. for example Accounts - List - List .

My tables are like below:

@Dao
public abstract class AccountDAO {

    /*Get accounts*/
    @Transaction
    @Query("SELECT * FROM AccountModel")
    public abstract LiveData<DataWithAccounts> getAccountList();

    /*Get the account with respected channels*/
    @Transaction
    @Query("SELECT * FROM AccountEntity")
    public abstract LiveData<AccountWithChannels> getAccountWithChannels();


    /*Get the account with respected channels and vpubs*/
    @Transaction
    @Query("SELECT * FROM AccountEntity")
    public abstract LiveData<AccountWithChannelsAndVpubs> getAccountWithChannelsAndVpubs();

    @Transaction
    @Query("SELECT * FROM AccountEntity WHERE accId = :id")
    public abstract AccountWithChannelsAndVpubs loadData(long id);

    /*
     *Insert the object in database
     * @param account list, object to be inserted
     */
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    public abstract void insertAccountModel(AccountModel accountModel);

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    public abstract void insertAccountList(List<AccountEntity> data);

    /*
     * update the object in database
     * @param account, object to be updated
     */
    @Update
    public abstract void updateAccountList(AccountModel repos);

    /*
     * delete the object from database
     * @param account, object to be deleted
     */
    @Delete
    public abstract void deleteAccountList(AccountModel note);
}

AccountWithChannels:

public class AccountWithChannels {
    @Embedded public AccountEntity accounts;

    @Relation(
            parentColumn = "accId",
            entityColumn = "channelId",
            associateBy = @Junction(AccountChannelCrossRef.class)
    )
    public List<ChannelEntity> channels;

    public AccountEntity getAccounts() {
        return accounts;
    }

    public void setAccounts(AccountEntity accounts) {
        this.accounts = accounts;
    }

    public List<ChannelEntity> getChannels() {
        return channels;
    }

    public void setChannels(List<ChannelEntity> channels) {
        this.channels = channels;
    }
}

ChannelWithVpubs:

public class ChannelWithVpubs {
    @Embedded
    public ChannelEntity channel;

    @Relation(
            parentColumn = "channelId",
            entityColumn = "vPubId",
            associateBy = @Junction(ChannelVpubCrossRef.class)
    )
    public List<VPubEntity> vPubs;

    public ChannelEntity getChannel() {
        return channel;
    }

    public void setChannel(ChannelEntity channel) {
        this.channel = channel;
    }

    public List<VPubEntity> getvPubs() {
        return vPubs;
    }

    public void setvPubs(List<VPubEntity> vPubs) {
        this.vPubs = vPubs;
    }
}

AccountWithChannelsAndVpubs:

public class AccountWithChannelsAndVpubs {

    @Embedded
    public AccountEntity account;
    @Relation(
            entity = ChannelEntity.class,
            parentColumn = "accId",
            entityColumn = "accountId"
    )
    public List<ChannelWithVpubs> channelWithVpubs;

    public AccountEntity getAccount() {
        return account;
    }

    public void setAccount(AccountEntity account) {
        this.account = account;
    }

    public List<ChannelWithVpubs> getChannelWithVpubs() {
        return channelWithVpubs;
    }

    public void setChannelWithVpubs(List<ChannelWithVpubs> channelWithVpubs) {
        this.channelWithVpubs = channelWithVpubs;
    }
}

ChannelVpubCrossRef:

@Entity(primaryKeys = {"channelId", "vPubId"})
public class ChannelVpubCrossRef {
    public int channelId;
    public int vPubId;
}

After creating the relation with cross reference I called below method to get all data with Account, List and List.

private List<ChannelWithvPub> getAllVpubs(AccountEntity mAccountEntity){
    List<ChannelWithvPub> mData = new ArrayList<>();

    AccountWithChannelsAndVpubs accWithChnlNvpubs = database.accountDao().loadData(mAccountEntity.getAccId());

    List<ChannelWithVpubs> mdata = accWithChnlNvpubs.getChannelWithVpubs();

    for(int i=0; i<mdata.size(); i++){

        ChannelWithVpubs mChannelWithVpubs = mdata.get(i);

        for(int j=0; j<mChannelWithVpubs.getvPubs().size(); j++){

            VPubEntity mVpub = mChannelWithVpubs.getvPubs().get(j);
            ChannelWithvPub newData = new ChannelWithvPub(mAccountEntity.getAccId(), 
   mChannelWithVpubs.getChannel().getChannelId(), mVpub);
            mData.add(newData);
        }
    }

   return mData;
}

But the strange thing is I am getting Account and also List of Channels. But the Vpub list is always returned as 0.

I tried with foreign also but not helped. I am sure I am doing something wrong here, which I am not able to detect. But the data is properly inserting and all tables including Vpub has data as well.

score 2 · Accepted Answer · answered Aug 25 '21 at 09:36

But I am getting a strange behavior which is working till the first step, but not working for the relations.

You appear to have a strange schema, which may be the cause. That is you appear to have 2 relationships between Account's and Channel's.

On the Channel Entity you appear to have a Channel as a child to a single Account, as per AccountWithChannelsAndVpubs which includes:-

@Relation(
            entity = ChannelEntity.class,
            parentColumn = "accId",
            entityColumn = "accountId"
    )

That is the accountId in the ChannelEntity maps/references the Account.

However, in AccountWithChannels you have a mapping table for a many-many relationship between Channel and Account as per :-

@Relation(
            parentColumn = "accId",
            entityColumn = "channelId",
            associateBy = @Junction(AccountChannelCrossRef.class)
    )

Although this could be made to work, it could result in great confusion and perhaps that confusion is the result.

But the Vpub list is always returned as 0.

As you are using primitives (int) for id's then these default to 0, so perhaps you are inserting as per the default and hence the 0.

I tried with foreign also but not helped.

I assume you mean by defining ForeignKeys, perhaps they didn't help because they indicated issues (e.g. 0 in a cross reference table would result in a ForeignKey conflict).

In essence you code appears to work. Here's an equivalent based upon your code. Noting :-

I always use Long for id's (where generated) as:-
- @Insert returns a Long
- In theory problems could result if using Int or int for large databases.
For convenience/brevity code is run tested on the main thread.
For brevity getters and setters have been omitted.
Class member variables have been made private for classes that were created as they were not in the question, or changed to suit.

The testing/demo code:-

AccountEntity

@Entity
class AccountEntity {
    @PrimaryKey
    private Long accId;
    private String accName;

    public AccountEntity() {}

    @Ignore
    public AccountEntity(String accountName) {
        this.accName = accountName;
    }
}

ChannelEntity

@Entity
class ChannelEntity {
    @PrimaryKey
    private Long channelId;
    private String channelName;
    private long accountId;

    public ChannelEntity(){}

    @Ignore
    public ChannelEntity(String channelName, long accountId) {

        this.channelName = channelName;
        this.accountId = accountId;
    }
}

VPubEntity

@Entity
class VPubEntity {
    @PrimaryKey
    private Long vPubId;
    private String vPubName;

    public VPubEntity(){}

    @Ignore
    public VPubEntity(String vPubName) {
        this.vPubName = vPubName;
    }
}

AccountChannelCrossref

@Entity(
        primaryKeys = {"accId","channelId"}
        )
class AccountChannelCrossRef {
    private long accId;
    private long channelId;

    public AccountChannelCrossRef(){}

    @Ignore
    public AccountChannelCrossRef(long accId, long channelId) {
        this.accId = accId;
        this.channelId = channelId;
    }
}

ChannelVpubCrossRef (changed from int to long)

@Entity(
        primaryKeys = {"channelId", "vPubId"}
        )
public class ChannelVpubCrossRef {
    public long channelId;
    public long vPubId;
}

Classes ChannelWithVpubs, AccountWithChannels and AccountWithChannelsAndVpubs were copied as is and thus have not been inclduded.

A single @Dao annotated class was used as per :-

@Dao
abstract class AllDao {

    @Insert
    abstract long insert(AccountEntity accountEntity);
    @Insert
    abstract long insert(ChannelEntity channelEntity);
    @Insert
    abstract long insert(VPubEntity vPubEntity);
    @Insert
    abstract long insert(AccountChannelCrossRef accountChannelCrossRef);
    @Insert
    abstract long insert(ChannelVpubCrossRef channelVpubCrossRef);

    @Query("INSERT INTO accountchannelcrossref VALUES(:accId,:channelId)")
    abstract long insertAccountChannelCrossRef(long accId, long channelId);
    @Query("INSERT INTO channelvpubcrossref VALUES(:channelId,:vPubId)")
    abstract long insertChannelVPubCrossRef(long channelId,long vPubId);

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    public abstract void insertAccountList(List<AccountEntity> data);

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    public abstract void insertChannelList(List<ChannelEntity> data);

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    public abstract void insertVPubList(List<VPubEntity> data);

    @Transaction
    @Query("SELECT * FROM AccountEntity")
    //public abstract LiveData<AccountWithChannelsAndVpubs> getAccountWithChannelsAndVpubs();
    public abstract List<AccountWithChannelsAndVpubs> getAccountWithChannelsAndVpubs();
}

The @Database class was TheDatabase as per :-

@Database(
        entities = {
                AccountEntity.class,
                ChannelEntity.class,
                VPubEntity.class,
                AccountChannelCrossRef.class,ChannelVpubCrossRef.class
        },
        version = 1
)
abstract class TheDatabase extends RoomDatabase {

    abstract AllDao getAllDao();

    private static volatile TheDatabase instance = null;
    public static TheDatabase getInstance(Context context) {
        if (instance == null) {
            instance = Room.databaseBuilder(
                    context,
                    TheDatabase.class,
                    "my.db"
            )
                    .allowMainThreadQueries()
                    .build();
        }
        return instance;
    }
}

Finally running the above was done using the following :-

public class MainActivity extends AppCompatActivity {

    TheDatabase db;
    AllDao dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        db = TheDatabase.getInstance(this);
        dao = db.getAllDao();

        long a1 = dao.insert(new AccountEntity("Account1"));
        long a2 = dao.insert(new AccountEntity("Account2"));
        long a3 = dao.insert(new AccountEntity("Account3"));

        long c1 = dao.insert(new ChannelEntity("Channel1",a1)); //???? N2M 1 channel Many accounts
        long c2 = dao.insert(new ChannelEntity("Channel2",a2));
        long c3 = dao.insert(new ChannelEntity("Channel3",a3));

        long v1 = dao.insert(new VPubEntity("VPub1"));
        long v2 = dao.insert(new VPubEntity("VPub2"));
        long v3 = dao.insert(new VPubEntity("VPub3"));

        // ???? M2M between account and channel
        dao.insert(new AccountChannelCrossRef(a1,c1));
        dao.insert(new AccountChannelCrossRef(a1,c3));
        dao.insert(new AccountChannelCrossRef(a2,c2));
        dao.insert(new AccountChannelCrossRef(a3,c1));
        dao.insert(new AccountChannelCrossRef(a3,c2));
        dao.insert(new AccountChannelCrossRef(a3,c3));

        ChannelVpubCrossRef cvxref1 = new ChannelVpubCrossRef();
        cvxref1.channelId = c1;
        cvxref1.vPubId = v1;
        dao.insert(cvxref1);
        cvxref1.vPubId = v2;
        dao.insert(cvxref1);
        cvxref1.vPubId = v3;
        dao.insert(cvxref1);
        cvxref1.channelId = c2;
        cvxref1.vPubId = v3;
        dao.insert(cvxref1);

        String TAG = "AWCAVINFO";
        for(AccountWithChannelsAndVpubs awcav: dao.getAccountWithChannelsAndVpubs()) {
            Log.d(TAG,"Account is " + awcav.account.getAccName());
            for (ChannelWithVpubs cwv: awcav.channelWithVpubs) {
                Log.d(TAG,"\tChannel is " + cwv.channel.getChannelName());
                for(VPubEntity v: cwv.vPubs) {
                    Log.d(TAG,"\t\tVPub is " + v.getVPubName());
                }
            }
        }
    }
}

Result (output to the log) :-

D/AWCAVINFO: Account is Account1
D/AWCAVINFO:    Channel is Channel1
D/AWCAVINFO:        VPub is VPub1
D/AWCAVINFO:        VPub is VPub2
D/AWCAVINFO:        VPub is VPub3
D/AWCAVINFO: Account is Account2
D/AWCAVINFO:    Channel is Channel2
D/AWCAVINFO:        VPub is VPub3
D/AWCAVINFO: Account is Account3
D/AWCAVINFO:    Channel is Channel3

So the VPub's are extracted if there are any and hence why I suspect that your issue is not in the code that you have included in your question.

Room many to many relation is not working

1 Answers1