Datastore query with IN operator

Question

The new flexible environment datastore interface does not seem to support IN operation when running a query. I hope that I'm wrong, and if so, how can one use an IN operator in the new Java interface of Datastore?

Answer 1

A query like - WHERE color IN('RED', 'BLACK'), it is not supported by the Datastore (server side). Same is the case with OR operator (e.g. WHERE color='RED' OR color='BLACK'). Some client APIs have added this functionality by splitting the query into multiple and then merging the results from each query. The new google-cloud-java API does not support this yet. For now, you would have to run multiple queries for each value in the IN clause and merge the results.

Answer 2

I tried using the repository query methods, but I got an error informing that it is not supported.

Only solved for me using the @Query annotation;

Example:

  @Query("select * from UserGroup where name IN @names")
  List<Company> findAllByName(List<String> names);

Answer 3

Here’s an example from the documentation:

If you want to set more than one filter on a query, you must use CompositeFilter, which requires at least two filters.

Filter tooShortFilter = new FilterPredicate("height", FilterOperator.LESS_THAN, minHeight);
Filter tooTallFilter = new FilterPredicate("height", FilterOperator.GREATER_THAN, maxHeight);
Filter heightOutOfRangeFilter = CompositeFilterOperator.or(tooShortFilter, tooTallFilter);
Query q = new Query("Person").setFilter(heightOutOfRangeFilter);

You can also use .and(). The code here is for Java 7. For Java 8 you can find a corresponding code in the documentation referenced above. I hope that helps.

Now to IN. While I have not tried it myself recently, the current documentation states that it can still be used as an operator. According to it, something like the code below should work:

Filter propertyFilter = new FilterPredicate("height", FilterOperator.IN, minHeights);
Query q = new Query("Person").setFilter(propertyFilter);

Alternatively, you could use Google GQL. It will allow you to write SQL-like syntax, in which you can use in(...).