How can I use joins in a RawQuery with Room?

Question

I have a query with about 15 possible conditional. I would like to avoid this answer because I don't want to update the DAO to add new options.

My data looks like:

@Entity
data class Author(
    @PrimaryKey
    val id: UUID,
    val name: Date,
    val birthday: Date,
)

@Entity
data class Book(
    @PrimaryKey
    val id: UUID,
    val authorId: UUID,
    val name: String,
    val published: Date,
)

data class AuthorWithBooks(
    @Embedded val author: Author,
    @Relation(parentColumn = "id", entityColumn = "authorId", entity = Book::class)
    val books: List<Books>,
)

There is no foreign key reference because this data is all backed by another source and it's possible that the book is saved before the author.

The DAO is something like:

@Dao
interface AuthorDao {
    .
    .
    .
    @Transaction
    @RawQuery(observedEntities = [AuthorWithBooks::class])
    fun observeByQuery(query: SupportSQLiteQuery): PagingSource<Int, AuthorWithBooks>
}

And the repository is something like this:

class AuthorRepository(
    private val dao: AuthorDao
) {
    fun search(arguments: List<Pair<String, Any?>>): Flow<PagingData<Author>> {
        val queryBuilder = SupportSQLiteQueryBuilder.builder("Author JOIN Book")
        val conditions = arguments.map { it.first }
        val whereArgs = arguments.mapNotNull { it.second }
        queryBuilder.selection(conditions.joinToString(" AND "), whereArgs.toTypedArray())
        val query = queryBuilder.create()
        return Pager(
            config = PagingConfig(pageSize = 10),
            pagingSourceFactory = { dao.observeByQuery(query) }
        ).flow
    }
}

My access to this is something like:

val results = authorRepository(listOf(
    Pair("Book.name LIKE ?", pattern),
    Pair("Author.birthday < ?", bornBefore)
))

And this returns one AuthorWithBooks more times than I can count. Is it possible to use RawQuery to do what I want? What is the mistake here? I might create a simple project to demonstrate this if no one sees the issue.

score 0 · Answer 1 · answered Aug 04 '21 at 20:21

The problem was the JOIN.

Removing the join from the builder like:

class AuthorRepository(
    private val dao: AuthorDao
) {
    fun search(arguments: List<Pair<String, Any?>>): Flow<PagingData<Author>> {
        val queryBuilder = SupportSQLiteQueryBuilder.builder("Author")
        val conditions = arguments.map { it.first }
        val whereArgs = arguments.mapNotNull { it.second }
        queryBuilder.selection(conditions.joinToString(" AND "), whereArgs.toTypedArray())
        val query = queryBuilder.create()
        return Pager(
            config = PagingConfig(pageSize = 10),
            pagingSourceFactory = { dao.observeByQuery(query) }
        ).flow
    }
}

and using subqueries in the search parameters like:

val results = authorRepository(listOf(
    Pair("EXISTS (SELECT 1 FROM Book WHERE (Book.authorId = Author.id) AND (Book.name LIKE ?)", pattern),
    Pair("Author.birthday < ?", bornBefore)
))

How can I use joins in a RawQuery with Room?

1 Answers1