Search code examples
androidforeign-keysandroid-roomrelationship

Room relation with conditions

I have an object Category

@Entity
data class Category(
    @PrimaryKey
    val id: String,
    val name: String,
    val is_free: Boolean
)

and object Section

@Entity
data class Section(
    @PrimaryKey
    val name: String,
    val category_ids: List<String>
)

Sample data for Category object as below

[
  {
    "id": "quotes",
    "name": "General",
    "is_free": true
  },
  {
    "id": "favorites",
    "name": "Favorites",
    "is_free": true
  },
  {
    "id": "positivity",
    "name": "Positive Thinking",
    "is_free": false
  },
  {
    "id": "love",
    "name": "Love",
    "is_free": false
  }
]

and this the sample data for Section object

[
  {
    "name": "Categories",
    "category_ids": [
      "quotes",
      "favorites"
    ]
  },
  {
    "name": "Most Popular",
    "category_ids": [
      "positivity",
      "love"
    ]
  }
]

Now I need create new object as SectionWithCategory like below by replacing the category_ids of Section object by relevant Category object,

data class SectionWithCategories(
    val name: String,
    val categories: List<Category>
)

My problem is how can I create SectionWithCategories object to get following result?

[
  {
    "name": "Categories",
    "categories": [
      {
        "id": "quotes",
        "name": "General",
        "is_free": true
      },
      {
        "id": "favorites",
        "name": "Favorites",
        "is_free": true
      }
    ]
  },
  {
    "name": "Most Popular",
    "categories": [
      {
        "id": "positivity",
        "name": "Positive Thinking",
        "is_free": false
      },
      {
        "id": "love",
        "name": "Love",
        "is_free": false
      }
    ]
  }
]
Solution

  • My problem is how can I create SectionWithCategories object to get following result?

    With some difficulty because you are, assuming that you have appropriate type converters, storing data that is not conducive to relationships and thus extracting related data.

    You would have to extract the data and then build the objects from the JOSN strings that are being stored.

    If you have no Type Converters then the your Section Entity will not compile as you cannot a column that has a type of List.

    I would suggest, as you appear to have a many-many relationship between Sections and Categories, that you adopt a third table that holds the relationships (and does away with the need to have Type Converters).

    • this also does away with the bloat that comes with storing objects as JSON

    So instead of having val category_ids: List<String> in the Section Entity e.g. :-

    @Entity
