Datastore GQL LIKE condition

[hads]

Hi all,

I'm aware that the datastore isn't a relational database and as such
doesn't support many relational database features.

I'm wanting to create a street address lookup with a model something
like this;

class Address(db.Model):
street = db.StringProperty()
suburb = db.StringProperty()
postcode = db.StringProperty()
city = db.StringProperty()
lon = db.StringProperty()
lat = db.StringProperty()

The thing I'm trying to get my head around is how to query for results
with a partial address i.e. "For" returning ("Forest Street", "Forman
Avenue", ...).

Normally one could do this with a LIKE condition (SELECT * FROM
address WHERE street LIKE 'For%'). I'm not sure how I would go about
this using the datastore since it only supports the common comparison
operators.

Does anyone have any insights on how to achieve this end result in
some way using the datastore?

Many thanks,

hads

[Hubert Chen]

You'd have to select all rows and then do a regex match in your code.

[Brett Morgan]

I'd probably head down the path of maintaining a trie for all the
words in the fields you want partial search on. This would give you
the ability to do ajax drop down completion on the fields after two or
three characters.

http://en.wikipedia.org/wiki/Trie

[hads]

On Apr 13, 8:24 pm, "Brett Morgan" <brett.mor...@gmail.com> wrote:
> I'd probably head down the path of maintaining a trie for all the
> words in the fields you want partial search on. This would give you
> the ability to do ajax drop down completion on the fields after two or
> three characters.
>
> http://en.wikipedia.org/wiki/Trie

Thanks Brett, that's one idea I had thought of and I guess the only
real solution.

Hubert, unfortunately that wouldn't work in this situation. For one
the data set is far too large (every street name in the country) to
process like that and secondly it would be over the limits of the
datastore api.

Cheers,

hads

[Brett Morgan]

What i'd think would be a first approximation, given your dataset
size, would be to start the trie at something like three characters,
and have a seperate datastore entity for each of those start nodes.

I suspect you are going to have to precompute the trie on your desktop
and bulk upload it tho.

[hads]

On Apr 13, 8:50 pm, "Brett Morgan" <brett.mor...@gmail.com> wrote:
> What i'd think would be a first approximation, given your dataset
> size, would be to start the trie at something like three characters,
> and have a seperate datastore entity for each of those start nodes.

That's my thought too.

> I suspect you are going to have to precompute the trie on your desktop
> and bulk upload it tho.

Yeah. I'm currently playing with the comparison operators after
finding a tip on the following page (I thought I'd read all the docs -
must have missed that bit);

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

This may be an easier route.

Cheers,

hads

[Brett Morgan]

I don't see how comparison operators help with partial word matching.
Please tell us how it works out. =)

[hads]

On Apr 13, 9:03 pm, "Brett Morgan" <brett.mor...@gmail.com> wrote:
> I don't see how comparison operators help with partial word matching.
> Please tell us how it works out. =)

Confused me for a minute too :)

The tip on that page is at the bottom of the "Introducing Indexes"
section.

I'm currently using something like this;

q = 'For'
addresses = db.GqlQuery('SELECT * FROM Address WHERE street >= :1 AND
street < :2', q, q + 'z')

which works fine. Now all I need to do is store a lowercase name with
every entity.

Thanks for the discussion.

hads

[Brett Morgan]

*lightbulb*

> Thanks for the discussion.

Thank you, takes two to tango and all that. *grin*