DISTINCT query on multiple columns doesn't work - Google App engine Datastore

Question

I want to fetch distinct multiple columns from Google app engine Datastore in endpoint class. For that i am using below code but problem is that if i try to fetch only single column then only it works properly and returns DISTINCT data. If i try to fetch multiple columns then DISTINCT doesn't work and it returns all records without any effect of Distinct query. I don't know what is wrong with my code. Please suggest any solution and guide me where i am going wrong. Thank you.

Code:

@SuppressWarnings({ "unchecked" })
    @ApiMethod(name = "getDistinctFollow", httpMethod = HttpMethod.GET, path = "userendpoint/userName_fk3")
    public ObjectListContainer getDistinctFollower(
            @Named("followName") ArrayList<String> lstFollower,
            @Nullable @Named("cursor") String cursorString,
            @Nullable @Named("limit") Integer limit) {

        EntityManager mgr = null;
        Cursor cursor = null;
        List<Object[]> lstNames;
        List<String> lstString;
        ObjectListContainer object;
        try {
            mgr = getEntityManager();
            Query query = mgr.createQuery("select distinct f.uFullName,f.uUrl from UMaster f where f.uName in (:followName)");
            query.setParameter("followName", lstFollower);
            if (cursorString != null && cursorString != "") {
                cursor = Cursor.fromWebSafeString(cursorString);
                query.setHint(JPACursorHelper.CURSOR_HINT, cursor);
            }
            if (limit != null) {
                query.setFirstResult(0);
                query.setMaxResults(limit);
            }
            lstNames = (List<Object[]>) query.getResultList();
            cursor = JPACursorHelper.getCursor(lstNames);
            if (cursor != null)
                cursorString = cursor.toWebSafeString();

            lstString = new ArrayList<String>();

            for (Object obj[] : lstNames) {
                lstString.add(obj != null ? obj[0].toString() : null);
                lstString.add(obj != null ? obj[1].toString() : null);
            }
            object = new ObjectListContainer();
            object.setObjectsList(lstString);

        } finally {
            mgr.close();
        }
        return object;
    }

I'm not sure anything's wrong with your code, but the DISTINCT keyword is still experimental as mentioned in the documentation, so maybe there is an issue with this kind of query. Maybe you can try not projecting on specific columns but retrieving all columns to see if it makes any difference. — brian
I'm not sure if this is the issue, but DISTINCT with multiple parameters will return distinct tuples. So in your query it is supposed to return every distinct pair of (uFullName, uUrl). Can you provide what duplicates are being returned? — Patrick Costello
@PatrickCostello when i use DISTINCT with single column then it returns distinct data but if i use it with multiple columns then it returns duplicates. — Zankhna
@PatrickCostello I am using NoSql in app engine. There is a total 12 records with userName = abc.If i use only Distint uFullName where uName = 'abc' then it returns single record with value and if i use Distinct uFullName,uUrl where uName = 'abc' then it returns all 12 record without any effect of distinct query. — Zankhna

svpino svpino · Accepted Answer · 2014-05-16T12:45:53

Notice that DISTINCT only works with Indexed properties. Is any of uFullName or uName are not indexed, then the projection won't work.

DISTINCT query on multiple columns doesn't work - Google App engine Datastore

1 Answers