How do I query in GQL using the entity key

Question

How do I write a query against the entity key using GQL in the Google App Engine Data Viewer ?

In the viewer, the first column (Id/Name) displays as name=_1, in the detail view it shows the key as

Decoded entity key: Programme: name=_1
Entity key: agtzcG9................... 

This query does not work:

SELECT * FROM Programme where name = '_1'

Solution 1

You can use the entity's key to retrieve it:

SELECT * FROM Programme where __key__ = KEY('agtzcG9...................')

And, you should be able to query using the name similarly:

SELECT * FROM Programme where __key__ = KEY(Programme, '_1')

Note that this is not something that you would want to do in your AppEngine application; as Nick notes in his comment, it is a huge waste of time. Really, this example is only good to show you how to query by Key in the Admin console.

Solution 2

For numeric IDs, a form similar to the query-by-name works:

SELECT * from Programme where __key__ = KEY('Programme', 1234567)

I found this form especially useful in the Admin Console.

Solution 3

You don't need to query to get an entity by key at all - you can simply fetch the entity by its key. In Python, you can do this with MyModel.get_by_key_name('_1'). This is 3 to 5 times faster than Adam's suggestion of using a query.

Solution 4

When querying by key, you need to match the key exactly, including the parent and not just the ID or name. Of course, if the parent is null, as in the example above, the ID or Name and the type of entity is enough.

If you have the already encoded entity key, you can just use that like:

SELECT * FROM Programme where __key__ = KEY('agtzcG9...................')

For the simple example above,

SELECT * FROM Programme where __key__ = KEY('Programme', '_1')

will do, but if your key has a parent, like

Paren: id=123

Then the query would be

SELECT * FROM Programme where __key__ = KEY('Paren', 123, 'Programme', '_1')

If the parent itself has a parent, you need to add that too. For more details see the official GQL documentation.

There does not appear to be a way to select everything with the same ID or name regardless of parent.

Solution 5

Just a quick note on this: When I use any quotes around any of the args in KEY, the call fails (in the admin console I get the error popup).

For example, for type "mytype" with ID/Name 12345, this does NOT work:

SELECT * FROM mytype WHERE __key__ = KEY('mytype', '12345')

But this does:

SELECT * FROM mytype WHERE __key__ = KEY(mytype, 12345)

View more solutions

Comments

[Nick Johnson]

Argh, no. This is a huge waste of time and resources.

[Adam Crossland]

It wasn't my suggestion, Nick, I was just trying to help him make his query work. I think that he is trying to view things in the admin console data viewer.

[hansvb]

@Nick+Adam: yes, I was trying to review some data in the admin console.

[hansvb]

@Nick: but in the Admin Console, there is probably no better way.

[tensaix2j]

is it possible to query by Id instead of Key?

[ryan]

for the record, __key__ queries actually generally read from the entities table itself, directly, instead of indices first and then the entities table. so in practice, when the datastore compiles this query down to a raw bigtable lookup or scan, there isn't much difference between a __key__ == query and a get(). of course, this is an implementation detail. it's still good practice to use get() whenever you're looking up a single entity.

[Ajax]

When you only need to know if something exists or not, performing a count operation of a query-by-key is the absolute fastest way to find out if an entity exists. If you need to do a key get, you can do a count on a key query, and if > 0, you already know the key. If you need the entity itself, just do as nick advises, and do a get() on the entity.

[Ajax]

ryan, the __key__ queries never, ever read from the entities table. They read from index tables, which includes all tables but the entities table; the index tables all point to a key, so whatever index you use to search, that's where your key comes from. In 1.6.5 there are projection queries to actually read the key and the matched index data for the same price as a keys query... ...Namely, that you never have to put contention on the entities table.

[Nick Johnson]

@Ajax Crossed wires. ryan is talking about queries of the form "SELECT * FROM Kind WHERE key = :1"; you're talking about queries of the form "SELECT key FROM Kind ...". You're both right about your respective types of queries.

[Ezward]

Remember, you only need to quote the id is it is a string. If it is a number, you should not quote it. So if the id was numeric with a value of 888, then the above query would be, SELECT * FROM Programme where key = KEY('Programme', 888)

[ZiglioUK]

count is actually an expensive operation, to see if an entity exists, I just query for it e get the first result