How should a custom query segment be passed to @query in RoomDb

Question

I am trying to pass a Where clause to the existing query in RoomDbD

@Query("SELECT products_table.id,products_table.name as productName,products_table.image,variations_table.name as variation,variations_table.id as variation_id,variations_table.default_sell_price as selling_price  from products_table INNER JOIN variations_table ON products_table.id = variations_table.product_id LEFT JOIN variations_locations_details_table as VLD ON variations_table.id = VLD.variation_id || :whereClause ")
LiveData<List<VariedProducts>> getProductsWithVariations(String whereClause);

It returns all the data irrespective of any query such as

productsDao.getProductsWithVariations("WHERE products_table.id = 4");

Any room DB users assist

Japheth Ongeri - inkalimeva · Answer 1 · 2018-09-11T14:06:34.630

0

You don't pass the where clause like in old android SQLite, instead you pass parameters to be used in the where clause.

In Room you write the whole query including the where clause with named parameters like in PHP DBO or Java JDBC, then pass the parameters when calling the method.

@Query("SELECT * FROM user WHERE username LIKE :username LIMIT 1")
LiveData<User> findByUserName(String username);

So for your case the correct method would be:

@Query("SELECT products_table.id,products_table.name as productName,products_table.image,variations_table.name as variation,variations_table.id as variation_id,variations_table.default_sell_price as selling_price  from products_table INNER JOIN variations_table ON products_table.id = variations_table.product_id LEFT JOIN variations_locations_details_table as VLD ON variations_table.id = VLD.variation_id WHERE products_table.id = :productId")
LiveData<List<VariedProducts>> getProductsWithVariations(Integer productId);

edited Sep 11 '18 at 14:06

answered Sep 11 '18 at 13:54

Japheth Ongeri - inkalimeva

4,909
6
29
46

i get the idea behind room but this is my dilema i wanted to modify the result based on brand,category or a search string for the product name. sometimes the user filters by brand alone or category alone or the search string alone and other by a combination of either. I dont want to write 9 conditions for the solution and room isnt working with SQL SELECT WHERE CASE THEN. How would you solve this? – DenoGeek Sep 11 '18 at 14:04
For that case I think you need optional parameters, check out https://stackoverflow.com/questions/52029435/option-query-parameters-for-android-room – Japheth Ongeri - inkalimeva Sep 11 '18 at 14:08

score 0 · Answer 2 · answered Sep 11 '18 at 14:29

0

You can not pass the column as a variable

just use multiple queries

answered Sep 11 '18 at 14:29

DenoGeek

39
1
2

How should a custom query segment be passed to @query in RoomDb

2 Answers2