Join Queries

join query using parse introduction in this guide, you will perform relational queries in parse mimicking the behavior of sql join queries using a nosql database prerequisites to complete this tutorial, you will need an app created on back4app goal query relational data stored on back4app in sql join query fashion 1 understanding the parse query class any parse query operation uses the parse query 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 parse query will only resolve after calling a retrieve method (like parse query find parse query find or parse query get 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 parse query , you need to pass as a parameter the desired parse object 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 profile subclass is being queried 1 // this will create your query 2 let parsequery = new parse query("profile"); 3 // the query will resolve only after calling this method 4 let queryresult = await parsequery find(); you can read more about the parse query parse query class here at the official documentation https //parseplatform org/parse sdk js/api/master/parse query html 2 save some data on back4app let’s create two example classes, tablea tablea and tableb 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 go ahead and create the classes with the following content 1 // create tablea and its records 2 let tablearecord1 = new parse object('tablea'); 3 tablearecord1 set('fielda', 'value a 1'); 4 tablearecord1 = await tablearecord1 save(); 5	 6 let tablearecord2 = new parse object('tablea'); 7 tablearecord2 set('fielda', 'value a 2'); 8 tablearecord2 = await tablearecord2 save(); 9	 10 let tablearecord3 = new parse object('tablea'); 11 tablearecord3 set('fielda', 'value a 3'); 12 tablearecord3 = await tablearecord3 save(); 13	 14 // create tableb and its records, some of them linked to tablea 15 let tablebrecord1 = new parse object('tableb'); 16 tablebrecord1 set('fieldb', 'value b 1'); 17 tablebrecord1 set('link', tablearecord1); 18 tablebrecord1 = await tablebrecord1 save(); 19	 20 let tablebrecord2 = new parse object('tableb'); 21 tablebrecord2 set('fieldb', 'value b 2'); 22 tablebrecord2 set('link', tablearecord1); 23 tablebrecord2 = await tablebrecord2 save(); 24	 25 let tablebrecord3 = new parse object('tableb'); 26 tablebrecord3 set('fieldb', 'value b 3'); 27 tablebrecord3 set('link', tablearecord3); 28 tablebrecord3 = await tablebrecord3 save(); 29	 30 let tablebrecord4 = new parse object('tableb'); 31 tablebrecord4 set('fieldb', 'value b 4'); 32 tablebrecord4 = await tablebrecord4 save(); 33	 34 console log('success!'); 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 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 parse query matchesquery method 1 // join query, get all records in tablea that have matching records in tableb 2 let innerquerytablea = new parse query("tablea"); 3 // limit to 10 results only for example so we don't fetch too much data 4 innerquerytablea limit(10); 5 let joinquerytableb = new parse query("tableb"); 6 // match the tablea query by the "link" property 7 joinquerytableb matchesquery("link", innerquerytablea); 8 // include the "link" property so we have the content of tablea as well 9 joinquerytableb include("link"); 10 let joinqueryresults = await joinquerytableb find(); 11	 12 // inner join, get only the records in tablea that have matching records in tableb 13 console log("inner join"); 14 console log("table a id | field a | field b"); 15 for (let joinresult of joinqueryresults) { 16 console log( 17 `${joinresult get("link") id} | ${joinresult 18 get("link") 19 get("fielda")} | ${joinresult get("fieldb")}` 20 ); 21 } the inner join 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 parse object you can use the get method to retrieve data by using the column name let’s now perform a left outer join left outer join consisting of getting all the records on tablea tablea and showing the relational data on tableb, when available 1 // join query, get all records in tablea that have matching records in tableb 2 let innerquerytablea = new parse query("tablea"); 3 // limit to 10 results only for example so we don't fetch too much data 4 innerquerytablea limit(10); 5 let joinquerytableb = new parse query("tableb"); 6 // match the tablea query by the "link" property 7 joinquerytableb matchesquery("link", innerquerytablea); 8 // include the "link" property so we have the content of tablea as well 9 joinquerytableb include("link"); 10 let joinqueryresults = await joinquerytableb find(); 11	 12 // left outer join, get records in tablea that have matching records in tableb and also every 13 // other tablea record 14 let querytablea = new parse query("tablea"); 15 querytablea limit(10); 16 let querytablearesults = await querytablea find(); 17 console log("left join"); 18 console log("table a id | field a | field b"); 19 for (let result of querytablearesults) { 20 // get all entries from join query that have a link to this tablea entry 21 let joinqueryresultsfiltered = joinqueryresults filter( 22 (joinqueryresult) => 23 joinqueryresult get("link") !== undefined && 24 joinqueryresult get("link") id == result id 25 ); 26 if (joinqueryresultsfiltered length > 0) { 27 for (let joinresult of joinqueryresultsfiltered) { 28 let fieldbvalue = joinresult get("fieldb"); 29 console log(`${result id} | ${result get("fielda")} | ${fieldbvalue}`); 30 } 31 } else { 32 console log(`${result id} | ${result get("fielda")} | `); 33 } 34 } the right outer join right outer join is the opposite of the left one, fetching the records from tableb tableb 1 // join query, get all records in tablea that have matching records in tableb 2 let innerquerytablea = new parse query("tablea"); 3 // limit to 10 results only for example so we don't fetch too much data 4 innerquerytablea limit(10); 5 let joinquerytableb = new parse query("tableb"); 6 // match the tablea query by the "link" property 7 joinquerytableb matchesquery("link", innerquerytablea); 8 // include the "link" property so we have the content of tablea as well 9 joinquerytableb include("link"); 10 let joinqueryresults = await joinquerytableb find(); 11	 12 // right outer join, get records in tablea that have matching records in tableb and also every 13 // other tableb record 14 let querytableb = new parse query("tableb"); 15 querytableb limit(10); 16 let querytablebresults = await querytableb find(); 17 console log("right join"); 18 console log("table b id | field a | field b"); 19 for (let result of querytablebresults) { 20 // get all entries from join query that matches this tableb entry 21 let joinqueryresultsfiltered = joinqueryresults filter( 22 (joinqueryresult) => joinqueryresult id == result id 23 ); 24 if (joinqueryresultsfiltered length > 0) { 25 for (let joinresult of joinqueryresultsfiltered) { 26 let fieldavalue = ""; 27 if (joinresult get("link") !== undefined) { 28 fieldavalue = joinresult get("link") get("fielda"); 29 } 30 console log( 31 `${result id} | ${fieldavalue} | ${joinresult get("fieldb")}` 32 ); 33 } 34 } else { 35 console log(`${result id} | | ${result get("fieldb")}`); 36 } 37 } finally, we have the full outer join full outer join which is the combination of the left and right inner joins 1 // join query, get all records in tablea that have matching records in tableb 2 let innerquerytablea = new parse query("tablea"); 3 // limit to 10 results only for example so we don't fetch too much data 4 innerquerytablea limit(10); 5 let joinquerytableb = new parse query("tableb"); 6 // match the tablea query by the "link" property 7 joinquerytableb matchesquery("link", innerquerytablea); 8 // include the "link" property so we have the content of tablea as well 9 joinquerytableb include("link"); 10 let joinqueryresults = await joinquerytableb find(); 11	 12 // full outer join, combining left and right outer join results 13 console log("full join"); 14 console log("table id | field a | field b"); 15 // first print all inner join results 16 for (let joinresult of joinqueryresults) { 17 console log( 18 `${joinresult get("link") id} | ${joinresult 19 get("link") 20 get("fielda")} | ${joinresult get("fieldb")}` 21 ); 22 } 23 // print left join leftovers 24 let outerquerytablea = new parse query("tablea"); 25 outerquerytablea limit(10); 26 let outerquerytablearesults = await outerquerytablea find(); 27 // get all entries from query that doesn't match the join query results 28 let filteredouterquerytablearesults = outerquerytablearesults filter( 29 (outerquerytablearesult) => 30 joinqueryresults find( 31 (joinqueryresult) => 32 joinqueryresult get("link") !== undefined && 33 joinqueryresult get("link") id === outerquerytablearesult id 34 ) === undefined 35 ); 36 for (let result of filteredouterquerytablearesults) { 37 console log(`${result id} | ${result get("fielda")} | `); 38 } 39 // print right join leftovers 40 let outerquerytableb = new parse query("tableb"); 41 outerquerytableb limit(10); 42 let outerquerytablebresults = await outerquerytableb find(); 43 // get all entries from query that doesn't match the join query results 44 let filteredouterquerytablebresults = outerquerytablebresults filter( 45 (outerquerytablebresult) => 46 joinqueryresults find( 47 (joinqueryresult) => joinqueryresult id === outerquerytablebresult id 48 ) === undefined 49 ); 50 for (let result of filteredouterquerytablebresults) { 51 console log(`${result id} | | ${result get("fieldb")}`); 52 } 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 join queries in a nosql database