Parse: Compound Query on Followers times out

Question

I have an activity table which says which users follows who. (fromUser and toUser) I am constructing a leaderboard to see who has the most rating posted, amongst the followers.

So I created this query:

ParseQuery<ParseObject> queryActivityFollowing = new ParseQuery<>("Activity");
queryActivityFollowing.whereEqualTo("type", "follow");
queryActivityFollowing.whereEqualTo("fromUser", ParseUser.getCurrentUser());
queryActivityFollowing.setLimit(500);

// innerQuery, only get Users posted by the users I follow
ParseQuery<ParseUser> queryUserFollowing = ParseUser.getQuery();
queryUserFollowing.whereMatchesQuery("toUser", queryActivityFollowing);

// querySelf
ParseQuery<ParseUser> querySelf = ParseUser.getQuery();
querySelf.whereEqualTo("objectId", ParseUser.getCurrentUser().getObjectId());

List<ParseQuery<ParseUser>> queries = new ArrayList<>();
queries.add(queryUserFollowing);
queries.add(querySelf);

query = ParseQuery.or(queries);
query.orderByDescending("rating_count");
query.setLimit(20);

But somehow, it times out and never displays a result. Is there something inefficient with my query?

Thanks!

Edit: Data description: Activity is a class with 3 columns, fromUser, toUser, type. fromUser and toUser are pointers to the _User class, type is a string

in _User, I have the classic attributes, and an integer named rating_count, to which is the orderBy criteria (updated code above).

Actually, I think the query doesn't time out, but just returns 0 results. I follow some of my users so it's definitely not the expected output.

Answer 1

It's a tough one, because parse's query supports this sort of thing only minimally. The best idea I can offer is this one:

1. One query on the Activity table whereEqualTo("type", "follow") and whereEqualTo("fromUser", ParseUser.getCurrentUser())
2. no queryUserFollowing, no querySelf. These are unnecessary. This also frees you from Parse.Query.or().
3. setLimit(1000) will explain why below
4. include("toUser")
5. upon completion, loop through the results, maximize for result.get("toUser").getInt("rating_count") since the results will be instances of Activity, and you'll have eagerly fetched their related toUsers.

This scheme is simpler than what you coded, and will get the job done. However, possibly a major problem is that it will miss data for users with > 1000 followers. Let me know if that's a problem, and I can suggest a more complex answer. A minor shortcoming is that you'll be forced to do the search (maybe a sort) yourself in memory to find the maximum rating_count.

EDIT - For > 1k followers, you're stuck with calling the query multiple times, setting the skip to the count of records received in the previous query, collecting the results in a big array.

Your point about transmitting so much data is well taken, and you can minimize the network use by putting all this into a cloud function, doing the in-memory work in the cloud and only returning the records the client needs. (This approach has the added benefit of being coded in javascript, which I speak more fluently than java, so I could be more prescriptive about the code).

EDIT 2 - Doing this in cloud code has the benefit of reducing the network traffic to just those users (say, 20) that have maximum ratings. It doesn't solve the other problems I indicated earlier. Here's how I'd do it in the cloud...

var _ = require('underscore');

Parse.Cloud.define("topFollowers", function(request, response) {
    var user = new Parse.User({id:request.params.userId});
    topFollowers(user, 20).then(function(result) {
        response.success(result);
    }, function(error) {
        response.error(error);
    });
});

// return the top n users who are the top-rated followers of the passed user
function topFollowers(user, n) {
    var query = new Parse.Query("Activity");
    query.equalTo("type", "follow");
    query.equalTo("fromUser", user);
    query.include("toUser");
    return runQuery(query).then(function(results) {
        var allFollowers = _.map(results, function(result) { return result.get("toUser"); });
        var sortedFollowers = _.sortBy(allFollowers, function(user) { return user.get("rating_count"); });
        return _.first(sortedFollowers, n);
    });
}

// run and rerun a query using skip until all results are gathered in results array
function runQuery(query, results) {
    results = results || [];
    query.skip(results.length);
    return query.find().then(function(nextResults) {
        results = results.concat(nextResults);
        return (nextResults.length)? runQuery(query, results) : results;
    });
}

Note - I haven't tested this, but have similar stuff working in production.

Answer 2

If you're up for changing the data model, there's a solution that gets what you need, plus some side benefits. Consider a system where the User class only pertains to the relationship between the app and a real person. Users' public faces to each other are presented by a new class (call it PublicUser or Persona).

In this PublicUser class, you have a pointer to the user that owns it, and a an array of pointers to other PublicUsers who this one is following. This class also contains the rating attribute. Now the query in the OP is simple:

1. query PublicUser whereKey "following" equals currentUser
2. order by rating, limit to 20 or whatever number you wish to limit

That's it. Another benefit of this scheme is access control. It's understood by the system that anything in PublicUser is readable to other PublicUsers, and everything about __User is kept between that individual person and the app.