App Engine datastore does not support operator OR

Question

I am trying to query the google datastore for something like (with pm --> persistanceManager):

String filters = "(  field == 'value' ||  field == 'anotherValue' )";
Query query = pm.newQuery(myType.class, filters);

When I execute - I am getting back: App Engine datastore does not support operator OR.

What's the best approach in people experience for this kind of queries?

Any help appreciated!

Answer 1

According to Google App Engine - Queries and Indexes:

Query Filters

A filter specifies a field name, an operator, and a value. The value must be provided by the app; it cannot refer to another property, or be calculated in terms of other properties. The operator can be any of the following: < <= == >= >

Note: The Java datastore interface does not support the != and IN filter operators that are implemented in the Python datastore interface. (In the Python interface, these operators are implemented in the client-side libraries as multiple datastore queries; they are not features of the datastore itself.)

The subject of a filter can be any object field, including the primary key and the entity group parent (see Transactions).

An entity must match all filters to be a result. In the JDOQL string syntax, multiple filters are specified separated by && (logical "and"). Other logical combinations of filters (logical "or", "not") are not supported.

Due to the way the App Engine datastore executes queries, a single query cannot use inequality filters (< <= >= >) on more than one property. Multiple inequality filters on the same property (such as querying for a range of values) are permitted. See Restrictions on Queries.

Basically you're either going to have to restructure your data so that you can find what you're looking for with one condition or multiple "and" conditions or you're going to have to retrieve the data via two (or more) queries and filter/combine it in your code.

Answer 2

Perform multiple queries. The Datastore, like all other databases, isn't able to efficiently execute disjunctions. Unlike other databases, it exposes this difficulty to the user, to make it clear that what you're doing isn't efficient. Your only solution is to execute multiple queries - one for each or - and combine them.

Answer 3

One way to simplify having to "do it yourself" might be to use parameterized queries:

   Query query = pm.newQuery(mytype.class);
   query.setFilter("field == autoParam");
   query.declareParameters("String autoParam");

   List<String> params = myListOfThingsFieldCanBeEqualTo;

   Set merged = new HashSet();
   for (String f : params) {
     merged.addAll(q.execute(f));
   }

Answer 4

Sorry I'm late to the game.. I just ran across your question today.

Another way to "simulate" 'IN' and 'OR' behavior is to use the "low level" Datastore API. The DatastoreService supports a get() method that accepts a collection of Keys and returns a Map of all Entities that matched the passed in Keys. It's an interface, but there's a handy DatastoreServiceFactory available that will dispense a ready-to-use instance.

Unfortunately, Google decided that they don't want to promote this low-level API approach and prefer that developers use JDO or JPA, so there's no documentation available other than the JavaDocs and whatever code samples that you might find when you Google "DatastoreService".

TL

Answer 5

Late breaking News.. at least I'm just getting it. As I was downloading the latest Java SDK for GAE I noticed on the Release Notes that "Issue 29: Expose batch gets" was fixed in the latest release (v1.2.1). Basically it seems that we (I'm looking for the same support it seems) may have a JDO based alternative rather than having to drop down to the "low-level" Datastore API. I've just downloaded the latest Java GAE SDK so I haven't had an opportunity to test anything yet, but I wanted to give you a heads-up ASAP. I'll post anything more I learn after I've had a chance to confirm this "fix".

Please accept my apologies if I've broken StackOverflow etiquette by re-posting my comment as an answer, but I decided to do it for two reasons. Firstly because, even though it's me addressing the same issue again, IMHO this new information appears to provide a completely different "answer" to the problem. And secondly, I was concerned that the comment form might not get your attention before you'd spent a great deal of time looking into the first answer that I provided.

Next time I'll think more carefully before acting.

TL

Answer 6

I don't know if GAE's JDO and JPA implementations support this, but using the low-level API, you can use the operator IN for this, in one query.

Query query = new Query("Issue");
List<String> list = Arrays.asList("NEW", "OPEN", "ACCEPTED");
query.addFilter("status", FilterOperator.IN, list);

DatastoreService datastore = DatastoreServiceFactory.getDatastoreService();
PreparedQuery preparedQuery = datastore.prepare(query);
for (Entity entity : preparedQuery.asIterable()) {
    // should iterate over 'NEW', 'OPEN' and 'ACCEPTED' issues
}

Answer 7

Contrary to cletus' answer, OR-ing works, in more recent version of App Engine anyway.

Indeed, I found OR-ing not working in App Engine 1.3.0 that I had, but according to Google App Engine - Queries and Indexes (the same source cletus referred to in his answer),

An entity must match all filters to be a result. In the JDOQL string syntax, you can separate multiple filters with || (logical "or") and && (logical "and"), although keep in mind that || can only be employed when the filters it separates all have the same field name. In other words, || is only legal in situations where the filters it separates can be combined into a single contains() filters.

I figured since his answer (and since I last updated my App Engine), App Engine must have been upgraded on this matter.

Update App Engine to 1.3.4, and the OR-ing works! Though with the limitation.

Thanks to cletus anyway:)