data class Section(
    @PrimaryKey
    val id: String,
    val name: String,
    //val category_ids: List<String> use mapping/associatetive table instead
)
    • commented out but should probably be removed.

    You remove this line and have a third Entity that stores the id of the Section and the respective id of the category allowing for any combination. The following is a full version with suggested options added such as Foreign Key definitions:-

    @Entity(
    primaryKeys = ["sectionMap","categoryMap"] /* MUST have a primary key */

    /* Foreign Keys are optional, BUT enforce referential integrity */
    /* A Foreign Key is a rule that says that a child must have a parent */
    /* If the rule is broken then a Foreign Key conflict results */
    , foreignKeys = [
        /* Defining the Section's id as the parent and the SectionMap as the child */
        ForeignKey(
            entity = Section::class,
            parentColumns = ["id"],
            childColumns = ["sectionMap"],
            /* Optional within a Foreign Key are the following two Actions that can
                onDelete - action to be taken if the parent is deleted
                    CASCADE will delete the rows that are a child of the parent
                onUpdate - action taken if the value of the parent's mapped column changes
                    CASCADE will update the rows that are a child of the parent
             */
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        /* Defining the Category's id as the parent and the CategoryMap as the child */
        ForeignKey(
            entity = Category::class,
            parentColumns = ["id"],
            childColumns = ["categoryMap"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class SectionCategoryMap(
    val sectionMap: String,
    @ColumnInfo(index = true) /* Room warns if not indexed */
    val categoryMap: String
)
    • Some notes, as comments, have been added, which should be read and perhaps investigated further.

    Now your SectionWithCategories could be :-

    data class SectionWithCategories(
    @Embedded
    val section: Section,
    @Relation(
        entity = Category::class, parentColumn = "id", entityColumn = "id",
        associateBy = Junction(
            value = SectionCategoryMap::class, /* The mapping/associative table Entity */
            parentColumn = "sectionMap", /* The column that maps to the parent (Section) */
            entityColumn = "categoryMap" /* The column that maps to the children (Categories) */
        )
    )
    val categories: List<Category>
)

    Demonstration

    With and @Dao class (AllDao) such as :-

    @Dao
abstract class AllDao {
    @Insert(onConflict = IGNORE)
    abstract fun insert(category: Category): Long
    @Insert(onConflict = IGNORE)
    abstract fun insert(section: Section): Long
    @Insert(onConflict = IGNORE)
    abstract fun insert(sectionCategoryMap: SectionCategoryMap): Long

    @Transaction
    @Query("SELECT * FROM section")
    abstract fun getAllSectionWithCategories(): List<SectionWithCategories>
}
    • onConflict IGNORE has been coded for the inserts so that duplicates are ignored without an exception occurring so the following code is rerunnable
    • Long will be the rowid (a normally hidden column), it will be -1 if the insert was ignored.

    Assuming a pretty standard @Database class (other than using allowMainThreadQueries, for brevity and convenience of the demo) was used, it being :-

    @Database(
    entities = [
        Category::class,
        Section::class,
        SectionCategoryMap::class
    ],
    version = DBVERSION
)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao
    companion object {
        const val DBVERSION = 1
        const val DBNAME = "my.db"
        @Volatile
        private var instance: TheDatabase? = null

        fun getInstance(context: Context): TheDatabase {
            if (instance == null) {
                instance = Room.databaseBuilder(
                    context,
                    TheDatabase::class.java,
                    DBNAME
                )
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

    Then in an activity the following :-

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

    dao.insert(Category("quotes","General",true))
    dao.insert(Category("favorites","Favorites",true))
    dao.insert(Category("positivity","Positive Thinking", false))
    dao.insert(Category("love","Love",false))

    dao.insert(Section("Section1","Section1"))
    dao.insert(Section("Section2","Section2"))
    dao.insert(Section("Bonus","Bonus Section"))

    dao.insert(SectionCategoryMap("Section1","quotes"))
    dao.insert(SectionCategoryMap("Section1","favorites"))
    dao.insert(SectionCategoryMap("Section2","positivity"))
    dao.insert(SectionCategoryMap("Section2","love"))

    /* Extra Data */
    dao.insert(SectionCategoryMap("Bonus","love"))
    dao.insert(SectionCategoryMap("Bonus","favorites"))
    dao.insert(SectionCategoryMap("Bonus","positivity"))
    dao.insert(SectionCategoryMap("Bonus","quotes"))
    dao.insert(Section("Empty","Section with no Categories"))

    val TAG = "DBINFO"
    for(swc: SectionWithCategories in dao.getAllSectionWithCategories()) {
        Log.d(TAG,"Section is ${swc.section.name} ID is ${swc.section.id} categories are:-")
        for (c: Category in swc.categories) {
            Log.d(TAG,"\tCategory is ${c.name} ID is ${c.id} IS FREE is ${c.is_free}")
        }
    }
    • inserts the Categories as per your data
    • inserts the Sections, the two as per your data and another two
    • inserts the SectionCategoryMap rows that reflects your data and also additional mappings for the two new Sections (Bonus Section maps to all Categories, Empty Section maps to no Categoires).
    • Extracts all the Sections with the respective Categories as SectionWithCategories objects and writes the result to the log.

    The result output to the log being:-

    2021-10-03 09:05:04.250 D/DBINFO: Section is Section1 ID is Section1 categories are:-
2021-10-03 09:05:04.250 D/DBINFO:   Category is Favorites ID is favorites IS FREE is true
2021-10-03 09:05:04.251 D/DBINFO:   Category is General ID is quotes IS FREE is true
2021-10-03 09:05:04.251 D/DBINFO: Section is Section2 ID is Section2 categories are:-
2021-10-03 09:05:04.251 D/DBINFO:   Category is Love ID is love IS FREE is false
2021-10-03 09:05:04.251 D/DBINFO:   Category is Positive Thinking ID is positivity IS FREE is false
2021-10-03 09:05:04.251 D/DBINFO: Section is Bonus Section ID is Bonus categories are:-
2021-10-03 09:05:04.251 D/DBINFO:   Category is Favorites ID is favorites IS FREE is true
2021-10-03 09:05:04.251 D/DBINFO:   Category is Love ID is love IS FREE is false
2021-10-03 09:05:04.251 D/DBINFO:   Category is Positive Thinking ID is positivity IS FREE is false
2021-10-03 09:05:04.251 D/DBINFO:   Category is General ID is quotes IS FREE is true
2021-10-03 09:05:04.251 D/DBINFO: Section is Section with no Categories ID is Empty categories are:-

    If you really want the data as JSON then you could use :-

        for(swc: SectionWithCategories in dao.getAllSectionWithCategories()) {
        Log.d(TAG, " SECTION JSON = ${Gson().toJson(swc.section)} CATEGROIES JSON = ${Gson().toJson(swc.categories)}")
    }

    in which case the output would be :-

    2021-10-03 09:24:34.954 D/DBINFO:  SECTION JSON = {"id":"Section1","name":"Section1"} CATEGROIES JSON = [{"id":"favorites","is_free":true,"name":"Favorites"},{"id":"quotes","is_free":true,"name":"General"}]
2021-10-03 09:24:34.956 D/DBINFO:  SECTION JSON = {"id":"Section2","name":"Section2"} CATEGROIES JSON = [{"id":"love","is_free":false,"name":"Love"},{"id":"positivity","is_free":false,"name":"Positive Thinking"}]
2021-10-03 09:24:34.960 D/DBINFO:  SECTION JSON = {"id":"Bonus","name":"Bonus Section"} CATEGROIES JSON = [{"id":"favorites","is_free":true,"name":"Favorites"},{"id":"love","is_free":false,"name":"Love"},{"id":"positivity","is_free":false,"name":"Positive Thinking"},{"id":"quotes","is_free":true,"name":"General"}]
2021-10-03 09:24:34.962 D/DBINFO:  SECTION JSON = {"id":"Empty","name":"Section with no Categories"} CATEGROIES JSON = []