Sort and "subsort" in datastore?

[Keith Chima]

My datastore has stored entities with a timestamp and relevance. I want to get the most relevant results from today, this week, month, or all time. The first sort would be timestamp, followed by relevance, pulling 10 at a time. However, this:

Filter timeMaxFilter = new FilterPredicate("timestamp",

                FilterOperator.GREATER_THAN_OR_EQUAL,

                getEarlierTimeStamp(Calendar.HOUR, -24)); 

if(timeFrame.equalsIgnoreCase("day")){

timeMaxFilter = new FilterPredicate("timestamp",

                     FilterOperator.GREATER_THAN_OR_EQUAL,

                     getEarlierTimeStamp(Calendar.HOUR, -24));

q.setFilter(timeMaxFilter);

}

q.addSort("relevance");

PreparedQuery pq = datastore.prepare(q);

for (Entity result : pq.asList(withLimit(10).offset(10*pageNumber))) {

}

Generates this error message:

[INFO] java.lang.IllegalArgumentException: The first sort property must be the s

ame as the property to which the inequality filter is applied.  In your query th

e first sort property is relevance but the inequality filter is on relevance.

[INFO]  at com.google.appengine.api.datastore.DatastoreApiHelper.translateError(

DatastoreApiHelper.java:53)

I understand why- they reindex in order of timestamp, because they want to be able to return the query in flat time, so they require me to sort by timestamp first. However, I feel like they can't be putting such a strict requirement on their customers. I could probably pull back ALL results in order of relevance, and then pull off the top 10. However, that seems wasteful. Is there a better way? If not, how would I set a FilterOrder with no limit, meaning ALL of the entries so I could sort them on my end?

Thanks,

-Keith

[Patrice (Cloud Platform Support)]

Hi Keith,

For the datastore to be scalable, indexing has to be done this way, and datastore cannot "think", only scan an index. This is the only way to keep datastore scalable when there's a really big amount of data in it.

You can get more information to the limits of querying and indexes in this article. Also, when I was learning datastore, I found that this video was of great help to understand the reasoning behind the limitations, so it might be worth watching it :).

Now, with that in mind, there's no way to do what you want straightforward. With the limitations on inequality filters, you can't filter for both date and relevance at once. You could get around this by having each entity have boolean properties for "this month", "this week", "this day", etc. With that, you could do a query for all "this week" = true, sort by relevance. A cron job would be needed to update the entities each day though, and depending on your use case and what's in your datastore, could end up being costly.

Your method of grabbing everything, then sorting yourself, is another idea that would work. Even if it will require a bit longer time to process your data

Personally, I'd use our automated system to backup your data to BigQuery daily, and then you could query the way you want easily.

Cheers!