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

Question

Hi,

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?

Anthony

Answer 1

GQL does not support LIKE.

I believe the concept is to do this filtering in the application code.

This is just one of the many reasons why I don't like app engine.

Answer 2

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 a 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.

Answer 3

@Dave Webb

You can search a table for full words. See SearchableModel.

Answer 4

hello there,

i'm facing the same problem, but i found something on google app engine pages:

Tip: Query filters do not have an explicit way to match just part of a string value, but you can fake a prefix match using inequality filters:

db.GqlQuery("SELECT * FROM MyModel WHERE prop >= :1 AND prop < :2", "abc", u"abc" + u"\ufffd")

This matches every MyModel entity with a string property prop that begins with the characters abc. The unicode string u"\ufffd" represents the largest possible Unicode character. When the property values are sorted in an index, the values that fall in this range are all of the values that begin with the given prefix.

http://code.google.com/appengine/docs/python/datastore/queriesandindexes.html

maybe this could do the trick ;)

Answer 5

Thanks Dave.

But the link is broken.

The right path is: http://code.google.com/p/googleappengine/source/browse/trunk/python/google/appengine/ext/search/__init__.py

Answer 6

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.

Answer 7

If you are using java to develop,you can use JPA with GAE like this.

jpaTemplate.find("SELECT DISTINCT o FROM Order o WHERE o.email LIKE '"+email+"%'");