How to make filter in inner table in android room join query

Question

I have two tables table A and table B, i am making inner join query in android room (A inner join B), but i have to filter data from table B , is that possible in android room?

Answer 1

Yes BUT you have to understand how Room works and also how a JOIN works (i.e. it is the cartesian product that is there will be a row for each combination that matches the filter)

Typically you would use a POJO with TableA Embedded (@Embedded) with TableB as an @Relation to combine. e.g. :-

class TableAWithTableB {
    @Embedded
    TableA tableA;
    @Relation(entity = TableB.class,parentColumn = "tableAId", entityColumn = "tableAMapId")
    List<TableB> tableBList;
}

However, the way Room handles @Relation is that it only retrieves the parent from the result and then proceeds to obtain ALL the children of the parent. That is it builds a complete object ignoring the filter if the filter is on the child.

If you wanted the filter applied upon the children then you would not use @Relation but could @Embedded both parent and child BUT then you get the parent for each child so could have multiple identical parents. e.g.

class TableAJoinedWithTableB {
    @Embedded
    TableA tableA;
    @Embedded
    TableB tableB;
}

Example

Considering the two Entities are :-

@Entity
class TableA {
    @PrimaryKey
    Long tableAId;
    String nameA;

    public TableA(){}
    @Ignore
    public TableA(String name){
        this.nameA = name;
    }

    public Long getTableAId() {
        return tableAId;
    }

    public void setTableAId(Long tableAId) {
        this.tableAId = tableAId;
    }

    public String getName() {
        return nameA;
    }

    public void setName(String name) {
        this.nameA = name;
    }
}

and:-

@Entity(
        foreignKeys = {
                @ForeignKey(
                        entity = TableA.class,
                        parentColumns = "tableAId",
                        childColumns = "tableAMapId"
                )
        }
)
class TableB {
    @PrimaryKey
    Long tableBId;
    @ColumnInfo(index = true)
    Long tableAMapId;
    String nameB;

    public TableB(){}
    @Ignore
    public TableB(long tableAMapId,String name) {
        this.tableAMapId = tableAMapId;
        this.nameB = name;
    }

    public Long getTableBId() {
        return tableBId;
    }

    public void setTableBId(Long tableBId) {
        this.tableBId = tableBId;
    }

    public Long getTableAMapId() {
        return tableAMapId;
    }

    public void setTableAMapId(Long tableAMapId) {
        this.tableAMapId = tableAMapId;
    }

    public String getName() {
        return nameB;
    }

    public void setName(String name) {
        this.nameB = name;
    }
}

and the @Dao, AllDao, is:-

@Dao
interface AllDao {
    @Insert long insert(TableA tableA);
    @Insert long[] insert(TableA...one_or_more_tableAs);
    @Insert long insert(TableB tableB);
    @Insert long[] insert(TableB...one_or_moretableBs);
    @Query("DELETE FROM tableA") int deleteAllFROMTableA();
    @Query("DELETE FROM tableB") int deleteAllFromTableB();
    @Query("SELECT * FROM tablea") List<TableA> getALlFromTableA();
    @Query("SELECT * FROM tableb") List<TableB> getAllFromTableB();
    @Transaction @Query("SELECT tableA.* FROM tablea JOIN tableb ON tableAId = tableAMapId WHERE tableb.nameB >:arg")
    List<TableAWithTableB> getSelectedViaRelation(String arg);
    @Query("SELECT * FROM tablea JOIN tableb ON tableAId = tableAMapId WHERE tableb.nameB >:arg")
    List<TableAJoinedWithTableB> getSelectedViaEmbedded(String arg);
}

and the @Database class is TheDatabase (a pretty standard one) then using :-

public class MainActivity extends AppCompatActivity {

    TheDatabase db;
    AllDao dao;
    private static final String TAG = "TABLEINFO";

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        db = TheDatabase.getInstance(this);
        dao = db.getDao();

        dao.deleteAllFromTableB();
        dao.deleteAllFROMTableA();

