CODEWITHSUNDEEP
javagoogle-app-enginegoogle-cloud-datastoreobjectify
Chetan Dekate
Chetan Dekate

Reputation: 67

Objectify / datastore query with inequality filters on multiple datastore properties

I am trying to use google app engine datastore in which i need to get the total salary of an employee for 2 date range. I need to provide the range of hour i.e. startDate and endDate so how can i do this on datastore. I am using objectify in app engine datastore.

Upvotes: 4

Views: 2216

Answers (1)

konqi
konqi

Reputation: 5227

Have a look at the objectify wiki. In the section Executing Queries there are samples on how to create basic queries.

Lets assume your entity looks something like this

@Entity
public class Salary {
  Ref<Employee> employee;
  @Id 
  Long salaryId;
  @Index
  Date startDate;
  @Index
  Date endDate;
  Long salaryCents;
}

If you want to create a query that takes a minimum data and a maximum date value into account you can chain filters like so:

ofy().load(Salary.class).filter("startDate >", start).filter("startDate <=", end).list();

As you can read in the datastore docs under section *Inequality filters are limited to at most one property' you cannot filter by two different properties with inequality filters, thus making a query like

ofy().load(Salary.class).filter("startDate >", start).filter("endDate <=", end).list();

impossible.

What you can do though is to filter by one property and filter the the other property in-memory in your Java code.

An alternative approach (that should be possible although i haven't tried it) would be to get a list of keys for your first filter like so:

Iterable<Key<Salary>> keys = ofy().load(Salary.class).filter("startDate >", start).keys();

and then use the keys in a second query like so:

ofy().load(Salary.class).filter("salaryId IN", keys).filter("endDate <=", end).list();

Note that an IN filter will perform multiple queries, so the in-memory approach could be faster, depending on your data.

Ideally you can reduce your query to just one property that requires an inequality filter.

Upvotes: 7

Related Questions