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()
}
}
- gets the built the database (allowing to be run on main thread for convenience and brevity)
- gets the dao
- clears all the tables (makes test rerunnable)
- adds 2 WorkSpaces, 2 Widgets and 2 Feeds
- adds the intersection map entries
- 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
- New data class IntersectionWithWorkSpaceWidgetFeed
:-
class IntersectionWithWorkSpaceWidgetFeed(
@Embedded
val workSpace: WorkSpace,
@Embedded
val widget: Widget,
@Embedded
val feed: Feed
)
- 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>
- 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