Searching multiple tables in Android Room using a single query

Issue

Given that there are 3 tables

@Entity
data class Pet(
  @PrimaryKey
  val id: String,
  val name: String,
  val colour: String,
  height: Int
  ownerId: String,
  householdId: String
)

@Entity
data class Owner(
  @PrimaryKey
  val id: String,
  val name: String,
  val address: String,
  val telephoneNumer: Int
)

@Entity
data class Household(
  @PrimaryKey
  val id: String,
  val name: String,
  val address: String,
  val region: String
)

What would be the best way of performing a query where it returns all entries in Pet, Owner and Household where name contains "Dav"

and get a list of data back similar to this (minus some fields for brevity)

Pet("Dave"...) // Pet
Owner("David"...) //Owner
Owner("Davina"...) //Owner
Household("Davenport Close"...) //Address

1. Is there a way to do this with SQLite queries and a custom POJO?
2. Do you perform 3 separate queries for the 3 tables and combine them together further up the chain (e.g. Repository/Usecase with Flow)?

Solution

If all you want is a LIST then you could use a custom POJO and a query that uses UNION's.

For example you could have a POJO such as :-

data class searchPOJO(
    val id: String,
    val type: String,
    val name: String
)

and a Query such as:-

    @Query("SELECT id,'Pet' AS type,name FROM pet WHERE name LIKE :search UNION SELECT id, 'owner' AS type, name FROM owner WHERE name LIKE :search UNION SELECT id, 'household' AS type, name FROM household WHERE name LIKE :search")
    abstract fun search(search: String): List<searchPOJO>

and you'd invoke it like:-

dao.search("%dav%")

• Noting:-
  • a UNION requires that number of columns match the initial select.
  • type is a literal used to identify from which table the data came
  • id would then be able to identify the row from which the specific object could be obtained according to it's type.

As an example the following code :-

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

    dao.insert(Pet("pet1","Dave","brown",10,"Fred","1 Maria Cresent"))
    dao.insert(Pet("pet2","George","red",11,"Bert","2 Somewhere Place"))
    dao.insert(Owner("owner1","David","10 Somewhere Place",1111111111))
    dao.insert(Owner("owner2","Laura","14 Overthere Way", 222222222))
    dao.insert(Owner("owner3","Davina","3 Wayland Way",333333333))
    dao.insert(Household("h1","Davenport Close","1 Davenport Close","region 1"))
    dao.insert(Household("h2","Daintree House","100 Smith Street","region 2"))

    for(s: searchPOJO in dao.search("%dav%")) {
        Log.d("DBINFO","ID is ${s.id} Name is ${s.name} Type is ${s.type}")
    }

results in :-

D/DBINFO: ID is h1 Name is Davenport Close Type is household
D/DBINFO: ID is owner1 Name is David Type is owner
D/DBINFO: ID is owner3 Name is Davina Type is owner
D/DBINFO: ID is pet1 Name is Dave Type is Pet

If you wanted something that returns one of each object per find (1 valid object and the other 2 invalid objects (blank for example)) with an indication of the type returned then you could use a POJO something like:-

data class searchPOJOAll (
    val type: String,
    @Embedded(prefix = Companion.pet_prefix)
    val pet: Pet,
    @Embedded(prefix = owner_prefix)
    val owner: Owner,
    @Embedded(prefix = household_prefix)
    val household: Household
) {

    companion object {
        const val pet_prefix: String = "pet_"
        const val owner_prefix: String = "owner_"
        const val household_prefix: String = "household_"
    }
}

• noting that due to the ambiguous (like named fields/variables) column names, that a prefix is used.

The Query would then be an expanded version of the previous query with each of the three SELECTS providing columns for the other objects, this again complicated due to the need to disambiguate the column names such as :-

