Get a list of Item with some other data (With the id of this data inside of the item)

Question

I have some products in my app and user can create Space if he want. In the home page, I'm showing all the product of the users. If the user has stored the product inside an space, in the home page you will see in the product item, the name of the space.

Do you get it?

You can have multiple Space and Multiple Product, so you can have many product in a space.

My first solution was, when the user is adding a product to a space, he stores the space data in the product too, then i show him a little chips with the space name in the product, but it's not very clean to do this, so on each Product the user store, I'm storing the id of the space if it exist.

How can I get the space data on each item ? (without doing one query on each product retrieved)

Answer 1

Without the specific schema, it is hard to say. However perhaps consider the following working demo that appears to cater for your scenarios.

There are 3 core @Entity annotated classes for User, Product and Space as per :-

@Entity
data class User(
    @PrimaryKey
    var userId: Long?=null,
    var userName: String
)
@Entity
data class Product(
    @PrimaryKey
    var productId: Long?=null,
    var productName: String
)
@Entity(
    foreignKeys = [
        ForeignKey(
            entity = User::class,
            parentColumns = ["userId"],
            childColumns = ["spaceUserId"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class Space(
    @PrimaryKey
    var spaceId: Long?=null,
    var spaceName: String,
    @ColumnInfo(index = true)
    var spaceUserId: Long
)

• Note that a Space has a relationship to the User that owns the Space (based upon you saying that "user can create Space") i.e. spaceUserId maps to the parent userId.
  • as such, the Foreign Key constraint has been specified, (this is optional)
    • This is a rule that says the spaceUserId MUST be an existing userid, and thus enforces referential integrity.
    • the onDelete CASCADE will automatically delete the children if the parent is deleted.
    • onUpdate is similar, but only if the respective key in the parent is updated (not any other column)

As a User can have many Products and assuming that another user can use products another user has used, then a many-many relationship is required. As such an intermediate table is utilised that maps(associates/relates/references) a User to a Product.

So :-

@Entity(
    primaryKeys = ["upm_userIdMap","upm_productIdMap"],
    foreignKeys = [
        ForeignKey(
            entity = User::class,
            parentColumns = ["userId"],
            childColumns = ["upm_userIdMap"],
            onDelete = ForeignKey.CASCADE,
            onUpdate =ForeignKey.CASCADE
        ),
        ForeignKey(
            Product::class,
            ["productId"],
            ["upm_productIdMap"],
            ForeignKey.CASCADE,
            ForeignKey.CASCADE)
    ]
)
data class UserProductMap(
    val upm_userIdMap: Long,
    @ColumnInfo(index = true)
    val upm_productIdMap: Long
)

• Note the mixture of the long and short forms of ForeignKey
• again using ForeignKeys is optional
• note the index on the second column, this could be more efficient and will supress Room issuing a warning.

Likewise a Space can have Products used by other spaces which may or may not be spaces owned by the same user and therefore a many-many relationship and a mapping table. As such :-

@Entity(
    primaryKeys = ["spm_spaceIdMap","spm_productIdMap"],
    foreignKeys = [
        ForeignKey(
            Space::class,
            ["spaceId"],
            ["spm_spaceIdMap"],
            ForeignKey.CASCADE,
            ForeignKey.CASCADE
        ),
        ForeignKey(
            Product::class,
            ["productId"],
            ["spm_productIdMap"],
            ForeignKey.CASCADE,
            ForeignKey.CASCADE
        )
    ]
)
data class SpaceProductMap(
    val spm_spaceIdMap: Long,
    @ColumnInfo(index = true)
    val spm_productIdMap: Long
)

Now some POJO's for extracting data. As the hierarchy will have Users referencing Spaces which then reference Products then you want a Space with it's Products POJO as well as a User with the directly specified Products as well as the Spaces with referenced Products.

So for the Spaces with it's Products :-

data class SpaceWithProducts(
    @Embedded
    val space: Space,
    @Relation(
        Product::class, parentColumn = "spaceId", "productId",
        associateBy = Junction(
            SpaceProductMap::class,"spm_spaceIdMap","spm_productIdMap"
        )
    )
    val productList: List<Product>
)

• as can be seen this associates (references/map/relates) the Products with the Space via the SpaceProductMap table(entity).

For the User with it's Products and Also the User's Spaces with the Space's Products :-

data class UserWithProductsAndWithSpacesWithProducts(
    @Embedded
    val user: User,
    @Relation(
        Product::class,
        parentColumn = "userId",
        entityColumn = "productId",
        associateBy = Junction(
            UserProductMap::class, parentColumn = "upm_userIdMap", entityColumn = "upm_productIdMap"
        )
    )
    val userProductList: List<Product>,
    @Relation(
        Space::class,"userId", entityColumn = "spaceUserId"
    )
    val spacesWithproducts: List<SpaceWithProducts>
    )

The @Dao functions could be :-

@Dao
interface AllDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(user:User): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(product: Product): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(space: Space): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(userProductMap: UserProductMap): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(spaceProductMap: SpaceProductMap): Long

    @Transaction
    @Query("SELECT * FROM user")
    fun getAllUsersWithAllProducts(): List<UserWithProductsAndWithSpacesWithProducts>
    
}

• Thus the 3 core inserts, the 2 mapping inserts and a Query that will retrieve all the products directly linked to the User, an the Spaces with the Space's products.

Using the above and then using the following as a demo :-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()

        var u1 = User(userName = "user1")
        var u2 = User(userName = "user2")
        var u3 = User(userName = "user3")

        u1.userId = dao.insert(u1)
        u2.userId = dao.insert(u2)
        u3.userId = dao.insert(u3)

        var p1 = Product(productName = "product1")
        var p2 = Product(productName = "product2")
        var p3 = Product(productName = "product3")
        var p4 = Product(productName = "product4")
        p1.productId = dao.insert(p1)
        p2.productId = dao.insert(p2)
        p3.productId = dao.insert(p3)
        p4.productId = dao.insert(p4)

        var s1 = Space(spaceName = "User1 Space1", spaceUserId = u1.userId!!)
        s1.spaceId = dao.insert(s1)
        var s2 = Space(spaceName = "User 2 Space1", spaceUserId = u2.userId!!)
        s2.spaceId = dao.insert(s2)
        var s3 = Space(spaceName = "User1 Space 2", spaceUserId = u1.userId!!)
        s3.spaceId = dao.insert(s3)
        var s4 = Space(spaceName = "User1 Space3", spaceUserId = u1.userId!!)
        s4.spaceId =  dao.insert(s4)

        dao.insert(UserProductMap(u1.userId!!,p1.productId!!))
        dao.insert(UserProductMap(u1.userId!!,p3.productId!!))
        dao.insert(UserProductMap(u2.userId!!,p2.productId!!))
        dao.insert(UserProductMap(u3.userId!!,p1.productId!!))
        dao.insert(UserProductMap(u3.userId!!,p2.productId!!))
        dao.insert(UserProductMap(u3.userId!!,p3.productId!!))

        dao.insert(SpaceProductMap(s1.spaceId!!,p4.productId!!))
        dao.insert(SpaceProductMap(s2.spaceId!!,p1.productId!!))
        dao.insert(SpaceProductMap(s2.spaceId!!,p2.productId!!))
        dao.insert(SpaceProductMap(s3.spaceId!!,p1.productId!!))
        dao.insert(SpaceProductMap(s3.spaceId!!,p4.productId!!))
        dao.insert(SpaceProductMap(s4.spaceId!!,p1.productId!!))
        dao.insert(SpaceProductMap(s4.spaceId!!,p2.productId!!))
        dao.insert(SpaceProductMap(s4.spaceId!!,p3.productId!!))
        dao.insert(SpaceProductMap(s4.spaceId!!,p4.productId!!))

        for (uwap in dao.getAllUsersWithAllProducts()) {
            Log.d("DBINFO","User is ${uwap.user.userName}. There are ${uwap.userProductList.size} directly linked products. There are ${uwap.spacesWithproducts.size} Spaces.")
            Log.d("DBINFO"," The directly linked products are:-")
            for (p in uwap.userProductList) {
                Log.d("DBINFO","\tProduct is ${p.productName} Space is NOT IN A SPACE")
            }
            Log.d("DBINFO","The Products in Spaces are:-")
            for (swp in uwap.spacesWithproducts) {
                for (p in swp.productList) {
                    Log.d("DBINFO","\tProduct is ${p.productName} Space is ${swp.space.spaceName}")
                }
            }
        }

    }
}

The result output to the log is :-

D/DBINFO: User is user1. There are 2 directly linked products. There are 3 Spaces.
D/DBINFO:  The directly linked products are:-
D/DBINFO:   Product is product1 Space is NOT IN A SPACE
D/DBINFO:   Product is product3 Space is NOT IN A SPACE
D/DBINFO: The Products in Spaces are:-
D/DBINFO:   Product is product4 Space is User1 Space1
D/DBINFO:   Product is product1 Space is User1 Space 2
D/DBINFO:   Product is product4 Space is User1 Space 2
D/DBINFO:   Product is product1 Space is User1 Space3
D/DBINFO:   Product is product2 Space is User1 Space3
D/DBINFO:   Product is product3 Space is User1 Space3
D/DBINFO:   Product is product4 Space is User1 Space3


D/DBINFO: User is user2. There are 1 directly linked products. There are 1 Spaces.
D/DBINFO:  The directly linked products are:-
D/DBINFO:   Product is product2 Space is NOT IN A SPACE
D/DBINFO: The Products in Spaces are:-
D/DBINFO:   Product is product1 Space is User 2 Space1
D/DBINFO:   Product is product2 Space is User 2 Space1


D/DBINFO: User is user3. There are 3 directly linked products. There are 0 Spaces.
D/DBINFO:  The directly linked products are:-
D/DBINFO:   Product is product1 Space is NOT IN A SPACE
D/DBINFO:   Product is product2 Space is NOT IN A SPACE
D/DBINFO:   Product is product3 Space is NOT IN A SPACE
D/DBINFO: The Products in Spaces are:-