Return Type for Android Room Joins

Joining tables in a ROOM database

This is a many-to-many relation so you have to create a join table:
UserPetsJoin table with the petId and userId;
Here you can find a good topic explaining you're same situation:
https://android.jlelse.eu/android-architecture-components-room-relationships-bf473510c14a

RoomDB - Can you JOIN tables from different databases

Does the fact I want to keep my db's seperate indicate a "smell"?

Yes, especially with Room. It introduces complexities and inefficiencies.

Is it possible to write a query where I can get the join model FooWithBar if the Foo table resides in a different database than the Bar table?

For your simple example Yes but as can be seen from the example not with an actual SQL JOIN (without attaching), that is you get the Foo object and mimic the JOIN by then getting the appropriate Bar (or Bars).

If you tried to mix Entities from different Databases then you will encounter problems e.g.

even though there are no issues adding (whilst editing) a Dao such as :-

@Query("SELECT * FROM foo JOIN bar ON foo.barId = bar.id")
fun getAllFooWithBar(): List<FooWithBar>

Which appears fine as per (screen shot from Android Studio) :-

When you compile you will get errors such as :-

E:\AndroidStudioApps\SO67981906KotlinRoomDate\app\build\tmp\kapt3\stubs\debug\a\a\so67981906kotlinroomdate\FooDao.java:22: error: There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such table: bar)
    public abstract void getAllFooWithBar();
                         ^E:\AndroidStudioApps\SO67981906KotlinRoomDate\app\build\tmp\kapt3\stubs\debug\a\a\so67981906kotlinroomdate\FooDao.java:22: error: Not sure how to convert a Cursor to this method's return type (void).
    public abstract void getAllFooWithBar();

To get anything that requires tables from both databases in a single query will be outside of the scope of Room as each database only knows about it's own tables.

However, if you ATTACH one database to another you then will have both database in one BUT Room does not understand it. So you basically have to revert to using a SupportSQLiteDatabase (similar to using native Android SQlite (but with some limitations)).

Example (very simple)

Foo Entity

@Entity
data class Foo(
    @PrimaryKey
    val fooId: Long?,
    val barId: Long,
    val fooText: String
)

• Basically the same

FooDao

@Dao
interface FooDao {
    @Insert
    fun insert(foo: Foo): Long
    @Query("SELECT * FROM foo")
    fun getAllFoos(): List<Foo>
    @Query("SELECT * FROM foo WHERE fooId=:fooId")
    fun getFooById(fooId: Long): Foo

    /* !!!!NO GO!!!!
    @Query("SELECT * FROM foo JOIN bar ON foo.barId = bar.id")
    fun getAllFooWithBar(): List<FooWithBar>
     */
}

• Some simple Dao's

FooDatabase

@Database(
    entities = [Foo::class],
    version = 1,
    exportSchema = false
)
abstract class FooDatabase : RoomDatabase() {
    abstract fun fooDao() : FooDao

    fun attachBar(context: Context): Boolean {
        var rv: Boolean = false
        if (instance != null) {
            val dbs = this.openHelper?.writableDatabase
            val barpath = context.getDatabasePath(BarDatabase.DBNAME)
            if (dbs != null) {
                dbs.execSQL("ATTACH DATABASE '$barpath' AS $BAR_SCHEMA_NAME")
                rv = true
            }
        }
        return rv
    }

    fun closeInstance() {
        if(instance == null) return
        if (this.isOpen()) {
            this.close()
        }
        instance = null
    }

    companion object {

        @Volatile
        private var instance: FooDatabase? = null
        fun getInstanceWithForceOption(context: Context, forceReopen: Boolean = false): FooDatabase {
            if (forceReopen) instance?.closeInstance()
            if (instance == null) {
                instance = Room.databaseBuilder(context,FooDatabase::class.java, DBNAME)
                    .allowMainThreadQueries()
                    .addCallback(FOO_CALLBACK)
                    .build()
            }
            return instance as FooDatabase
        }

        fun getInstance(context: Context): FooDatabase {
            return getInstanceWithForceOption(context, false)
        }

        val FOO_CALLBACK = object: RoomDatabase.Callback() {
            override fun onOpen(db: SupportSQLiteDatabase) {
                super.onOpen(db)
            }
            override fun onCreate(db: SupportSQLiteDatabase) {
                super.onCreate(db)
            }
        }
        const val DBNAME: String = "foo.db"
        const val BAR_SCHEMA_NAME = "bar_schema"
    }
}

• CallBacks not used but ATTACH could be done in onOpen if ALWAYS access via attach

Bar Entity

@Entity
data class Bar(
    @PrimaryKey
    val id: Long?,
    val barText: String
)

• Basically the same

BarDao

@Dao
interface BarDao {

    @Insert
    fun insert(bar: Bar): Long
    @Query("SELECT * FROM bar")
    fun getAllBars(): List<Bar>
    @Query("SELECT * FROM Bar WHERE id=:id")
    fun getBarById(id: Long): Bar
}

BarDatabase

@Database(
    entities = [Bar::class],
    version = 1,
    exportSchema = false
)
abstract class BarDatabase : RoomDatabase() {
    abstract fun barDao() : BarDao

