Parse.com LEFT JOIN alternative

Question

Can someone please explain what the correct data structure would be in a noSQL environment like parse.com to do the equivalent of a LEFT JOIN in SQL. I come from a relational world so I am very much open to the idea that my tables need to be entirely redesigned.

I want to store "Jobs" and "Users" where Users can post Jobs and Apply to Jobs. So I created three tables: "Job", "User", and "Application". Applications has three columns: "pointer to Job", "pointer to User", and "Application status".

Problem is:

1. I can't in single query get all the jobs posted by a user with the number of applications for each.

2. I can't in a single query get all available job for a user and also know whether that user applied

   I am using parse.com for this project.

EDIT for bounty

I have several solutions with either multiple queries or using arrays in the Job table. Each has limitations but I am looking to understand if there is a better data structure or query to get all jobs with the applications for those jobs.

Answer 1

Second answer in brief: taking the question constraints strictly (given data model, one query, given output), I think the problem is over constrained. We can achieve the functional goal by relaxing either the fixed data model or the single query.

First, relaxing single query. My answer above I assumed the following data model...

Application --1 User (applicant)
          |
          --1 Job --1 User (postingUser)

...and suggested a single query of Application where the app job matched a Job via a second implicit inner query. This idea solved most of the problem, but misses Jobs for which there are no applications (INNER JOIN, as you point out).

Repairing that error, we could achieve the goal with two explicit queries as follows:

var user = // a given user
var jobQuery = new Parse.Query("Job");
jobQuery.equalTo("postingUser", user);
var jobs = [];
jobQuery.find().then(function(results) {
    // requirement 1: jobs posted by a user
    jobs = results;
    var appQuery = new Parse.Query("Application");
    appQuery.containedIn("job", jobs);
    return appQuery.find();
}).then(function(apps) {
    _.each(jobs, function(job) {
        // requirement 1.1: jobs' related to apps (trivial to count them)
        job["applications"] = _.find(apps, function(app) {
            return app.get("job").id == job.id;
        });

        // requirement 2: decorate jobs where the postingUser is an applicant
        // could have combined loops above, but looping again for clarity
        _.each(jobs, function(job) {
            var posterIsApplicant = _.find(apps, function(app) {
                return app.get("applicant").id == user.id
            });
            job["posterIsApplicant"] = app.get("user").id == user.id;
        });            
    });
});

Alternatively, unconstrained by the data model, but with a single query, we can get the functional result by arranging the data this way:

Job --1 User (postingUser)
   |
   --< Application (applications) --1 User (postingUser)

Here, Job has a pointer to user and an array of pointers (or a relation if you expect the cardinality to be high... my rule of thumb is > 100) to Application. Application could continue to have a single pointer to Job if it was needed elsewhere (this could be maintained in beforeSave cloud code).

With that, its simple to achieve the function with a single query:

var user = // a given user
var jobQuery = new Parse.Query("Job");
jobQuery.equalTo("postingUser", user);
jobQuery.include("applications")l
jobQuery.find().then(function(results) {
    // requirement 1: results contains jobs, each has an array
    // of applications
    // requirement 2: is copy-paste from above
    _.each(results, function(job) {
        var apps = job.get("applications");
        var posterIsApplicant = _.find(apps, function(app) {
            return app.get("applicant").id == user.id
        });
        job["posterIsApplicant"] = app.get("user").id == user.id;
    });            
});