Android Room Many to Many Junction table getting more than relation of 2 tables

Question

I have created a relation between User, Property and junction table of these two items UserPropertyJunction which can be visualized as releation in the image below

UserEntity, instead of customers, which is

@Entity(tableName = "user")
data class UserEntity(
    @PrimaryKey
    val userId: Long,
    val firstName: String,
    val lastName: String,
    val email: String,
    val password: String
)

Property, instead of products,

@Entity(
    tableName = "property",
    primaryKeys = ["id"]
)
data class Property(
    val id: Int,
    val update: Int,
    val categoryId: Int,
    val title: String,
    val subject: String,
    val type: String,
    val typeId: Int
}

And Junction table instead of product_helper

@Entity(
    tableName = "user_property_junction",
    primaryKeys = ["userAccountId", "propertyId"],
    // Foreign Keys
    foreignKeys = [
        ForeignKey(
            entity = User::class,
            parentColumns = ["userId"],
            childColumns = ["userAccountId"],
            onDelete = ForeignKey.NO_ACTION
        ),
        ForeignKey(
            entity = Property::class,
            parentColumns = ["id"],
            childColumns = ["propertyId"],
            onDelete = ForeignKey.NO_ACTION
        )
    ]
)
data class UserPropertyJunction(
    val userAccountId: Long,
    val propertyId: Int
)

And created relation class with

data class UserWithFavorites(

    @Embedded
    val user: User,

    @Relation(
        parentColumn = "userId",
        entity = Property::class,
        entityColumn = "id",
        associateBy = Junction(
            value = UserPropertyJunction::class,
            parentColumn = "userAccountId",
            entityColumn = "propertyId"
        )
    )
    val propertyList: List<Property>
)

Also need to get data of how many times these properties displayed and liked by users.

And for that checked out the solution in this link which adds additional field to junction, in my case adding displayCount and favorite properties

data class UserPropertyJunction(
    val userAccountId: Long,
    val propertyId: Int,
    val displayCount:Int=0,
    val favorite:Boolean=false
)

My first question is as far as i have seen, not much experience with junction or associative tables, they only store foreign keys for the tables that should associate with, is it okay to add value fields to junction table?

If it's not elegant or not preferred way of doing it, should i add another table that has relation with junction table such as

data class PropertyStatus(
    val userAccountId: Long,
    val propertyId: Int,
    val displayCount:Int=0,
    val favorite:Boolean=false
)

and associate them?

And when retrieving data from both properties of a User and status of properties should i manually get data from

data class UserWithProperties(

    @Embedded
    val user: User,

    @Relation(
        parentColumn = "userId",
        entity = Property::class,
        entityColumn = "id",
        associateBy = Junction(
            value = UserPropertyJunction::class,
            parentColumn = "userAccountId",
            entityColumn = "propertyId"
        )
    )
    val propertyList: List<Property>
)

and get with SELECT from status table or junction table based on your answer to first question or is it possible to add another relation to UserWithProperties with @Embedded or @Relation

Yacht

Answer 1

As far as I know, unfortunately there is no out-of-the-box way, how you can solve your use case with just Room's tools (@Relation, @Embedded, @Junction).

My first question is as far as i have seen, not much experience with junction or associative tables, they only store foreign keys for the tables that should associate with, is it okay to add value fields to junction table?

The thing is @Junction has some restriction in use - it just helps to bind two tables with the values that kept in third (junction) table. But @Relation-with-@Junction API doesn't support getting from this junction table any field to include to result class (these foreign keys are used only for binding). That's why technically you can add some fields to the junction table (and it seems it's the most suitable place where you should keep these values), but in practice you just couldn't get these fields using @Junction.

Maybe there is some hack-ish way you could use for that, but my guess - you'll have to implement your own method with SQL joins and passing result with loops to form needed result (similar to that was implemented in the link you mentioned in your post).

As a simplification to your case you could really describe Entity-junction table as you suggested (but not to use @Junction at all):

data class UserPropertyJunction(
    val userAccountId: Long,
    val propertyId: Int,
    val displayCount:Int=0,
    val favorite:Boolean=false
)

and then to add auxiliary class (not entity) and use it as a query result:

data class UserWithFavorites(
    val displayCount:Int,
    val favorite:Boolean,
    @Relation(
         parentColumn = "userAccountId",
         entityColumn = "userId"
    )
    val user: UserEntity,
    @Relation(
         parentColumn = "propertyId",
         entityColumn = "id"
    )
    val property: Property,
)

Of course, that's not what you want but at least you can deal with that, it's out-of-the-box and you can use LiveData/Flow/RxJava with that (for example, after getting this you could try to use some transformation operators to somehow change it to needed format)

UPDATE (for simplified version)

If you don't need users in result, but you just want to filter by userId then your auxiliary class could be as follows:

data class PropertiesWithFavorites(
    val displayCount:Int,
    val favourite:Boolean,
    val propertyId: Long,
    @Relation(
         parentColumn = "propertyId",
         entityColumn = "id"
    )
    val property: Property
)

And dao method:

@Query("SELECT * FROM user_property_junction as j where j.userAccountId =:userId")
fun getPropertiesByUser(userId: Long): List<PropertiesWithFavorites>  

Kellar

This is not exactly what i hoped from Room annotations but if that's the way to do it i would do it that way. I have a question though, would you mind writing the exact statement for getting UserAndLibrary properties with SELECT and JOIN? I also don't even need user, for these query only need the property and how many times it's displayed and whether it's favorited by the user with userAccountId – Yacht

My other question is why UserEntity is annotated with @Relation instead of @Embeded, what's the difference? – Yacht

Thanks to Room the query is very simple: select * from user_property_junction (like in all @Relations). Type of query's Result - List<UserAndProperty>. All the rest magic Room will do considering UserAndProperty annotations. There is no JOIN needed as well. – Kellar

Relation - is a Room's replacement for JOIN. You don't need to write JOINs manually, Room will generate needed result. I guess it was made for those who are frightened by SQL syntax, that's why query with Relation is so simple. – Kellar

It should work, if you're against relations, then in mentioned class you should replace Relation annotation with Embedded. With relations for the same - you can just remove User field from mentioned class and change query to select * from user_property_junction as j where j.userAccountId='1' – Kellar

In UserAndLibrary class why it's single Property instead of a list? And from which table should i query with SELECT to get the whole class of data class UserAndLibrary with. Can you post a full implementation for the query, i get an error when i query from any table no matter what – Yacht

Updated my answer for simplified version. So you put userId to method and should get List of properties with favourites and display count – Kellar