In this article we will see how FullSync can handle relational data and how to build relational databases using FullSync NoSQL technology
In Many apps, you may want to create relation between documents in your FullSync databases. For example, let's consider a Flight planning application. We would have 'flight' documents with 'from' and 'to' airports using a 'plane' model. This could be described as this jSON document:
{
_id: "6ccbe0fe-63b3-4063-b73b-16e2cae112c4",
flight: {
"from": "LFPN",
"to": "LFOZ",
"plane": "F-GXCF",
"comment": "test flight",
"type": "flight",
"date": "10/11/2017",
"time": "10:00"
}
}
As you see, this document refers to a 'LFPN' 'from' airport and to a 'LFOZ' 'to' airport, using a 'F-GXCF' plane. All these are IDs to other documents that can be described this way
{
_id: "LFPN",
airport: {
"type": [
"airport",
"medium_airport"
],
"airport_id": "4188",
"iaco": "LFPN",
"name": "Toussus-le-Noble Airport",
"latitude": "48.75189971923828",
"longitude": "2.1061899662017822",
"altitude": "538",
"zone": "EU",
"country": "FR",
"iso_region": "FR-J",
"city": "Toussus-le-Noble",
"scheduled_service": "no",
"gps_code": "LFPN",
"iata_faa": "TNF",
"local_code": "",
"Colonne2": "",
"Colonne3": "",
"Colonne1": "",
"id": "LFPN"
}
}
And for a plane document :
{
_id: "F-GXCF",
plane: {
"type": "plane",
"ID": "3",
"TAG": "F-GXCF",
"TYPE": "AT-01",
"SPEED": "110",
"SEATS": "2",
"pid": "F-GXCF"
}
}
The goal is to get in one query a flight with all the related data attached
This feature is known as 'Linked documents' in CouchDB. Convertigo FullSync supports linked documents on the server side and of course on the client side for all SDKs including the JS based SDKs used by Mobile Builder. More information on linked documents can be found on the official documentation for CouchDB : http://docs.couchdb.org/en/stable/ddocs/views/joins.html
Creating linked documents views is easy (when of course you know how ...) CouchDB documentation says that emitting an object
{_id:<some other document id>}
on an index wil have CouchDB return the linked document if the query is done with 'include_docs' set to 'true'. To illustrate this, here is how we can write a map function using this feature:
function (doc) {
try {
if (doc.flight) {
emit(doc._id, doc.flight);
emit(doc._id, {_id: doc.flight.from});
emit(doc._id, {_id: doc.flight.to});
emit(doc._id, {_id: doc.flight.plane});
}
} catch (err) {
log(err.message);
}
}
As we see here, we emit on the same index, the flight itself plus the 3 linked documents for the 'from' , 'to' airports and the 'plane'
We will query the view exactly as usual by specifying the 'ddoc' (Design document) and the 'view', but we will also set the 'include_docs' parameter to 'true'. If you use Mobile Builder, be sure to set the "include_ docs" property to true in the QueryView Action.
The Query result will be in this format:
{
"total_rows": 4,
"offset": 0,
"rows": [
{
"key": "35f20ed0-a2c7-4943-b108-7803e94b9fc8",
"id": "35f20ed0-a2c7-4943-b108-7803e94b9fc8",
"value": {
"_id": "F-GFZA"
},
"doc": {
"plane": {
"type": "plane",
"ID": "4",
"TAG": "F-GFZA",
"TYPE": "PA-28-181",
"SPEED": "120",
"SEATS": "4",
"pid": "F-GFZA"
},
"_id": "F-GFZA",
"_rev": "1-244618913889b27284f5e93c3080323e"
}
},
{
"key": "35f20ed0-a2c7-4943-b108-7803e94b9fc8",
"id": "35f20ed0-a2c7-4943-b108-7803e94b9fc8",
"value": {
"_id": "LFAI"
},
"doc": {
"airport": {
"type": [
"airport",
"small_airport"
],
"airport_id": "4052",
"iaco": "LFAI",
"name": "Nangis-Les Loges Airport",
"latitude": "48.59619903564453",
"longitude": "3.0067899227142334",
"altitude": "428",
"zone": "EU",
"country": "FR",
"iso_region": "FR-J",
"city": "Nangis/Les Loges",
"scheduled_service": "no",
"gps_code": "LFAI",
"iata_faa": "",
"local_code": "",
"Colonne2": "",
"Colonne3": "",
"Colonne1": "",
"id": "LFAI"
},
"_id": "LFAI",
"_rev": "1-cfdd54a3b80495dc22b5a6fcde71a62e"
}
},
{
"key": "35f20ed0-a2c7-4943-b108-7803e94b9fc8",
"id": "35f20ed0-a2c7-4943-b108-7803e94b9fc8",
"value": {
"_id": "LFPN"
},
"doc": {
"airport": {
"type": [
"airport",
"medium_airport"
],
"airport_id": "4188",
"iaco": "LFPN",
"name": "Toussus-le-Noble Airport",
"latitude": "48.75189971923828",
"longitude": "2.1061899662017822",
"altitude": "538",
"zone": "EU",
"country": "FR",
"iso_region": "FR-J",
"city": "Toussus-le-Noble",
"scheduled_service": "no",
"gps_code": "LFPN",
"iata_faa": "TNF",
"local_code": "",
"Colonne2": "",
"Colonne3": "",
"Colonne1": "",
"id": "LFPN"
},
"_id": "LFPN",
"_rev": "1-b77d4e3f144e235f1c296cefd56a79c4"
}
},
{
"key": "35f20ed0-a2c7-4943-b108-7803e94b9fc8",
"id": "35f20ed0-a2c7-4943-b108-7803e94b9fc8",
"value": {
"from": "LFPN",
"to": "LFAI",
"plane": "F-GFZA",
"occupiedSeats": 2,
"date": "11/10/2018",
"time": "10:00",
"comment": "TEST"
},
"doc": {
"flight": {
"from": "LFPN",
"to": "LFAI",
"plane": "F-GFZA",
"occupiedSeats": 2,
"date": "11/10/2018",
"time": "10:00",
"comment": "TEST"
},
"~c8oAcl": "olivierp@convertigo.com",
"_id": "35f20ed0-a2c7-4943-b108-7803e94b9fc8",
"_rev": "1-4f512abbf19c4667901c90e66136abac"
}
}
]
}
The query will return 4 rows of data for the same key. (One row by emit) And in the response you will have the flight itself plus all the linked documents for this flight
You will be able to use this data in your app easily as row[0] is 'plane' data, row[1] 'from' data, row[2] 'to' data and flight details are in row[3]
