multiple calls to order() in a query

[Eliot Stock]

Hi all,

I have a query to which I've just added a second order() call and the requisite indexes. It now looks like this:

Query<Post> postQuery = ofy.query(postType).filter("state", Post.State.LIVE)

.order("-score").order("-timestamp");

if (geoCells != null && geoCells.length > 0) {

postQuery.filter("geoCells in", geoCells);

}

if (category != null && !"".equals(category) && !"all".equals(category)) {

postQuery.filter("categories", category);

}

A toString() on the query before it's run looks like this:

kind=Post,ancestor=,

filter=^iEQUALOfferedPost,

filter=geoCellsIN[b5d75b, b5d75e, b5d75f, b5d771, b5d774, b5d775],

filter=stateEQUALLIVE,

sort=scoreDESCENDING,

sort=timestampDESCENDING

and I have the following indexes in datastore-indexes.xml:

    <!-- / & /wanted, AJAX calls, category = all -->

    <datastore-index kind="Post" ancestor="false" source="manual">

        <property name="^i" direction="asc"/>

        <property name="geoCells" direction="asc"/>

        <property name="state" direction="asc"/>

        <property name="score" direction="desc"/>

        <property name="timestamp" direction="desc"/>

    </datastore-index>

    

    <!-- / & /wanted, AJAX calls -->

    <datastore-index kind="Post" ancestor="false" source="manual">

        <property name="^i" direction="asc"/>

        <property name="categories" direction="asc"/>

        <property name="geoCells" direction="asc"/>

        <property name="state" direction="asc"/>

        <property name="score" direction="desc"/>

        <property name="timestamp" direction="desc"/>

    </datastore-index>

The new property on the Post class is "score". I've been through and added a score value of 0 to all existing Post entities in the datastore, and did this *after* the indexes were there. The query runs without an "index needed" exception, but there are no results. Any ideas why? I can definitely sort by two properties in one query, right?

Cheers,

Eliot.

[Jeff Schnitzer]

Are there single-property indexes on all of those fields?  One annoying thing about GAE is that you need single-property indexes covering every single one of the fields in a multi-property index.

This makes writes for objects like this very expensive.

Jeff

[Eliot Stock]

Yep, every one of those properties has @Indexed on it. Not too concerned about the write cost - writes happen once for every hundred thousand or so reads.

On Thursday, 1 March 2012 14:51:12 UTC, Jeff Schnitzer wrote:

Are there single-property indexes on all of those fields?  One annoying thing about GAE is that you need single-property indexes covering every single one of the fields in a multi-property index.

This makes writes for objects like this very expensive.

Jeff

[Jeff Schnitzer]

Try running the equivalent query in GSQL from the console datastore viewer.  Closely examine one entity you "know" should come back from the query.

Usually the problem is that the stored data isn't what you think it is.  You can certainly order by multiple columns.

Jeff

[Jeff Schnitzer]

Also:  Watch out for type mismatches.  A property value of "0" is not the same as 0.

Jeff

[Eliot Stock]

Thanks, but I can't see how to get a valid GQL query for a class that has a subclass. The "^" symbol in the identifier upsets the query tool. This query:

SELECT * FROM Post   

WHERE "^i" = 'OfferedPost'  

AND geoCells IN ('b5d75b', 'b5d75e', 'b5d75f', 'b5d771', 'b5d774', 'b5d775')   

AND state = 'LIVE'   

ORDER BY score DESC, timestamp DESC

gives the error:

Invalid GQL query string. 

[Jeff Schnitzer]

I just ran this query against my datastore just fine:

SELECT * FROM Event where "^i" = 'CyclingEvent'

Your query looks fine to my eyes so I don't know what the problem is.  There have certainly been bugs in the query tool before.  If you are certain that's the problem, open an issue at the official GAE issue tracker:

http://code.google.com/p/googleappengine/issues/list

Jeff

[Eliot Stock]

Thanks. Have opened this issue:

http://code.google.com/p/googleappengine/issues/detail?id=7078