3 way intersection Table in room Android

I am trying to achieve the following

I have the following Entities

@Entity(tableName = "workspace_table")
data class WorkSpace(
    @PrimaryKey
    val workSpaceId:Long,
    .....
)

@Entity(tableName = "widget_table")
data class Widget(
    val widgetId:Long,
    .......
)

@Entity(tableName = "feed_table")
data class Feed(
    val feedId:Long,
    .......
)

What I want from the 3 table is the below POJO

data class MergedData(
@Embedded workSpace:WorkSpace,
@Embedded widget:List<Widget>,
@Embedded feeds:List<Feed>,
)

The relationship is like this

workSpaceId|widgetId|feedId|
  1        | 1      | 2
  1        | 1      | 1    |
  2        | 1      | 2
  2        | 2      | 1    

Basically there is a many to many relation ship between workspace and widgets and widgets and feeds They should come together when all of the three tables are participating

I went through the guide https://developer.android.com/training/data-storage/room/relationships

and tried Mapping two way between widget and workspace and feed and widget however those I am not even been able to build with that

I tried one to many with Workspace and Widget and Many to Many with Widget and Feeds then I am getting feeds in my workspace for widgets which I don’t want .

I am really confused at this point any nudge in the correct direction will greatly appreciated

Update

With Mike’s Answer below I am getting this

2021-04-04 12:16:06.097 10237-10291/com.example.datacreation D/MainActivty: meta data [IntersectionWithWorkSpaceWidgetFeed(workSpace=WorkSpace(workSpaceId=2, 
associatedUserId=test, workSpaceName=Demo),
 widget=WidgetMetaData(widgetId=11, widgetName=Widget1, backgroundColor=None, widgetType=Normal, dataUrl=www), 
feed=Feed(feedId=2, feedName=Feed2, count=0, asyncInterval=1234)),
 IntersectionWithWorkSpaceWidgetFeed(workSpace=WorkSpace(workSpaceId=2, associatedUserId=test, 
workSpaceName=Demo),
 widget=WidgetMetaData(widgetId=12, widgetName=Widget2, backgroundColor=None, widgetType=normal, dataUrl=www), 
feed=Feed(feedId=1, feedName=Feed1, count=2, asyncInterval=1234)),
 IntersectionWithWorkSpaceWidgetFeed(workSpace=WorkSpace(workSpaceId=2, associatedUserId=igvuser, workSpaceName=Demo),
 widget=WidgetMetaData(widgetId=13, widgetName=Widget3, backgroundColor=None, widgetType=normal, dataUrl=www),
 feed=Feed(feedId=2, feedName=Feed2, count=0, asyncInterval=1234))]

Near enough to my original MergedData POJO. Thanks Mike.

Answer

I believe that you want a 3 way mapping table. Each row consisting of 3 columns, the WorkSpaceId, the WidgetId and the FeedId with the Primary Key composed of all 3 columns.

Assuming this here is a working example:-

The 3 base entities:

WorkSpace

@Entity(
    tableName = "workspace_table",
)
data class WorkSpace(
    @PrimaryKey
    val workSpaceId:Long,
    val workPlaceName: String
)

Widget

@Entity(
    tableName = "widget_table",

)
data class Widget(
    @PrimaryKey
    val widgetId:Long,
    val widgetName: String
)

Feed

@Entity(tableName = "feed_table")
data class Feed(
    @PrimaryKey
    val feedId:Long,
    val feedName: String
)

The NEW mapping table WorkSpaceWidgetFeedIntersectionMap

@Entity(
    tableName = "workspace_widget_feed_mapping_table",
    foreignKeys = [
        ForeignKey(
            entity = WorkSpace::class,
            parentColumns = ["workSpaceId"],
            childColumns = ["workSpaceId_map"]
        ),
        ForeignKey(
            entity = Widget::class,
            parentColumns = ["widgetId"],
            childColumns = ["widgetId_map"]
        ),
        ForeignKey(
            entity = Feed::class,
            parentColumns = ["feedId"],
            childColumns = ["feedId_map"]
        )
    ],
    primaryKeys = ["workSpaceId_map","widgetId_map","feedId_map"],
)
data class WorkSpaceWidgetFeedIntersectionMap(

    @NonNull
    val workSpaceId_map: Long,
    @NonNull
    val widgetId_map: Long,
    @NonNull
    val feedId_map: Long
)
  • Foreign Keys are optional

The Dao’s AllDao

@Dao
interface AllDao {

    @Insert
    fun insertWorkSpace(workSpace: WorkSpace): Long
    @Insert
    fun insertWidget(widget: Widget): Long
    @Insert
    fun insertFeed(feed: Feed): Long
    @Insert
    fun insertWorkSpaceWidgetFeedMap(workSpaceWidgetFeedIntersectionMap: WorkSpaceWidgetFeedIntersectionMap): Long


    @Query("DELETE FROM workspace_table")
    fun clearWorkSpaceTable(): Int
    @Query("DELETE FROM widget_table")
    fun clearWidgetTable(): Int
    @Query("DELETE FROM feed_table")
    fun clearFeedTable(): Int

    @Query("DELETE FROM workspace_widget_feed_mapping_table")
    fun clearWorkSpaceWidgetFeedMap(): Int

    @Query("SELECT * FROM workspace_widget_feed_mapping_table")
    fun getWorkSpaceWidgetFeedIntersections(): List<WorkSpaceWidgetFeedIntersectionMap>

}

The Database MyDatabase

