1

I am using Room for DB and have a table by the name of 'Products'. Products Entity class has field ArrayList'String' tags.

@Entity Class Products { some auto generate primary key; String name; ArrayList<String>tags;}

I want to search products by tag, so how to do it? Can we use 'tags' in where clause? I tried below but didn't work:

select name from Products where tags IN (:value) List<String> getSearchedProducts(String value);

Using this type converter to save list.

public class StringListConverters {

private static Gson gson = new Gson();

@TypeConverter
public static ArrayList<String> fromString(String data) {
    Type listType = new TypeToken<ArrayList<String>>() {}.getType();
    return gson.fromJson(data, listType);
}

@TypeConverter
public static String fromArrayList(ArrayList<String> list) {
    return gson.toJson(list);
}

}

For search by name it works but for search by tag it doesn't. More than syntax, error fix etc. here I am looking for approach to search Collections in Room DB.

Sharad
  • 589
  • 5
  • 19

2 Answers2

3

I got the answer, its pretty simple. So that's how we search through the ArrayList which is stored in column.

select name from Products where tags Like :value List<String> getSearchedProducts(String value);

It makes sense also because Room converts list in to String entries and save in column and according to my assumption while searching through ArrayList it will be looping in that column like foreach loop.

And to search by name or by tag you will do like this:

select name from Products where name Like :value OR tags Like :value List<String> getSearchedProducts(String value);

Sharad
  • 589
  • 5
  • 19
0

What you are trying to achieve works, I had the same problem. In my case I had a String[], it doesn't work if you provide a String separated by commas, but it should work with ArrayList as well.

If you don't manipulate the tags list a lot and don't add elements to it after its creation then use String[]. That way you don't have to use a TypeConverter.

Suleyman
  • 2,765
  • 2
  • 18
  • 31
  • LieForBananas, "tag IN (:values)" means tag column values will be searched for values input. tag has to be single string and input has to be either String[] or arrayList. But what I am asking is opposite. In my case tags is ArrayList. Its completely opposite from your case. – Sharad May 10 '18 at 09:40
  • @Sharad I suggested using an array because it works without a `TypeConverter`, the only thing that I didn't notice is that you use the same `value` for LIKE and for IN. The problem is that LIKE can't take in `ArrayList` or array as a parameter and IN can't take a `String`. And I don't understand why you are using LIKE if you don't have any patterns ('XXXX%') you just have a single word. You are better off replacing LIKE with IN as well and using `ArrayList` or array as usual. – Suleyman May 10 '18 at 10:37
  • LieForBananas, I edited the code. As said don't go by code I am looking for approach to search Collections. One product may have several tags and I want to search product by tag. If user is providing one tag say "sports" then all items should be listed in search result. Input i want only one string not string[]. – Sharad May 10 '18 at 11:18
  • @Sharad In that case IN is probably not what you are looking for, because as I now understood you want search through the `ArrayList` stored in your column (you should try to clarify your question so it attracts more answers). I'm not sure how to do this, but you probably have to look at how Room flattens and stores your 'ArrayList` and query accordingly. – Suleyman May 10 '18 at 11:48