Skip to content

Instantly share code, notes, and snippets.

@iperdomo

iperdomo/ds-in.org

Last active June 16, 2020 06:06
Show Gist options
  • Save iperdomo/a988097ea20d23da25d33b21dec72433 to your computer and use it in GitHub Desktop.
Save iperdomo/a988097ea20d23da25d33b21dec72433 to your computer and use it in GitHub Desktop.
Download ZIP
Problems with multiple IN clauses in GAE datastore queries
Raw
ds-in.org

Problems with multiple IN queries

The IN operator also performs multiple queries: one for each item in the specified list, with all other filters unchanged and the IN filter replaced with an EQUAL filter. The results are merged in order of the items in the list. If a query has more than one IN filter, it is performed as multiple queries, one for each possible combination of values in the IN lists.

https://cloud.google.com/appengine/docs/standard/java/datastore/queries

In our case means that if we want to support filtering by questionID and surveyInstanceId, we’ll read QuestionAnswerStore multiple times to calculate the result.

package org.akvo.gae.remoteapi;

import com.google.appengine.api.datastore.DatastoreService;
import com.google.appengine.api.datastore.DatastoreServiceFactory;
import com.google.appengine.api.datastore.Entity;
import com.google.appengine.api.datastore.FetchOptions;
import com.google.appengine.api.datastore.PreparedQuery;
import com.google.appengine.api.datastore.Query;
import com.google.appengine.tools.remoteapi.RemoteApiInstaller;
import com.google.appengine.tools.remoteapi.RemoteApiOptions;

import java.util.Arrays;
import java.util.Collections;
import java.util.List;