@Database(entities = [WorkSpace::class,Widget::class,Feed::class,WorkSpaceWidgetFeedIntersectionMap::class],version = 1)
abstract class MyDatabase: RoomDatabase() {
    abstract fun getAllDoa(): AllDao
}

And finally an Activity MainActivity to test the basic functionality

class MainActivity : AppCompatActivity() {
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        val db = Room.databaseBuilder(applicationContext,MyDatabase::class.java,"mydb")
            .allowMainThreadQueries()
            .build()
        val dao = db.getAllDoa()
        clearAllTables(dao)
        dao.insertWorkSpace(WorkSpace(1,"WorkSpace 1"))
        dao.insertWorkSpace( WorkSpace(2,"WorkSpace 2"))

        dao.insertWidget(Widget(1,"Widget 1"))
        dao.insertWidget(Widget(2,"Widget 2"))

        dao.insertFeed(Feed(1,"Feed 1"))
        dao.insertFeed( Feed(2,"Feed 2"))

        dao.insertWorkSpaceWidgetFeedMap(WorkSpaceWidgetFeedIntersectionMap(1,1,2))
        dao.insertWorkSpaceWidgetFeedMap(WorkSpaceWidgetFeedIntersectionMap(1,1,1))
        dao.insertWorkSpaceWidgetFeedMap(WorkSpaceWidgetFeedIntersectionMap(2,1,2))
        dao.insertWorkSpaceWidgetFeedMap(WorkSpaceWidgetFeedIntersectionMap(2,2,1))

        val wwfiList = dao.getWorkSpaceWidgetFeedIntersections()
        for(cwwfi: WorkSpaceWidgetFeedIntersectionMap in wwfiList) {
            Log.d("WWFIINFO","WorkSpaceID = " + cwwfi.workSpaceId_map + " WidgetID = " + cwwfi.widgetId_map + " FeedID = " + cwwfi.feedId_map)
        }

    }

    private fun clearAllTables(dao: AllDao) {
        dao.clearWorkSpaceWidgetFeedMap()
        dao.clearFeedTable()
        dao.clearWidgetTable()
        dao.clearWorkSpaceTable()
    }
}
  1. gets the built the database (allowing to be run on main thread for convenience and brevity)
  2. gets the dao
  3. clears all the tables (makes test rerunnable)
  4. adds 2 WorkSpaces, 2 Widgets and 2 Feeds
  5. adds the intersection map entries
  6. extracts and logs the intersections

Result

Running the above produces :-

2021-04-04 08:31:02.942 D/WWFIINFO: WorkSpaceID = 1 WidgetID = 1 FeedID = 2
2021-04-04 08:31:02.942 D/WWFIINFO: WorkSpaceID = 1 WidgetID = 1 FeedID = 1
2021-04-04 08:31:02.942 D/WWFIINFO: WorkSpaceID = 2 WidgetID = 1 FeedID = 2
2021-04-04 08:31:02.943 D/WWFIINFO: WorkSpaceID = 2 WidgetID = 2 FeedID = 1
  • You could easily then get the respective WorkSpace, Wdiget and Feed from the retrieved WorkSpaceWidgetFeedIntersectionMap.
  • see addtional

Additional

Now to get your MergedData (equivalent) then consider the following additions to the above

  1. New data class IntersectionWithWorkSpaceWidgetFeed

:-

class IntersectionWithWorkSpaceWidgetFeed(
    @Embedded
    val workSpace: WorkSpace,
    @Embedded
    val widget: Widget,
    @Embedded
    val feed: Feed
)
  1. An extra Dao function getWorkSpaceWidgetAndFeedFromIntersectionMap()

:-

@Query("SELECT * FROM workspace_widget_feed_mapping_table JOIN workspace_table ON workSpaceId = workSpaceId_map JOIN widget_table ON widgetId = widgetId_map JOIN feed_table ON feedId = feedId_map")
fun getWorkSpaceWidgetAndFeedFromIntersectionMap(): List<IntersectionWithWorkSpaceWidgetFeed>
  1. A new (or replace existing 6.) section in MainActivity’s onCreate method

:-

    val iwwfList= dao.getWorkSpaceWidgetAndFeedFromIntersectionMap()
    for(iwwf: IntersectionWithWorkSpaceWidgetFeed in iwwfList) {
        Log.d("WWFINFO","WorkSpaceID = " + iwwf.workSpace.workSpaceId + " WorkSpaceName = " + iwwf.workSpace.workPlaceName +
                " WidgetID = " + iwwf.widget.widgetId + " WidgetName = " + iwwf.widget.widgetName +
                " FeedID = " + iwwf.feed.feedId + " FeedName = " + iwwf.feed.feedName
        )
    }

The Result from the above changes :-

2021-04-04 09:20:34.371 D/WWFINFO: WorkSpaceID = 1 WorkSpaceName = WorkSpace 1 WidgetID = 1 WidgetName = Widget 1 FeedID = 2 FeedName = Feed 2
2021-04-04 09:20:34.371 D/WWFINFO: WorkSpaceID = 1 WorkSpaceName = WorkSpace 1 WidgetID = 1 WidgetName = Widget 1 FeedID = 1 FeedName = Feed 1
2021-04-04 09:20:34.371 D/WWFINFO: WorkSpaceID = 2 WorkSpaceName = WorkSpace 2 WidgetID = 1 WidgetName = Widget 1 FeedID = 2 FeedName = Feed 2
2021-04-04 09:20:34.371 D/WWFINFO: WorkSpaceID = 2 WorkSpaceName = WorkSpace 2 WidgetID = 2 WidgetName = Widget 2 FeedID = 1 FeedName = Feed 1