How to Query in a many to many relationship Room database?-CodePudding

I have a many to many relationship Room database with three tables:

First one :

data class Name(
@PrimaryKey(autoGenerate = true)
var nameId : Long = 0L,
@ColumnInfo(name = "name")
var name : String = "",
@ColumnInfo(name = "notes")
var notes: String=""
)

Second:

@Entity(tableName = "tags_table")
data class Tag(
@PrimaryKey(autoGenerate = true)
var tagId : Long = 0L,
@ColumnInfo(name = "tag_name")
var tagName : String = ""
)

Third:

@Entity(
    tableName = "tagInName_table",
    primaryKeys = ["nameId", "tagId"],
    foreignKeys = [
        ForeignKey(
            entity = Name::class,
            parentColumns = ["nameId"],
            childColumns = ["nameId"]
        ),
        ForeignKey(
            entity = Tag::class,
            parentColumns = ["tagId"],
            childColumns = ["tagId"]
        )
    ]
)
data class TagInName(
    @ColumnInfo(name = "nameId")
    var nameId: Long = 0L,
    @ColumnInfo(name = "tagId")
    var tagId: Long = 0L

)

The data class I use for a return object in a Query:

data class NameWithTags(
    @Embedded
    val name: Name,
    @Relation(
        parentColumn = "nameId",
        entityColumn = "tagId",
        associateBy = Junction(value = TagInName::class)
    )
    val listOfTag : List<Tag>
)

This is how I query to get all NamesWithTags:

@Query("SELECT * FROM names_table")
@Transaction
fun getNamesWithTags() : LiveData<List<NameWithTags>>

So the thing I need to do is, I need to Query to return LiveData<List<NameWithTags>> where every NamesWithTags has a list which contains the Tag ID that I Query for.

CodePudding user response：

From my interpretation of what you say you need to do, then :-

@Transaction
@Query("SELECT names_table.* FROM names_table JOIN tagInName_table ON names_table.nameId = tagInName_table.nameId JOIN tags_table ON tagInName_table.tagId = tags_table.tagId WHERE tags_table.tagId=:tagId ")
fun getNameWithTagsByTagId(tagId: Long): LiveData<List<NamesWithTags>>

Note the above is in-principle code and has not been compiled or tested, so it may contain some errors.
A NameWithTags will contain ALL related tags whcih should be fine according to (where every NamesWithTags has a list which contains the Tag ID ), if you wanted just certain Tags in the List of Tags then it's a little more complex, this is explained in a recent answer at Android Room query with condition for nested object