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'

score 109 · Accepted Answer · edited Oct 06 '17 at 05:36

109

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.

edited Oct 06 '17 at 05:36

tristansokol

4,054
2
17
32

answered Jan 04 '10 at 14:13

Adam Crossland

14,198
3
44
54

4

Argh, no. This is a _huge_ waste of time and resources. – Nick Johnson Jan 06 '10 at 21:37
4

@Nick: but in the Admin Console, there is probably no better way. – Thilo Jan 07 '10 at 00:01
4

is it possible to query by Id instead of Key? – tensaix2j Jun 24 '10 at 13:47
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 May 03 '12 at 15:09
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) – Ezward Aug 02 '13 at 21:40
count is actually an expensive operation, to see if an entity exists, I just query for it e get the first result – ZiglioUK Mar 02 '14 at 21:56
why is this a huge waste of time and resource, isn't the main key column indexed by default? – the pillow Feb 09 '23 at 22:59

score 20 · Answer 2 · answered Jun 02 '11 at 04:38

20

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.

answered Jun 02 '11 at 04:38

Jeffrey Miller

201
2
3

score 18 · Answer 3 · edited Aug 26 '15 at 05:18

18

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.

edited Aug 26 '15 at 05:18

alex

479,566
201
878
984

answered Jan 06 '10 at 21:38

Nick Johnson

100,655
16
128
198

5

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. – Adam Crossland Jan 06 '10 at 23:34
@Nick+Adam: yes, I was trying to review some data in the admin console. – Thilo Jan 07 '10 at 00:00
1

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. – ryan Jan 25 '11 at 23:30
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. – Ajax May 03 '12 at 15:14
2

@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. – Nick Johnson May 03 '12 at 23:43

score 3 · Answer 4 · answered Jul 02 '14 at 23:01

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.

score 1 · Answer 5 · edited May 16 '15 at 00:58

1

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)

edited May 16 '15 at 00:58

Nathan Tuggy

2,237
27
30
38

answered May 15 '15 at 21:55

Tim Consolazio

4,802
2
19
28

How do I query in GQL using the entity key

5 Answers5

Linked