Google App Engine: Is it possible to do a Gql LIKE query?

Question

Simple one really. In SQL, if I want to search a text field for a couple of characters, I can do:

SELECT blah FROM blah WHERE blah LIKE '%text%'

The documentation for App Engine makes no mention of how to achieve this, but surely it's a common enough problem?

Solution 1

BigTable, which is the database back end for App Engine, will scale to millions of records. Due to this, App Engine will not allow you to do any query that will result in a table scan, as performance would be dreadful for a well populated table.

In other words, every query must use an index. This is why you can only do =, > and < queries. (In fact you can also do != but the API does this using a a combination of > and < queries.) This is also why the development environment monitors all the queries you do and automatically adds any missing indexes to your index.yaml file.

There is no way to index for a LIKE query so it's simply not available.

Have a watch of this Google IO session for a much better and more detailed explanation of this.

Solution 2

Altough App Engine does not support LIKE queries, have a look at the properties ListProperty and StringListProperty. When an equality test is done on these properties, the test will actually be applied on all list members, e.g., list_property = value tests if the value appears anywhere in the list.

Sometimes this feature might be used as a workaround to the lack of LIKE queries. For instance, it makes it possible to do simple text search, as described on this post.

Solution 3

You need to use search service to perform full text search queries similar to SQL LIKE.

Gaelyk provides domain specific language to perform more user friendly search queries. For example following snippet will find first ten books sorted from the latest ones with title containing fern and the genre exactly matching thriller:

def documents = search.search {
    select all from books
    sort desc by published, SearchApiLimits.MINIMUM_DATE_VALUE
    where title =~ 'fern'
    and genre =  'thriller'
    limit 10
}

Like is written as Groovy's match operator =~. It supports functions such as distance(geopoint(lat, lon), location) as well.

Solution 4

App engine launched a general-purpose full text search service in version 1.7.0 that supports the datastore.

Details in the announcement.

More information on how to use this: https://cloud.google.com/appengine/training/fts_intro/lesson2

Solution 5

Have a look at Objectify here , it is like a Datastore access API. There is a FAQ with this question specifically, here is the answer

How do I do a like query (LIKE "foo%")
You can do something like a startWith, or endWith if you reverse the order when stored and searched. You do a range query with the starting value you want, and a value just above the one you want.

String start = "foo";
    ... = ofy.query(MyEntity.class).filter("field >=", start).filter("field <", start + "\uFFFD");

View more solutions

Comments

[user1930106]

this works for prefix, but what if I want to match from the end of the string? For e.g - i want to search abc in sdfdsabc, then it should return sdfdsabc

[mwm]

the post doesn't exist anymore

[Hardik Patel]

it will search "starts with" not "Contains".