        long row1Id = dao.insert(new TableA("ROW1"));
        long row2Id = dao.insert(new TableA("ROW2"));
        long row3Id = dao.insert(new TableA("ROW3"));
        long row4Id = dao.insert(new TableA("ROW4"));

        dao.insert(new TableB(row1Id,"A"));
        dao.insert(new TableB(row1Id,"Z"));
        dao.insert(new TableB(row1Id,"P"));
        dao.insert(new TableB(row2Id,"B"));
        dao.insert(new TableB(row2Id,"Y"));
        dao.insert(new TableB(row2Id,"O"));
        dao.insert(new TableB(row3Id,"C"));
        dao.insert(new TableB(row3Id,"X"));
        dao.insert(new TableB(row3Id,"N"));
        dao.insert(new TableB(row4Id,"AA"));
        dao.insert(new TableB(row4Id,"BB"));

        for(TableAWithTableB t: dao.getSelectedViaRelation("M")) {
            logTableA(t.tableA);
            for(TableB tb: t.tableBList) {
                logTableB(tb);
            }
        }

        for (TableAJoinedWithTableB t: dao.getSelectedViaEmbedded("M")) {
            Log.d(TAG,"TableA is " + t.tableA.getName() + " TableB is " + t.tableB.getName() + " Parent(TableA) ID is " + t.tableB.getTableAMapId());
        }
    }

    private void logTableA(TableA tableA) {
        Log.d(TAG,"TableA Name = " + tableA.getName());
    }
    private void logTableB(TableB tableB) {
        Log.d(TAG,"\tTableB = " + tableB.getName() + " Parent TableA is " + tableB.getTableAMapId());
    }
}

results in :-

2021-06-29 07:43:49.134 D/TABLEINFO: TableA Name = ROW1
2021-06-29 07:43:49.134 D/TABLEINFO:    TableB = A Parent TableA is 1
2021-06-29 07:43:49.134 D/TABLEINFO:    TableB = Z Parent TableA is 1
2021-06-29 07:43:49.134 D/TABLEINFO:    TableB = P Parent TableA is 1
2021-06-29 07:43:49.134 D/TABLEINFO: TableA Name = ROW1
2021-06-29 07:43:49.134 D/TABLEINFO:    TableB = A Parent TableA is 1
2021-06-29 07:43:49.134 D/TABLEINFO:    TableB = Z Parent TableA is 1
2021-06-29 07:43:49.134 D/TABLEINFO:    TableB = P Parent TableA is 1
2021-06-29 07:43:49.134 D/TABLEINFO: TableA Name = ROW2
2021-06-29 07:43:49.134 D/TABLEINFO:    TableB = B Parent TableA is 2
2021-06-29 07:43:49.134 D/TABLEINFO:    TableB = Y Parent TableA is 2
2021-06-29 07:43:49.135 D/TABLEINFO:    TableB = O Parent TableA is 2
2021-06-29 07:43:49.135 D/TABLEINFO: TableA Name = ROW2
2021-06-29 07:43:49.135 D/TABLEINFO:    TableB = B Parent TableA is 2
2021-06-29 07:43:49.135 D/TABLEINFO:    TableB = Y Parent TableA is 2
2021-06-29 07:43:49.135 D/TABLEINFO:    TableB = O Parent TableA is 2
2021-06-29 07:43:49.135 D/TABLEINFO: TableA Name = ROW3
2021-06-29 07:43:49.135 D/TABLEINFO:    TableB = C Parent TableA is 3
2021-06-29 07:43:49.135 D/TABLEINFO:    TableB = X Parent TableA is 3
2021-06-29 07:43:49.135 D/TABLEINFO:    TableB = N Parent TableA is 3
2021-06-29 07:43:49.135 D/TABLEINFO: TableA Name = ROW3
2021-06-29 07:43:49.135 D/TABLEINFO:    TableB = C Parent TableA is 3
2021-06-29 07:43:49.135 D/TABLEINFO:    TableB = X Parent TableA is 3
2021-06-29 07:43:49.135 D/TABLEINFO:    TableB = N Parent TableA is 3


