Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 491
Chetan Dekate Avatar asked Mar 15 '23 10:03

Chetan Dekate


1 Answers

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.

like image 113
konqi Avatar answered Apr 08 '23 04:04

konqi