Why doesn't the following query work with double LIKE clause?

Question

I'm using GAE's datastore and JPA persistence framework to store my entities. Though when attempting to retreive some specific entities I run into the problem mentioned below.

The following exception is thrown when invoking the getResultList() method on my TypedQuery: javax.persistence.PersistenceException: Illegal argument

    EntityManager em = Persistence.createEntityManagerFactory("test-persistence")
                           .createEntityManager();
    String q = "SELECT c FROM c TestBord c WHERE c.publiclyAvailible=true 
               AND c.avarageRating='5' 
               AND c.user LIKE 'user%' 
               AND c.nameBord LIKE 'bord%'";
    TypedQuery<TestBord> tq = em.createQuery(q, TestBord.class);
    List<TestBord> l = tq.getResultList();

As also shown above, here is the query I'm using:

SELECT c FROM c KvCBord c WHERE c.publiclyAvailible=true 
AND c.avarageRating='5' 
AND c.user LIKE 'user%' 
AND c.nameBord LIKE 'bord%'

It seems to break when I use two LIKE clauses, anybody have any ideas on how to work around this problem, or knows how to properly rewrite the query?

NOTE: Works fine with just one LIKE clause though.

Related question: http://stackoverflow.com/questions/47786/google-app-engine-is-it-possible-to-do-a-gql-like-query — victorantunes, Jan 30 '14 at 17:39

score 4 · Accepted Answer · answered Jan 30 '14 at 17:32

4

AppEngine translates your GQL query into a low level Datastore API query. According to the Restrictions on queries Java docs, "Inequality filters are limited to at most one property". This is usually because of index selection. The LIKE operator becomes an inequality filter and cannot apply to both .user and .nameBord properties in the same query.

answered Jan 30 '14 at 17:32

Martin Berends

3,450
2
14
19

Correct me if I'm wrong, but `LIKE` does not fit into this. Here's a quote from the link you posted: `a single query may not use inequality comparisons (LESS_THAN, LESS_THAN_OR_EQUAL, GREATER_THAN, GREATER_THAN_OR_EQUAL, NOT_EQUAL) on more than one property across all of its filters.` – victorantunes Jan 30 '14 at 17:36
2

@victorantunes: The partial LIKE 'prefix%' query is under the hood implemented as a query with inequality filter – Peter Knego Jan 30 '14 at 17:51

score 0 · Answer 2 · answered Jan 30 '14 at 18:00

It doesn't work because App Engine has restrictions in its queries. Queries results come from indexes, and you can't have indexes that support 2 or more inequality filters.

I recommend using Search API for that searches. It's easy, practical, fast, and you can do more complex searches:

https://developers.google.com/appengine/docs/java/search/

Why doesn't the following query work with double LIKE clause?

2 Answers2