@Query("SELECT 'PET' AS type, " +
        " id AS " + searchPOJOAll.pet_prefix + "id," +
        " name AS " + searchPOJOAll.pet_prefix + "name," +
        " colour AS " + searchPOJOAll.pet_prefix + "colour," +
        " height AS " + searchPOJOAll.pet_prefix + "height," +
        " ownerId AS " + searchPOJOAll.pet_prefix + "ownerId," +
        " householdId AS " + searchPOJOAll.pet_prefix + "householdId," +
        " '' AS " + searchPOJOAll.owner_prefix + "id," +
        " '' AS " + searchPOJOAll.owner_prefix + "name," +
        " '' AS " + searchPOJOAll.owner_prefix + "address," +
        " '' AS " + searchPOJOAll.owner_prefix + "telephoneNumer," +
        " '' AS " + searchPOJOAll.household_prefix + "id," +
        " '' AS " + searchPOJOAll.household_prefix + "name," +
        " '' AS " + searchPOJOAll.household_prefix + "address," +
        " '' AS " + searchPOJOAll.household_prefix + "region" +
        " FROM pet WHERE name LIKE :search " +
        " UNION SELECT 'OWNER' AS type, " +
        " '' AS " + searchPOJOAll.pet_prefix + "id," +
        " '' AS " + searchPOJOAll.pet_prefix + "name," +
        " '' AS " + searchPOJOAll.pet_prefix + "colour," +
        " '' AS " + searchPOJOAll.pet_prefix + "height," +
        " '' AS " + searchPOJOAll.pet_prefix + "ownerId," +
        " '' AS " + searchPOJOAll.pet_prefix + "householdId," +
        " id AS " + searchPOJOAll.owner_prefix + "id," +
        " name AS " + searchPOJOAll.owner_prefix + "name," +
        " address AS " + searchPOJOAll.owner_prefix + "address," +
        " telephoneNumer AS " + searchPOJOAll.owner_prefix + "telephoneNumer," +
        " '' AS " + searchPOJOAll.household_prefix + "id," +
        " '' AS " + searchPOJOAll.household_prefix + "name," +
        " '' AS " + searchPOJOAll.household_prefix + "address," +
        " '' AS " + searchPOJOAll.household_prefix + "region" +
        " FROM owner WHERE name LIKE :search " +
        " UNION SELECT 'HOUSEHOLD' AS type," +
        " '' AS " + searchPOJOAll.pet_prefix + "id," +
        " '' AS " + searchPOJOAll.pet_prefix + "name," +
        " '' AS " + searchPOJOAll.pet_prefix + "colour," +
        " '' AS " + searchPOJOAll.pet_prefix + "height," +
        " '' AS " + searchPOJOAll.pet_prefix + "ownerId," +
        " '' AS " + searchPOJOAll.pet_prefix + "householdId," +
        " '' AS " + searchPOJOAll.owner_prefix + "id," +
        " '' AS " + searchPOJOAll.owner_prefix + "name," +
        " '' AS " + searchPOJOAll.owner_prefix + "address," +
        " '' AS " + searchPOJOAll.owner_prefix + "telephoneNumer," +
        " id AS " + searchPOJOAll.household_prefix + "id," +
        " name AS " + searchPOJOAll.household_prefix + "name," +
        " address AS " + searchPOJOAll.household_prefix + "address," +
        " region AS " + searchPOJOAll.household_prefix + "region" +
        " FROM household WHERE name LIKE :search"
)
abstract fun searchAll(search: String): List<searchPOJOAll>

Expanding the previous demo code with :-

    /* List of all 3 object types (only one of which is of use) */
    for(s: searchPOJOAll in dao.searchAll("%dav%")) {
        if(s.type == "PET") {
            Log.d("DBINFO","${s.type}>>>" + getPetString(s.pet))
        }
        if (s.type == "OWNER") {
            Log.d("DBINFO","${s.type}>>>" + getOwnerString(s.owner))
        }
        if (s.type == "HOUSEHOLD") {
            Log.d("DBINFO","${s.type}>>>" +getHouseholdString(s.household))
        }
    }

and with the following functions :-

fun getPetString(pet: Pet):String {
    return "ID is ${pet.id} Petname = ${pet.name} colour is ${pet.colour} height is ${pet.height} ownerID is ${pet.ownerId} householId is ${pet.householdId}"
}
fun getOwnerString(owner: Owner): String {
    return "ID is ${owner.id} Ownername is ${owner.name} address is ${owner.address} telno is ${owner.telephoneNumer}"
}
fun getHouseholdString(household: Household): String {
    return "ID is ${household.id} name is ${household.name} address is ${household.address} region is ${household.region}"
}

The result (for the same data) would be :-

D/DBINFO: HOUSEHOLD>>>ID is h1 name is Davenport Close address is 1 Davenport Close region is region 1
D/DBINFO: OWNER>>>ID is owner1 Ownername is David address is 10 Somewhere Place telno is 1111111111
D/DBINFO: OWNER>>>ID is owner3 Ownername is Davina address is 3 Wayland Way telno is 333333333
D/DBINFO: PET>>>ID is pet1 Petname = Dave colour is brown height is 10 ownerID is Fred householId is 1 Maria Cresent

Although according to the comment The example provided in the Android documentation seems to create a new combined model instead of returning separate entities from the 3 tables mentioned above using a POJO such as :-

data class PetWithOwnerAndHousehold (
    @Embedded
    val pet: Pet,
    @Relation( entity = Owner::class, parentColumn = "ownerId",entityColumn = "id")
    val owner: Owner,
    @Relation(entity = Household::class, parentColumn = "householdId", entityColumn = "id")
    val household: Household
        )

and a Query/Dao such as :-

@Query("SELECT pet.* FROM pet JOIN owner ON owner.id = pet.ownerId JOIN household ON household.id = pet.householdId WHERE pet.name LIKE :search OR owner.name LIKE :search OR household.name LIKE :search")
abstract fun getPetWithOwnerAndHousehold(search: String): List<PetWithOwnerAndHousehold> 

would return the pet, it's owner and it's houdehold (assuming the relationships (pet->owener and pet->houdhold) are valid (in the above data they are not)) if either the pat name, the owner's name or the household name match the search item.

e.g. if the following rows were added (using the existing owners and houdeholds) :-

    dao.insert(Pet("p1","adava","grey",12,"owner1","h2")) /* multiple hits pet name and owner name*/
    dao.insert(Pet("p2","lady","blue",13,"owner2","h2")) /* no hits */
    dao.insert(Pet("p3","X","pink",14,"owner2","h1")) /* one hit household name */

Then the result from the query using %dav% as the search string would be :-

D/DBINFO: Pet ID is p1 pet's name is adava OwnerID is owner1  owner's name is David etc. HouseholdID is h2 household's name is Daintree House
D/DBINFO: Pet ID is p3 pet's name is X OwnerID is owner2  owner's name is Laura etc. HouseholdID is h1 household's name is Davenport Close

Answered By - MikeT