Add Relationships to Model

There are scenarios in Application development when Models need to be related to each other. This guide demonstrates the process to define relations between 2 models and ways to manage data for related models. Relationship is very important concept of oeCloud and is very powerful to build complex query. Understanding this concept will be helpful to you in further understanding of oeCloud.

What you will learn

By the end of this guide you will learn to

  • Create a Model Customer having hasMany relation with Account.
  • Create a Model Account having hasMany relation with AccountTransaction.

What you will need

To complete this guide, you will need the following -

  • an understanding of what a Model is in the context of the oeCloud.io.
  • an understanding of what Relations between models. It is very similar to loopback framework.
  • a running NodeJS application built using the oeCloud Framework.
  • a working REST client. You can use the Linux cURL command as a REST client if you have access to a Linux machine or have Git Bash installed on your Windows machine. REST addons for browsers, like Postman for Firefox or Google Chrome also can be used to complete this guide.

How to complete this Guide

Download and Install oeCloud.io application

Click here

Creating Customer, Account and AccountTransaction Models

Once you have started the server, open a browser link: http://localhost:3000/explorer/.

We will first create the Customer model with the name property and a relation to Account with foreign key as customerId. But briefly, you can POST below JSON Data for Customer Model to ModelDefinitions. Request API will be http://localhost:3000/api/ModelDefinitions.

{
    "properties": {
        "name": {
            "type": "string"
        }
    },
    "name": "Customer",
    "description": "customer desc",
    "plural": "Customers",
    "base": "BaseEntity",
    "relations": {
        "accountsRel": {
            "type": "hasMany",
            "model": "Account",
            "foreignKey": "customerId"
        }
    }
}

Now we will create the Account model with accountType,accountBalance property and relations to Customer as well as AccountTransaction. To do this, you can follow the Create a Model with Properties Guide. But briefly, you can POST below JSON Data for Account Model to ModelDefinitions. Request API will be http://localhost:3000/api/ModelDefinitions..

{
  "properties": {
    "accountType"  : {
        "type": "string"
    },
    "accountBalance"  : {
        "type": "number"
    }
  },
  "name": "Account",
  "description": "Account desc",
  "plural": "Accounts",
  "base": "BaseEntity",  
  "relations": {
    "customerRel": {
        "type": "belongsTo",
        "model": "Customer",
        "foreignKey": "customerId"  
    },
    "transactionRel": {
        "type": "hasMany",
        "model": "AccountTransaction",
        "foreignKey": "accountId"  
    }
  }
}

Now we will create the AccountTransaction model with transactionType,amount property and relations to Customer as well as AccountTransaction. To do this, you can follow the Create a Model with Properties Guide. But briefly, you can POST below JSON Data for AccountTransaction Model to ModelDefinitions. Request API will be http://localhost:3000/api/ModelDefinitions..

{
  "properties": {
    "transactionType"  : {
        "type": "string"
    },
    "amount"  : {
        "type": "number"
    }
  },
  "name": "AccountTransaction",
  "description": "Account Transaction desc",
  "plural": "AccountTransactions",
  "base": "BaseEntity"
}

POSTing data to Customer, Account and AccountTransaction Models

Now POST below data to Customer Model. Request API will be http://localhost:3000/api/Customers. Request Data

[
    {
        "name" : "john",
        "id" : 1
    },
    {
        "name" : "dave",
        "id" : 2
    }              
]

POST below data to Account Model. Request API will be http://localhost:3000/api/Accounts Account Data

[
    {
        "accountType" : "saving",
        "accountBalance" : 5200,
        "id" : 1,
        "customerId" : 1
    },
    {
        "accountType" : "loan",
        "accountBalance" : 800,
        "id" : 2,
        "customerId" : 1
    },
    {
        "accountType" : "fd",
        "accountBalance" : 11000,
        "id" : 3,
        "customerId" : 1
    },
    {
        "accountType" : "saving",
        "accountBalance" : 1005,
        "id" : 4,
        "customerId" : 2
    },
    {
        "accountType" : "fd",
        "accountBalance" : 1000,
        "id" : 5,
        "customerId" : 2
    }
]

POST below data to AccountTransaction Model. Request API will be http://localhost:3000/api/AccountTransactions

[
    {
        "transactionType" : "credit",
        "amount" : 5000,
        "id" : 1,
        "accountId" : 1
    },
    {
        "transactionType" : "credit",
        "amount" : 100,
        "id" : 2,
        "accountId" : 1
    },
    {
        "transactionType" : "emi",
        "amount" : 100,
        "id" : 3,
        "accountId" : 2
    },
    {
        "transactionType" : "emi",
        "amount" : 100,
        "id" : 4,
        "accountId" : 2
    },
    {
        "transactionType" : "credit",
        "amount" : 10000,
        "id" : 5,
        "accountId" : 3
    },
    {
        "transactionType" : "interest",
        "amount" : 1100,
        "id" : 6,
        "accountId" : 3
    },
    {
        "transactionType" : "credit",
        "amount" : 1000,
        "id" : 7,
        "accountId" : 4
    },
    {
        "transactionType" : "credit",
        "amount" : 5,
        "id" : 8,
        "accountId" : 4
    },
    {
        "transactionType" : "credit",
        "amount" : 1000,
        "id" : 9,
        "accountId" : 5
    }              
]

GETting data from Customer, Account and AccountTransaction Models using Relationship Concepts

  • Basic relation - to get customer and all accounts belongs to customer

put following filter in Swagger and use ’try it’ option of ‘Get’ operation on Customer