public class TestQueryTwoInClauses {
    public static void main(String[] args) {
        final RemoteApiOptions options = new RemoteApiOptions().server("akvoflow-uat**.appspot.com", 443);
        options.useServiceAccountCredential("sa-akvoflow-***@***", "/path/to/file.p12");
        final RemoteApiInstaller installer = new RemoteApiInstaller();

        try {
            installer.install(options);
            DatastoreService ds = DatastoreServiceFactory.getDatastoreService();

            List<Long> formInstanceIds = Arrays.asList(587119117L, 579079115L);
            List<String> questionIds = Arrays.asList("609479145", "601899166");

            Query q = new Query("QuestionAnswerStore");
            Query.Filter f1 = new Query.FilterPredicate("surveyInstanceId", Query.FilterOperator.IN, formInstanceIds);
            Query.Filter f2 = new Query.FilterPredicate("questionID", Query.FilterOperator.IN, questionIds);
            Query.CompositeFilter comp = Query.CompositeFilterOperator.and(f1, f2);

            q.setFilter(comp);

            PreparedQuery pq = ds.prepare(q);

            List<Entity> result = pq.asList(FetchOptions.Builder.withDefaults());
            System.out.println(result.size());
            System.out.println(result);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            installer.uninstall();
        }
    }
}
10
[<Entity [QuestionAnswerStore(598009120)]:
	lastUpdateDateTime = Thu Jun 11 17:20:33 CEST 2020
	valueText = null
	surveyId = 601879159
	createUserId = 0
	questionID = 609479145
	strength = null
	lastUpdateUserId = 0
	scoredValue = null
	createdDateTime = Thu Jun 11 17:20:33 CEST 2020
	type = OPTION
	collectionDate = Thu Jun 11 17:20:40 CEST 2020
	surveyInstanceId = 587119117
	ancestorIds = null
	iteration = 2
	value = [{"text":"yes","code":"1"}]
	arbitratyNumber = null
>
, <Entity [QuestionAnswerStore(599689125)]:
	lastUpdateDateTime = Thu Jun 11 17:20:33 CEST 2020
	valueText = null
	surveyId = 601879159
	createUserId = 0
	questionID = 609479145
	strength = null
	lastUpdateUserId = 0
	scoredValue = null
	createdDateTime = Thu Jun 11 17:20:33 CEST 2020
	type = OPTION
	collectionDate = Thu Jun 11 17:20:40 CEST 2020
	surveyInstanceId = 587119117
	ancestorIds = null
	iteration = 0
	value = [{"text":"yes","code":"1"}]
	arbitratyNumber = null
>
, <Entity [QuestionAnswerStore(599689126)]:
	lastUpdateDateTime = Thu Jun 11 17:20:33 CEST 2020
	valueText = null
	surveyId = 601879159
	createUserId = 0
	questionID = 609479145
	strength = null
	lastUpdateUserId = 0
	scoredValue = null
	createdDateTime = Thu Jun 11 17:20:33 CEST 2020
	type = OPTION
	collectionDate = Thu Jun 11 17:20:40 CEST 2020
	surveyInstanceId = 587119117
	ancestorIds = null
	iteration = 1
	value = [{"text":"no","code":"2"}]
	arbitratyNumber = null
>
, <Entity [QuestionAnswerStore(599689122)]:
	lastUpdateDateTime = Thu Jun 11 17:20:33 CEST 2020
	valueText = null
	surveyId = 601879159
	createUserId = 0
	questionID = 601899166
	strength = null
	lastUpdateUserId = 0
	scoredValue = null
	createdDateTime = Thu Jun 11 17:20:33 CEST 2020
	type = DATE
	collectionDate = Thu Jun 11 17:20:40 CEST 2020
	surveyInstanceId = 587119117
	ancestorIds = null
	iteration = 0
	value = 1591197342535
	arbitratyNumber = null
>
, <Entity [QuestionAnswerStore(599689123)]:
	lastUpdateDateTime = Thu Jun 11 17:20:33 CEST 2020
	valueText = null
	surveyId = 601879159
	createUserId = 0
	questionID = 601899166
	strength = null
	lastUpdateUserId = 0
	scoredValue = null
	createdDateTime = Thu Jun 11 17:20:33 CEST 2020
	type = DATE
	collectionDate = Thu Jun 11 17:20:40 CEST 2020
	surveyInstanceId = 587119117
	ancestorIds = null
	iteration = 1
	value = 1591802266547
	arbitratyNumber = null
>
, <Entity [QuestionAnswerStore(599689124)]:
	lastUpdateDateTime = Thu Jun 11 17:20:33 CEST 2020
	valueText = null
	surveyId = 601879159
	createUserId = 0
	questionID = 601899166
	strength = null
	lastUpdateUserId = 0
	scoredValue = null
	createdDateTime = Thu Jun 11 17:20:33 CEST 2020
	type = DATE
	collectionDate = Thu Jun 11 17:20:40 CEST 2020
	surveyInstanceId = 587119117
	ancestorIds = null
	iteration = 2
	value = 1591715949166
	arbitratyNumber = null
>
, <Entity [QuestionAnswerStore(615349118)]:
	lastUpdateDateTime = Thu Jun 11 17:15:26 CEST 2020
	valueText = null
	surveyId = 601879159
	createUserId = 0
	questionID = 609479145
	strength = null
	lastUpdateUserId = 0
	scoredValue = null
	createdDateTime = Thu Jun 11 17:15:26 CEST 2020
	type = OPTION
	collectionDate = Thu Jun 11 17:15:34 CEST 2020
	surveyInstanceId = 579079115
	ancestorIds = null
	iteration = 1
	value = [{"text":"no","code":"2"}]
	arbitratyNumber = null
>
, <Entity [QuestionAnswerStore(617389125)]:
	lastUpdateDateTime = Thu Jun 11 17:15:26 CEST 2020
	valueText = null
	surveyId = 601879159
	createUserId = 0
	questionID = 609479145
	strength = null
	lastUpdateUserId = 0
	scoredValue = null
	createdDateTime = Thu Jun 11 17:15:26 CEST 2020
	type = OPTION
	collectionDate = Thu Jun 11 17:15:34 CEST 2020
	surveyInstanceId = 579079115
	ancestorIds = null
	iteration = 0
	value = [{"text":"yes","code":"1"}]
	arbitratyNumber = null
>
, <Entity [QuestionAnswerStore(617389123)]:
	lastUpdateDateTime = Thu Jun 11 17:15:26 CEST 2020
	valueText = null
	surveyId = 601879159
	createUserId = 0
	questionID = 601899166
	strength = null
	lastUpdateUserId = 0
	scoredValue = null
	createdDateTime = Thu Jun 11 17:15:26 CEST 2020
	type = DATE
	collectionDate = Thu Jun 11 17:15:34 CEST 2020
	surveyInstanceId = 579079115
	ancestorIds = null
	iteration = 0
	value = 1591888256090
	arbitratyNumber = null
>
, <Entity [QuestionAnswerStore(617389124)]:
	lastUpdateDateTime = Thu Jun 11 17:15:26 CEST 2020
	valueText = null
	surveyId = 601879159
	createUserId = 0
	questionID = 601899166
	strength = null
	lastUpdateUserId = 0
	scoredValue = null
	createdDateTime = Thu Jun 11 17:15:26 CEST 2020
	type = DATE
	collectionDate = Thu Jun 11 17:15:34 CEST 2020
	surveyInstanceId = 579079115
	ancestorIds = null
	iteration = 1
	value = 1591802017118
	arbitratyNumber = null
>
]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment