XCode Warrier XCode Warrier - 10 months ago 61
PHP Question

PHP-GDS Sort queries from AppEngine DataStore

I've created an index using a schema using the awesome PHP-GDS:

$obj_product_schema = (new Schema('Product'))
->addString('name', TRUE)
->addInteger('votes', TRUE);

However if I try to return a subset of objects with a sorted query as follows I get a 'no matching index found' error.

$queryStr = "SELECT * FROM Product WHERE date='" . $dateQ . '" ORDER BY votes ASC ";
$products = $obj_event_store->fetchPage(5);

The error:

Fatal error: Uncaught exception 'Google_Service_Exception' with message 'Error calling POST https://www.googleapis.com/datastore/v1beta2/datasets/app/runQuery: (412) no matching index found.'

Answer Source

When you put "true" on your parameter, it did create an index for that single parameter. I think the confusion was between single parameter indexes and composite indexes(I just saw your other question answered by Tom).

While these single parameter indexes indeed works for single parameter queries (SELECT * FROM X WHERE Y = whatever, where it only needs an index for Y), when you add an order by on a different parameter, it needs a composite index (SELECT * FROM X WHERE Y = whatever ORDER BY Z, where it needs Y and Z). You can't declare them in your schema directly, they need to be uploaded with your application (in a file called index.yaml).

Think of it this way : your datastore currently has an index where the only info available to it is the date, and the key of the object. It also has another index where the only info available is the vote and the key of the object. How would it know what to return if you ask for a date with a vote order by? That's why you need to add an index where the info available is the date and the vote, with the key of the object, so your datastore can lookup into that table

As a side note : the system has gotten better than it was before : it can now use the ZigZag merge algorithm to make more complex indexes out of simpler ones (there are still limitations around this though, and obviously, performance is impacted).

More information in a related answer