Request URL : http://localhost:3000/api/Customers
filter: {"include" : "accountsRel"}

Get URL will look like

http://localhost:3000/api/Customers?filter=%7B%22include%22%20%3A%20%22accountsRel%22%20%7D

which is really encoded URL of 

http://localhost:3000/api/Customers?filter={"include" : "accountsRel" }
  • Nested relation - to get customer and all accounts belongs to customer and all transactions on account
Request URL : http://localhost:3000/api/Customers

filter: {"include" : {"accountsRel" : "transactionRel" } }

you should see data as shown below

[
  {
    "name": "jhon",
    "gender": "m",
    "age": 30,
    "id": 1,
    "accountsRel": [
      {
        "accountType": "saving",
        "accountBalance": 5200,
        "id": 1,
        "customerId": 1,
        "transactionRel": [
          {
            "transactionType": "credit",
            "amount": 5000,
            "id": 1,
            "accountId": 1
          },
          {
            "transactionType": "credit",
            "amount": 100,
            "id": 2,
            "accountId": 1
          }
        ]
      },
      {
        "accountType": "loan",
        "accountBalance": 800,
        "id": 2,
        "customerId": 1,
        "transactionRel": [
          {
            "transactionType": "emi",
            "amount": 100,
            "id": 3,
            "accountId": 2
          },
          {
            "transactionType": "emi",
            "amount": 100,
            "id": 4,
            "accountId": 2
          }
        ]
      },
      {
        "accountType": "fd",
        "accountBalance": 11000,
        "id": 3,
        "customerId": 1,
        "transactionRel": [
          {
            "transactionType": "credit",
            "amount": 10000,
            "id": 5,
            "accountId": 3
          },
          {
            "transactionType": "interest",
            "amount": 1100,
            "id": 6,
            "accountId": 3
          }
        ]
      }
    ]
  },
  {
    "name": "dave",
    "gender": "m",
    "age": 35,
    "id": 2,
    "accountsRel": [
      {
        "accountType": "saving",
        "accountBalance": 1005,
        "id": 4,
        "customerId": 2,
        "transactionRel": [
          {
            "transactionType": "credit",
            "amount": 1000,
            "id": 7,
            "accountId": 4
          },
          {
            "transactionType": "credit",
            "amount": 5,
            "id": 8,
            "accountId": 4
          }
        ]
      },
      {
        "accountType": "fd",
        "accountBalance": 1000,
        "id": 5,
        "customerId": 2,
        "transactionRel": [
          {
            "transactionType": "credit",
            "amount": 1000,
            "id": 9,
            "accountId": 5
          }
        ]
      }
    ]
  }
]
  • Relationship with where clause on parent model (customer in this case) to get Customer data of id:1
http://localhost:3000/api/Customers

filter: { "where" : {"id" : 1}, "include" : "accountsRel"}
  • Relation with where clause on parent model and child model too. (Get Customer whose id is 1 along with his SAVING account)
http://localhost:3000/api/Customers

filter: { "where" : {"id" : 1}, "include" : {"relation" : "accountsRel" , "scope" : { "where" : {"accountType" : "saving" } } } }
  • Relationship with where clause on parent model (customer in this case), where clause on child Model and child’s child data ( Get Customer with id:1 ->his SAVING accounts->Transactions on those accounts )
http://localhost:3000/api/Customers
filter : { "where" : {"id" : 1}, "include" : {"relation" : "accountsRel" , "scope" : { "where" : {"accountType" : "saving" }, "include" : "transactionRel" } } }
  • Add filter to transaction where transaction amount greater than 50
filter : { "where" : {"id" : 1}, "include" : {"relation" : "accountsRel" , "scope" : { "where" : {"accountType" : "saving" }, "include" : { "relation" : "transactionRel", "scope" : { "where" : {"amount": {"gt": 50}}} } } } }
  • We want only 5 transactions per account ( limit clause )
http://localhost:3000/api/Customers

filter: { "where" : {"id" : 1}, "include" : {"relation" : "accountsRel" , "scope" : { "where" : {"accountType" : "saving" }, "include" : { "relation" : "transactionRel", "scope" : { "where" : {"amount": {"gt": 50}}, "limit" : 5 } } } } }
  • we want top 5 transactions per account ( Putting order clause )
http://localhost:3000/api/Customers

filter: { "where" : {"id" : 1}, "include" : {"relation" : "accountsRel" , "scope" : { "where" : {"accountType" : "saving" }, "include" : { "relation" : "transactionRel", "scope" : { "where" : {"amount": {"gt": 10}}, "limit" : 5, "order" : "amount desc" } } } } }

you will get following data


[
  {
    "name": "jhon",
    "gender": "m",
    "age": 30,
    "id": 1,
    "accountsRel": [
      {
        "accountType": "saving",
        "accountBalance": 5200,
        "id": 1,
        "customerId": 1,
        "transactionRel": [
          {
            "transactionType": "credit",
            "amount": 100,
            "id": 2,
            "accountId": 1
          },
          {
           "transactionType": "credit",
            "amount": 5000,
            "id": 1,
            "accountId": 1
          }
        ]
      }
    ]
  }
]
  • Using BelongsTo relation

This is rarely used. With this, you can get parent relation data from child model with relation name. For example below, we are getting customer for accountId : 5

http://localhost:1444/api/Accounts/5/customerRel

You will get data as

{
                "name":"dave",
                "gender":"m",
                "age":35,
                "id":2
}

Summary

We have seen now

  • How hasMany and belongsTo relations working
  • How you can get child relations data along with filters
  • How several relations and filters and scope fields can be used to form complex query