11

I have the following DAO with a query:

@Dao
public interface BaseballCardDao {
    @Query(
        "SELECT * FROM baseball_cards " +
        "WHERE brand LIKE :brand " +
        "  AND year = :year " +
        "  AND number LIKE :number " +
        "  AND player_name LIKE :playerName " +
        "  AND team LIKE :team"
    )
    LiveData<List<BaseballCard>> getBaseballCards(
        String brand, int year, String number, String playerName, String team
    );
}

The String parameters are "optional" in the sense that I can pass "%%" to match all rows due to the LIKE operator. But I cannot do this with year since it is an int. One solution is to add two different @Query methods, one with the int year parameter and the other without. Is there a more elegant way to create an optional parameter with Room's @Query?

Code-Apprentice
  • 81,660
  • 23
  • 145
  • 268
  • What would your method signature be for one where `year` is optional? Would you be keeping this signature and using some magic `year` value that means "ignore the year"? If so, you might be able to do something like `AND (year = :year OR -1 = :year)`, assuming `-1` is your magic value. – CommonsWare Aug 26 '18 at 19:10
  • @CommonsWare Yes, keeping the signature and modifying the query is along the lines of what I had in mind. Will you post that as an answer? – Code-Apprentice Aug 26 '18 at 19:16

1 Answers1

13

It is a late answer but as I have faced it recently, I wanted to share my simple (but silly!) trick for those who are looking for it.

As @CommonsWare has said, we can add an OR statement that checks for null to it and then simply make our optional parameters nullable and pass null for them. For example, your query would look like:

@Dao
public interface BaseballCardDao {
    @Query(
        "SELECT * FROM baseball_cards " +
        "WHERE (:brand IS NULL OR brand LIKE :brand)" +
        "  AND (:year IS NULL OR year = :year)" +
        "  AND (:number IS NULL OR number LIKE :number)" +
        "  AND (:playerName IS NULL OR player_name LIKE :playerName)" +
        "  AND (:team IS NULL OR team LIKE :team)"
    )
    LiveData<List<BaseballCard>> getBaseballCards(
        @Nullable String brand, @Nullable Integer year, @Nullable String number, @Nullable String playerName, @Nullable String team
    );
}

Or more declarative using kotlin and optional parameters:

@Query(
    """SELECT * FROM baseball_cards 
        WHERE (:brand IS NULL OR brand LIKE :brand) 
        AND (:year IS NULL OR year = :year) 
        AND (:number IS NULL OR number LIKE :number) 
        AND (:playerName IS NULL OR player_name LIKE :playerName)
        AND (:team IS NULL OR team LIKE :team)"""
)
fun getBaseballCards(
    brand: String? = null,
    year: Int? = null,
    number: String? = null,
    playerName: String? = null,
    team: String? = null
): LiveData<List<BaseballCard>>

Edit: Please consider that this solution is useful for non-nullable fields. If the field is nullable and you want to look for the records which don't have a value for the field, this is not the correct way of querying and you may consider dynamic query creation.

momvart
  • 1,737
  • 1
  • 20
  • 32
  • In my original code, I used `int year` instead of `Integer year`. Using a wrapper here might be a better solution than the SQL hack suggested by CommonsWare. – Code-Apprentice Apr 15 '20 at 15:04
  • Also, is there a reason you a reason you didn't modify `brand LIKE :brand` to add a NULL check? – Code-Apprentice Apr 15 '20 at 15:06
  • @Code-Apprentice No, It seems that I have missed it! thanks for your attention. edited. – momvart Apr 15 '20 at 15:14
  • 1
    This is an awful approach, because it may mess up the whole result-set. The question actually asked for a "more elegant way" ...now please explain to me how this would be more elegant than defining individual methods and switch them externally? – Martin Zeitler Apr 15 '20 at 15:51
  • @MartinZeitler It was not meant to be an "elegant" approach in that way. As room doesn't have a built-in support for it, it is a simple trick to not write 2^5 = 32 functions for each possible form of optional parameters and also using the `Query` annotation. Surely, it has performance overhead and if we want to employ the best approach, we need to write the raw query manually. – momvart Apr 15 '20 at 17:16
  • @MohammadOmidvar its not necessary about being elegant (for me at least), its about being correct. This solution will give an incorrect result to the query – user1202032 Jun 25 '20 at 09:01
  • @user1202032 could you please give an example in which a wrong result is produced? – momvart Jun 25 '20 at 09:40
  • It doesn't work in case argument is an array unfortunately :( – XZen Mar 28 '21 at 09:52
  • @XZen Yeah, I myself wrote custom queries for those cases. But you may achieve it using some tricks like: `WHERE coalesce(NULL, NULL, :theArray) IS NULL OR column IN (:theArray)` which I'm not sure would be working and don't believe is a clean way. – momvart Mar 28 '21 at 12:19
  • 1
    @momt99 Thank you for advice, unfortunately cannot make it working with room - in my case `(COALESCE(NULL, :myArray ) IS NULL) ` is becoming `(COALESCE(NULL, ) IS NULL) ` at runtime (when I check logs) in case I provide empty array as myArray argument. The only working way I found is to pass 'special' array with only one element and just compare it like `WHERE (:myArray) = (-1) OR (col IN (:myArray)). It's very ugly and inefficient I guess. Another way could be passing 'myArray' size as a separate parameter. – XZen Mar 28 '21 at 13:46
  • 1
    @momt99 nice hack, but might return wrong result, as said above. Imagine the case: you want to fetch all items where 'brand' is null; with this approach 'brand' condition will be neglected (":brand IS NULL" is fulfilled, check for "OR brand LIKE :brand" is omitted), and you'll get all items, with 'brand = null' and 'brand != null'. But should work fine for the case when 'brand' column in Entity is defined as non-nullable. – ernazm Apr 27 '21 at 10:38
  • @ernazm yeah, you're right. it doesn't work correctly in nullable cases. – momvart Apr 27 '21 at 13:50