14

I want to query the user associations list with the following room query using public constant variable Association.MEMBER_STATUS_APPROVED.

@Query("SELECT * FROM Association WHERE memberStatus = " + Association.MEMBER_STATUS_APPROVED)
LiveData<List<Association>> loadUserAssociations();

But, room gives me [SQLITE_ERROR] when build. It is possible to re-write that query by replacing the constant variable with parameter like the following.

@Query("SELECT * FROM Association WHERE memberStatus = :statusApproved")
LiveData<List<Association>> loadUserAssociations(String statusApproved);

I would like to know that does Room support such kind of string concatenation or String Format? (or) May be I missing something?

Alireza Noorali
  • 3,129
  • 2
  • 33
  • 80
Toe Lie
  • 551
  • 1
  • 5
  • 8

3 Answers3

6

For those who have the same problem, the following could be the solution in Kotlin:

@Query("SELECT * FROM Association WHERE memberStatus = :statusApproved")
loadUserAssociations(statusApproved: String = Association.MEMBER_STATUS_APPROVED): LiveData<List<Association>>

And I think it is more clean way than hardcoding or passing obvious constant into the function.

Sirojiddin Komolov
  • 771
  • 10
  • 17
  • Can we use enums in the query like the following "SELECT * FROM Table WHERE status = STATES.RUNNING.ordinal()" – K Pradeep Kumar Reddy Nov 03 '20 at 04:27
  • Not sure but I'd expect you need a converter for the Enums. So your converter might look like this: [at]TypeConverter fun toEnum(value: MyEnum?) = value?.let { enumValueOf(it) } [at]TypeConverter fun fromEnum(value: MyEnum?) = value?.name not sure it works but give it a try. – Tobias Reich Jan 20 '21 at 11:51
1

you can query like below.

Instead of

@Query("SELECT * FROM Association WHERE memberStatus = " + Association.MEMBER_STATUS_APPROVED) LiveData<List<Association>> loadUserAssociations();

use

@Query("SELECT * FROM Association WHERE memberStatus=${Association.MEMBER_STATUS_APPROVED}) LiveData<List<Association>> loadUserAssociations();

Varun A M
  • 1,103
  • 2
  • 14
  • 29
  • 3
    Do you have a link to the reference docs for this? I can't get "expression expected" compile time error on the $ sign – peterpie Jul 10 '21 at 17:24
0
  • problem

if you make a query like this,

"SELECT * FROM Association WHERE memberStatus = " + Association.MEMBER_STATUS_APPROVED

it's just

"SELECT * FROM Association WHERE memberStatus = somevalue"

sql cannot know "somvalue" is a string.

  • try this (wrap it with quotation mark)

"SELECT * FROM Association WHERE memberStatus = '" + Association.MEMBER_STATUS_APPROVED + "'"

yeonseok.seo
  • 2,696
  • 3
  • 13
  • 14