CODEWITHSUNDEEP
javagoogle-app-enginejpagoogle-cloud-datastore
Auberon
Auberon

Reputation: 715

Google App Engine/JPA Can't sort and apply inequality filter with two different util.dates

In my application I have following method. The relevant part is the Query.

public List<ShiftlistDTO> getShiftlists() {
    //Get from Datastore.
    EntityManager em = EMF.get().createEntityManager();

    List<?> untypedShiftlists = em.createQuery("SELECT sl FROM Shiftlist sl WHERE sl.end > :currentDate ORDER BY sl.start")
            .setParameter("currentDate", new Date()).getResultList(); 
    em.close();

    //Cast to Shiftlist and get DTOs.
    List<ShiftlistDTO> shiftlistDTOs = new ArrayList<>();       
    for (Object shiftlist : untypedShiftlists)
        shiftlistDTOs.add(Shiftlist.class.cast(shiftlist).getData()); // ClassCastException? Shouldn't occur.
    return shiftlistDTOs;
}

When executing this Query, following exception is thrown.

java.lang.IllegalArgumentException: The first sort property must be the same as the property to which the inequality filter is applied. In your query the first sort property is start but the inequality filter is on end

The Query works fine when the dates used are either both sl.start or sl.end.

Why isn't it possible to filter and sort on two distinct dates and how should I resolve this?

I'm trying to achieve the following. Consider the Shiftlist table.

enter image description here

A Shiftlist starts at some time and ends some time later (the meaning of name/dates/shiftTypes is irrelevant for this question).

I want to query all Shiftlists which haven't ended yet (i.e. sl.end > new Date()) and have them sorted by their start time.

Upvotes: 1

Views: 79

Answers (1)

Dan McGrath
Dan McGrath

Reputation: 42008

Cloud Datastore only allows you to execute queries that we can prove scale by the size of the result set and not by the size of overall data set. This ensures that calls to the database will still return in a timely fashion as your app scales, rather than suddenly growing to 30 second queries that are not much different from being unavailable.

When you have order and inequality on a single property, Cloud Datastore can laser into the appropriate location in your indexes and just return you the sequence of Entities indicated in the index already in an appropriately sorted order.

If we allowed you to order and have inequality on different properties, we lose that ability to laser in and just read from an index. Instead Cloud Datastore would need to do some very unscalable things, such as either holding all results in memory and sorting before returning, or scanning across your entire data set. Neither of these approaches are really tenable at scale in a timely fashion, and hence are not allowed.

PS: I can't really answer the 'how' without understanding your use case and what you are trying to achieve.

Upvotes: 2

Related Questions