Hardcode Boolean Query In Room Database

Question

I'm building an Android application that displays a list of potential matches for a user. The user can click on one to like the user, and I save all of those likes locally.

I can write a query to get the list of matches like this:

@Query("SELECT * FROM match WHERE liked = :liked ORDER BY match DESC LIMIT :limit")
fun getMatches(limit: Int = 6, liked: Boolean = true): Flowable<List<Match>>

I've learned that this works fine. However, I don't foresee any scenario where I'll ever set liked to false, and so I'm curious if there is a way to hardcode my Boolean condition? If I try:

@Query("SELECT * FROM match WHERE liked = true ORDER BY match DESC LIMIT :limit")

I get the following error at compile time:

Error:(8, 0) Gradle: error: There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such column: true)

How can I hard code this Boolean in my query string?

I have also tried:

• Wrapping the condition in single quotes
  • @Query("SELECT * FROM match WHERE liked = 'true' ORDER BY match DESC LIMIT :limit")

Answer 1

SQLite does not have a boolean data type. Room maps it to an INTEGER column, mapping true to 1 and false to 0.

So, I would expect this to work:

@Query("SELECT * FROM match WHERE liked = 1 ORDER BY match DESC LIMIT :limit")

Bear in mind that this behavior is undocumented. However, it shouldn't change — at least not without alarm klaxons sounding — as we'd need to use migrations to deal with any changes.

Answer 2

CommonWare's approach does work and also answers the OPs question directly; however, I'm not a fan of making such an assumption about the database. The assumption should be safe, but it may create unexpected work down the road if Room ever decides to change it's boolean implementation.

I'd suggest that the better approach is to not hardcode the boolean 1 or 0 into the query. If the database is behind a repository, it is still possible for the repository to expose a graceful API. Personally, I think shielding the larger codebase from the database implementation is a good thing anyways.

Dao Method (copied from OP's question)

@Query("SELECT * FROM match WHERE liked = :liked ORDER BY match DESC LIMIT :limit")
fun getMatches(limit: Int = 6, liked: Boolean = true): Flowable<List<Match>>

Repository

class Repository {
    public Flowable<List<Match>> getLikedMatches() {
        return dao.getMatches(6, true);
    }
}

Of course, this is an opinionated option in that it assumes a certain architectural style. However, it does not make assumptions about the internal database. Even without the repository shielding the database, the call can be made into the database by passing true everywhere - also without making assumptions as to the underlying data.

Answer 3

You don't have to compare boolean column to a value. Just use the column value itself as a boolean expression. You can easily change your query to SELECT * FROM match WHERE liked ORDER BY match DESC LIMIT :limit. If you want to compare to false value you can use following expression: where not liked.