2021-06-29 07:43:49.140 D/TABLEINFO: TableA is ROW1 TableB is Z Parent(TableA) ID is 1
2021-06-29 07:43:49.140 D/TABLEINFO: TableA is ROW1 TableB is P Parent(TableA) ID is 1
2021-06-29 07:43:49.140 D/TABLEINFO: TableA is ROW2 TableB is Y Parent(TableA) ID is 2
2021-06-29 07:43:49.140 D/TABLEINFO: TableA is ROW2 TableB is O Parent(TableA) ID is 2
2021-06-29 07:43:49.140 D/TABLEINFO: TableA is ROW3 TableB is X Parent(TableA) ID is 3
2021-06-29 07:43:49.140 D/TABLEINFO: TableA is ROW3 TableB is N Parent(TableA) ID is 3

So ROW4 is not present, due to the filtering, in either method BUT:-

with @Relation for TableB, the result is complete Table A objects ignoring the WHERE clause in regard to the children.

When using @Embedded for TableB then the filter is applied BUT you get multiple rows for the parent (as you would without room).

Additional

If you wanted to get partially built TableA objects, i.e. with selective children, then you have to do the underlying work (as you would have to with native SQLite). You could build them based upon the result from the getSelectedViaEmbedded by merging the same TableA into either of the POJO classes (the annotations will be ignored if not built by room). For example consider :-

class TableAsWithSelectiveTableB {

    private ArrayList<TableAWithTableB> tableAWithTableBList = new ArrayList<>();

    private TableAsWithSelectiveTableB(List<TableAJoinedWithTableB> baselist) {
        String previousTableAName = "";
        TableA currentTableA = null;
        ArrayList<TableB> currentTableBList = new ArrayList<>();
        boolean toBeApplied = false;
        for (TableAJoinedWithTableB tabj: baselist) {
            if (!tabj.tableA.nameA.equals(previousTableAName)) {
                if (toBeApplied) {
                    TableAWithTableB apply = new TableAWithTableB();
                    apply.tableA = currentTableA;
                    apply.tableBList = currentTableBList;
                    tableAWithTableBList.add(apply);
                }
                toBeApplied = true;
                currentTableA = tabj.tableA;
                currentTableBList = new ArrayList<>();
                previousTableAName = currentTableA.nameA;
            }
            currentTableBList.add(tabj.tableB);
        }
        if (toBeApplied) {
            TableAWithTableB apply = new TableAWithTableB();
            apply.tableA = currentTableA;
            apply.tableBList = currentTableBList;
            tableAWithTableBList.add(apply);
        }
    }

    public static List<TableAWithTableB> getTableAWithTableBList(List<TableAJoinedWithTableB> list) {
        return new TableAsWithSelectiveTableB(list).tableAWithTableBList;
    }
}

Used with :-

    for(TableAWithTableB tatab: TableAsWithSelectiveTableB.getTableAWithTableBList(dao.getSelectedViaEmbedded("M"))) {
        logTableA(tatab.tableA);
        for(TableB tb: tatab.tableBList) {
            Log.d(TAG,"\tTableB is " + tb.getName() + " Parent(TableA) ID is " + tb.getTableAMapId());
        }
    }

It Results in:-

2021-06-29 10:00:41.432 D/TABLEINFO: TableA Name = ROW1
2021-06-29 10:00:41.432 D/TABLEINFO:    TableB is Z Parent(TableA) ID is 1
2021-06-29 10:00:41.432 D/TABLEINFO:    TableB is P Parent(TableA) ID is 1
2021-06-29 10:00:41.432 D/TABLEINFO: TableA Name = ROW2
2021-06-29 10:00:41.432 D/TABLEINFO:    TableB is Y Parent(TableA) ID is 2
2021-06-29 10:00:41.433 D/TABLEINFO:    TableB is O Parent(TableA) ID is 2
2021-06-29 10:00:41.433 D/TABLEINFO: TableA Name = ROW3
2021-06-29 10:00:41.433 D/TABLEINFO:    TableB is X Parent(TableA) ID is 3
2021-06-29 10:00:41.433 D/TABLEINFO:    TableB is N Parent(TableA) ID is 3