    fun closeInstance() {
        if (this.isOpen()) {
            this.close()
        }
        instance = null
    }

    companion object {
        @Volatile
        private var instance: BarDatabase? = null
        fun getInstanceWithForceOption(context: Context, forceReopen: Boolean = false): BarDatabase {
            if (forceReopen) instance?.closeInstance()
            if (instance == null) {
                instance = Room.databaseBuilder(context,BarDatabase::class.java, DBNAME)
                    .allowMainThreadQueries()
                    .addCallback(BAR_CALLBACK)
                    .build()
            }
            return instance as BarDatabase
        }

        fun getInstance(context: Context): BarDatabase {
            return getInstanceWithForceOption(context, false)
        }

        val BAR_CALLBACK = object: RoomDatabase.Callback() {
            override fun onOpen(db: SupportSQLiteDatabase) {
                super.onOpen(db)
            }
            override fun onCreate(db: SupportSQLiteDatabase) {
                super.onCreate(db)
            }
        }
        const val DBNAME: String = "bar.db"
    }
}

• Again CallBacks do nothing

FooWithBar

class FooWithBar {
    
    var foo: Foo
    var bar: Bar

    constructor(fooId: Long, fooDao: FooDao, barDao: BarDao) {
        this.foo = fooDao.getFooById(fooId)
        this.bar = barDao.getBarById(foo.barId)
    }
}

• As you cannot get both a Foo and a Bar this does the equivalent of a join by getting the Foo via the FooDatabase and then gets the associated Bar via the BarDatabase.

MainActivity putting it altogether :-

class MainActivity : AppCompatActivity() {

    lateinit var foodb: FooDatabase
    lateinit var fooDao: FooDao
    lateinit var bardb: BarDatabase
    lateinit var barDao: BarDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        foodb = FooDatabase.getInstance(this)
        fooDao = foodb.fooDao()
        bardb = BarDatabase.getInstance(this)
        barDao = bardb.barDao()

        /* Add some data */
        fooDao.insert(Foo(null,barDao.insert(Bar(null,"BAR1")),"FOO1"))
        barDao.insert(Bar(null,"BAR UNUSED"))
        fooDao.insert(Foo(null,barDao.insert(Bar(null,"BAR2")),"FOO2"))

        /* Get equivalent of join (simple) using the FooWithBar */
        val allFoosWithBars = mutableListOf<FooWithBar>()
        for(foo: Foo in fooDao.getAllFoos()) {
            allFoosWithBars.add(FooWithBar(foo.fooId!!,fooDao,barDao))
        }
        for(fwb: FooWithBar in allFoosWithBars) {
            Log.d("FOOBARINFO","Foo is ${fwb.foo.fooText} Bar is ${fwb.bar.barText}")
        }
        //* Done with the Bar database Room wise
        bardb.closeInstance()
        foodb.attachBar(this) //<<<<< ATTACHES the Bar database to the Foo

        /* Get a Supprort SQLite Database */
        var sdb = foodb.openHelper.writableDatabase

        /* Query Foo and the attached Bar */
        var csr = sdb.query("SELECT * FROM foo JOIN ${FooDatabase.BAR_SCHEMA_NAME}.bar ON foo.barId = ${FooDatabase.BAR_SCHEMA_NAME}.bar.id")
        DatabaseUtils.dumpCursor(csr)
        csr.close()
   }
}

Result

2021-06-16 16:35:04.045 D/FOOBARINFO: Foo is FOO1 Bar is BAR1
2021-06-16 16:35:04.045 D/FOOBARINFO: Foo is FOO2 Bar is BAR2

2021-06-16 16:35:04.092 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@ee9871b
2021-06-16 16:35:04.093 I/System.out: 0 {
2021-06-16 16:35:04.093 I/System.out:    fooId=1
2021-06-16 16:35:04.093 I/System.out:    barId=1
2021-06-16 16:35:04.093 I/System.out:    fooText=FOO1
2021-06-16 16:35:04.093 I/System.out:    id=1
2021-06-16 16:35:04.093 I/System.out:    barText=BAR1
2021-06-16 16:35:04.093 I/System.out: }
2021-06-16 16:35:04.093 I/System.out: 1 {
2021-06-16 16:35:04.093 I/System.out:    fooId=2
2021-06-16 16:35:04.093 I/System.out:    barId=3
2021-06-16 16:35:04.093 I/System.out:    fooText=FOO2
2021-06-16 16:35:04.093 I/System.out:    id=3
2021-06-16 16:35:04.093 I/System.out:    barText=BAR2
2021-06-16 16:35:04.094 I/System.out: }
2021-06-16 16:35:04.094 I/System.out: <<<<<

Android Room inner join returns columns which are not used

You can use SELECT orders.* FROM orders INNER JOIN ....

Adding the suggested @RewriteQueriesToDropUnusedColumns does not do anything.

This could be because of the caveat:-

Note that Room will not rewrite the query if it has multiple columns that have the same name as it does not yet have a way to distinguish which one is necessary.

I'd suggest always using unique column names, doing so can avoid issues with ambiguities

Note that it appears that when there are duplicate column names, then the value of last column with the duplicated name is used by room. As can be seen in this example

---

Related Topics