React

Join Query using Parse

Introduction

In this guide, you will perform relational queries in Parse mimicking the behavior of SQL JOIN queries using MongoDB, which is a NoSQL database.

Prerequisites

To complete this tutorial, you will need:

Goal

Query relational data stored on Back4App in SQL JOIN query fashion.

Step 1 - Understanding the Parse.Query class

Any Parse query operation uses the Parse.Query object type, which will help you retrieve specific data from your database throughout your app. It is crucial to know that a Parse.Query will only resolve after calling a retrieve method (like Parse.Query.find or Parse.Query.get), so a query can be set up and several modifiers can be chained before actually being called.

To create a new Parse.Query, you need to pass as a parameter the desired Parse.Object subclass, which is the one that will contain your query results. An example query can be seen below, in which a fictional Profile subclass is being queried.

1
2
3
4
// This will create your query
let parseQuery = new Parse.Query("Profile");
// The query will resolve only after calling this method
let queryResult = await parseQuery.find();

You can read more about the Parse.Query class here at the official documentation.

Step 2 - Save some data on Back4App

Let’s create two example classes, TableA and TableB, which will be the targets of our queries in this guide. On Parse JS Console it is possible to run JavaScript code directly, querying and updating your application database contents using the JS SDK commands. Run the code below from your JS Console and insert the data on Back4App.

Here is how the JS Console looks like in your dashboard:

React Back4App

Go ahead and create the classes with the following content:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
// Create TableA and its records
let TableARecord1 = new Parse.Object('TableA');
TableARecord1.set('FieldA', 'Value A 1');
TableARecord1 = await TableARecord1.save();

let TableARecord2 = new Parse.Object('TableA');
TableARecord2.set('FieldA', 'Value A 2');
TableARecord2 = await TableARecord2.save();

let TableARecord3 = new Parse.Object('TableA');
TableARecord3.set('FieldA', 'Value A 3');
TableARecord3 = await TableARecord3.save();

// Create TableB and its records, some of them linked to TableA
let TableBRecord1 = new Parse.Object('TableB');
TableBRecord1.set('FieldB', 'Value B 1');
TableBRecord1.set('link', TableARecord1);
TableBRecord1 = await TableBRecord1.save();

let TableBRecord2 = new Parse.Object('TableB');
TableBRecord2.set('FieldB', 'Value B 2');
TableBRecord2.set('link', TableARecord1);
TableBRecord2 = await TableBRecord2.save();

let TableBRecord3 = new Parse.Object('TableB');
TableBRecord3.set('FieldB', 'Value B 3');
TableBRecord3.set('link', TableARecord3);
TableBRecord3 = await TableBRecord3.save();

let TableBRecord4 = new Parse.Object('TableB');
TableBRecord4.set('FieldB', 'Value B 4');
TableBRecord4 = await TableBRecord4.save();

console.log('Success!');

Step 3 - Querying the data

Now that you have populated the classes, we can now perform the relational queries in it. Let’s begin by performing the INNER JOIN, introducing the join relational query that we will use in all of our examples. This query represents the results of two combined queries between tables A and B, returning all the records that are related by a specific condition using the Parse.Query.matchesQuery method.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
// JOIN query, get all records in TableA that have matching records in TableB
let innerQueryTableA = new Parse.Query("TableA");
// Limit to 10 results only for example so we don't fetch too much data
innerQueryTableA.limit(10);
let joinQueryTableB = new Parse.Query("TableB");
// Match the TableA query by the "link" property
joinQueryTableB.matchesQuery("link", innerQueryTableA);
// Include the "link" property so we have the content of TableA as well
joinQueryTableB.include("link");
let joinQueryResults = await joinQueryTableB.find();

// INNER JOIN, get only the records in TableA that have matching records in TableB
console.log("INNER JOIN");
console.log("TABLE A ID | FIELD A | FIELD B");
for (let joinResult of joinQueryResults) {
  console.log(
    `${joinResult.get("link").id} | ${joinResult
      .get("link")
      .get("FieldA")} | ${joinResult.get("FieldB")}`
  );
}

The INNER JOIN SQL query behavior is exactly the one achieved in our generic join relational query, so we need to print its results in the console. Remember that with a Parse.Object you can use the get method to retrieve data by using the column name.

Let’s now perform a LEFT OUTER JOIN consisting of getting all the records on TableA and showing the relational data on TableB, when available:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
// JOIN query, get all records in TableA that have matching records in TableB
let innerQueryTableA = new Parse.Query("TableA");
// Limit to 10 results only for example so we don't fetch too much data
innerQueryTableA.limit(10);
let joinQueryTableB = new Parse.Query("TableB");
// Match the TableA query by the "link" property
joinQueryTableB.matchesQuery("link", innerQueryTableA);
// Include the "link" property so we have the content of TableA as well
joinQueryTableB.include("link");
let joinQueryResults = await joinQueryTableB.find();

