Get vararg parameter size in Room Query

Issue

Say I have a DB with two main entities (Song and Tag) and a many-to-many relationship between them. Using Room, I want to query the Songs that have a series of Tags (all of them) by their names.

So, given this example data in the cross ref table (SongTagCrossRef):

Song	Tag
song1	tag1
song1	tag2
song1	tag3
song2	tag2
song3	tag2
song3	tag3

I want the query to return only song1 if I enter tag1 and tag2, as it's the only song related to both.

I've come up with this @Query in the corresponding Dao:

@Query("""
    SELECT s.* FROM Song s
    JOIN SongTagCrossRef st ON s.song_id = st.song_id
    JOIN Tag t ON st.tag_id = t.tag_id
    WHERE t.name IN (:tagNames)
    GROUP BY s.song_id
    HAVING COUNT(*) = (SELECT COUNT(*) FROM Tag WHERE name IN (:tagNames))
""")
fun getSongsWithAllOfTheTagsByName(vararg tagNames: String): List<SongEntity>

Since I can't access tagNames.size in the @Query, I've had to use a subquery to artificially get it. This subquery shouldn't be too heavy, but it would always be better to somehow access tagNames.size.

After reading the answers to a slightly related question, I've been toying with creating a @RawQuery and calling it from a function that takes only tagNames, something along these lines:

@RawQuery
fun getSongsWithAllOfTheTagsByName(query: SupportSQLiteQuery): List<SongEntity>

fun getSongsWithAllOfTheTagsByName(vararg tagNames: String): List<SongEntity> {
    val query = SimpleSQLiteQuery("""
        SELECT s.* FROM Song s
        JOIN SongTagCrossRef st ON s.song_id = st.song_id
        JOIN Tag t ON st.tag_id = t.tag_id
        WHERE t.name IN (?)
        GROUP BY s.song_id
        HAVING COUNT(*) = ?
    """, arrayOf(tagNames, tagNames.size))
    return getSongsWithAllOfTheTagsByName(query)
}

(only converting tagNames to something it can actually swallow)

But I've discarded this approach because I don't want to expose a function that takes a query.

Is there a simpler, more elegant way to write this query?

Solution

I finally did it, so I want to share what I found out. It's actually not quite straightforward, but it does the trick.

Going through the SQLite documentation, I came upon the JSON1 extension and more specifically the json_array() and json_array_length() functions.

However, to use this extension, as CommonsWare points out in this answer and Hooman summarises here, Requery's standalone library must be used, through RequerySQLiteOpenHelperFactory.

In conclusion:

build.gradle file

dependencies {
    ...
    implementation 'com.github.requery:sqlite-android:3.36.0'
    ...
}

Room database class

Room.databaseBuilder(...)
    ...
    .openHelperFactory(RequerySQLiteOpenHelperFactory())
    ...
    .build()

Dao interface

@Query("""
    SELECT s.* FROM Song s
    JOIN SongTagCrossRef st ON s.song_id = st.song_id
    JOIN Tag t ON st.tag_id = t.tag_id
    WHERE t.name IN (:tagNames)
    GROUP BY s.song_id
    HAVING COUNT(*) = JSON_ARRAY_LENGTH(JSON_ARRAY(:tagNames))
""")
fun getSongsWithAllOfTheTagsByName(vararg tagNames: String): List<SongEntity>

Answered By - Mario MG