// LEFT OUTER JOIN, get records in TableA that have matching records in TableB and also every
// other TableA record
let queryTableA = new Parse.Query("TableA");
queryTableA.limit(10);
let queryTableAResults = await queryTableA.find();
console.log("LEFT JOIN");
console.log("TABLE A ID | FIELD A | FIELD B");
for (let result of queryTableAResults) {
  // Get all entries from JOIN query that have a link to this TableA entry
  let joinQueryResultsFiltered = joinQueryResults.filter(
    (joinQueryResult) =>
      joinQueryResult.get("link") !== undefined &&
      joinQueryResult.get("link").id == result.id
  );
  if (joinQueryResultsFiltered.length > 0) {
    for (let joinResult of joinQueryResultsFiltered) {
      let fieldBValue = joinResult.get("FieldB");
      console.log(`${result.id} | ${result.get("FieldA")} | ${fieldBValue}`);
    }
  } else {
    console.log(`${result.id} | ${result.get("FieldA")} | `);
  }
}

The RIGHT OUTER JOIN is the opposite of the left one, fetching the records from TableB.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
// JOIN query, get all records in TableA that have matching records in TableB
let innerQueryTableA = new Parse.Query("TableA");
// Limit to 10 results only for example so we don't fetch too much data
innerQueryTableA.limit(10);
let joinQueryTableB = new Parse.Query("TableB");
// Match the TableA query by the "link" property
joinQueryTableB.matchesQuery("link", innerQueryTableA);
// Include the "link" property so we have the content of TableA as well
joinQueryTableB.include("link");
let joinQueryResults = await joinQueryTableB.find();

// RIGHT OUTER JOIN, get records in TableA that have matching records in TableB and also every
// other TableB record
let queryTableB = new Parse.Query("TableB");
queryTableB.limit(10);
let queryTableBResults = await queryTableB.find();
console.log("RIGHT JOIN");
console.log("TABLE B ID | FIELD A | FIELD B");
for (let result of queryTableBResults) {
  // Get all entries from JOIN query that matches this TableB entry
  let joinQueryResultsFiltered = joinQueryResults.filter(
    (joinQueryResult) => joinQueryResult.id == result.id
  );
  if (joinQueryResultsFiltered.length > 0) {
    for (let joinResult of joinQueryResultsFiltered) {
      let fieldAValue = "";
      if (joinResult.get("link") !== undefined) {
        fieldAValue = joinResult.get("link").get("FieldA");
      }
      console.log(
        `${result.id} | ${fieldAValue} | ${joinResult.get("FieldB")}`
      );
    }
  } else {
    console.log(`${result.id} | | ${result.get("FieldB")}`);
  }
}

Finally, we have the FULL OUTER JOIN which is the combination of the left and right inner joins:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
// JOIN query, get all records in TableA that have matching records in TableB
let innerQueryTableA = new Parse.Query("TableA");
// Limit to 10 results only for example so we don't fetch too much data
innerQueryTableA.limit(10);
let joinQueryTableB = new Parse.Query("TableB");
// Match the TableA query by the "link" property
joinQueryTableB.matchesQuery("link", innerQueryTableA);
// Include the "link" property so we have the content of TableA as well
joinQueryTableB.include("link");
let joinQueryResults = await joinQueryTableB.find();

// FULL OUTER JOIN, combining LEFT and RIGHT OUTER JOIN results
console.log("FULL JOIN");
console.log("TABLE ID | FIELD A | FIELD B");
// First print all INNER JOIN results
for (let joinResult of joinQueryResults) {
  console.log(
    `${joinResult.get("link").id} | ${joinResult
      .get("link")
      .get("FieldA")} | ${joinResult.get("FieldB")}`
  );
}
// Print LEFT JOIN leftovers
let outerQueryTableA = new Parse.Query("TableA");
outerQueryTableA.limit(10);
let outerQueryTableAResults = await outerQueryTableA.find();
// Get all entries from query that doesn't match the JOIN query results
let filteredOuterQueryTableAResults = outerQueryTableAResults.filter(
  (outerQueryTableAResult) =>
    joinQueryResults.find(
      (joinQueryResult) =>
        joinQueryResult.get("link") !== undefined &&
        joinQueryResult.get("link").id === outerQueryTableAResult.id
    ) === undefined
);
for (let result of filteredOuterQueryTableAResults) {
  console.log(`${result.id} | ${result.get("FieldA")} | `);
}
// Print RIGHT JOIN leftovers
let outerQueryTableB = new Parse.Query("TableB");
outerQueryTableB.limit(10);
let outerQueryTableBResults = await outerQueryTableB.find();
// Get all entries from query that doesn't match the JOIN query results
let filteredOuterQueryTableBResults = outerQueryTableBResults.filter(
  (outerQueryTableBResult) =>
    joinQueryResults.find(
      (joinQueryResult) => joinQueryResult.id === outerQueryTableBResult.id
    ) === undefined
);
for (let result of filteredOuterQueryTableBResults) {
  console.log(`${result.id} | | ${result.get("FieldB")}`);
}

Conclusion

At the end of this guide, you learned how to perform on Back4App relational queries using Parse and emulating the most common SQL JOIN queries in a NoSQL database.