Skip to main content

Module 3: Relationships

SubQuery TeamAbout 12 min

Module 3: Relationships

This module explains the different types of entity relations (one-to-one, one-to-many, and many-to-many) with guided examples. The module is divided into 3 video lessons for in-depth explanations.

Note

For a basic uderstanding of the terminologies related to entity relations, visit GraphQL Schema Documentation.

Let's have a look at each relationship one-by-one.

Lesson 1: One to Many Entities


Exercise - Balances Transfers (One-to-Many)

In these exercises, we will take the starter project and focus on understanding one to many entity relationships. We will create a project that allows us to query for accounts and determine how much was transferred to what receiving address.

Pre-Requisites

Completion of Module 2.

Overview of Steps Involved

  1. Initialise the starter project.
  2. Update your mappings, manifest file, and graphql schema file by removing all the default code except for the handleEvent function.
  3. Generate, build, and deploy your code.
  4. Deploy your code in Docker.
  5. Query for address transfers in the playground.

Detailed Steps

Step 1: Initialise Your Project

The first step to create a SubQuery project using the following command:

$ subql init
Project name [subql-starter]: account-transfers
? Select a network family Substrate
? Select a network Polkadot
? Select a template project subql-starter     Starter project for subquery
RPC endpoint: [wss://polkadot.api.onfinality.io/public-ws]:
Git repository [https://github.com/subquery/subql-starter]:
Fetching network genesis hash... done
Author [Ian He & Jay Ji]:
Description [This project can be use as a starting po...]:
Version [1.0.0]:
License [MIT]:
Preparing project... done
account-transfers is ready

Step 2: Update the Graphql Schema

Create an entity called Account. This account will contain multiple transfers. Here, an account can be considered as a Polkadot address owned by someone.

Transfers can be considered as a transaction with an amount, a sender, and a receiver(let’s ignore the sender for now). Here, you will obtain the amount transferred, the blockNumber, and to whom it was sent(also known as the receiver).

  • The schema file should look like this:
type Account @entity {
  id: ID! #this primary key is set as the toAddress
}

type Transfer @entity {
  id: ID!
  amount: BigInt
  blockNumber: BigInt
  to: Account! # receiving address
}

Step 3: Update the Manifest File (aka project.yaml)

Update the manifest file to only include the handleEvent handler and update the filter method to Transfer. The reason is that we only want to work with the "balance transfer events" in this example. These events will contain the data of those transactions, which are being transferred from one account to another.

Important

Avoid messing with the auto-generated version names(as shown in the initial section of the manifest file).

  • The updated part of the project.yaml file should look similar to as below:
network:
  chainId: '0x91b171bb158e2d3848fa23a9f1c25182fb8e20313b2c1eb49219da7a70ce90c3'
  endpoint: 'wss://polkadot.api.onfinality.io/public-ws'
  dictionary: 'https://api.subquery.network/sq/subquery/polkadot-dictionary'
  #genesisHash: '0x91b171bb158e2d3848fa23a9f1c25182fb8e20313b2c1eb49219da7a70ce90c3'
dataSources:
  - kind: substrate/Runtime
    startBlock: 1
    mapping:
      file: ./dist/index.js
      handlers:
        - handler: handleEvent
          kind: substrate/EventHandler
          filter:
            module: balances
            method: Transfer

Note

Note the inclusion of a dictionary and the exclusion of the genesisHash.

Step 4: Update the Mappings File

The initialisation command pre-creates a sample mappings file with 3 functions: handleBlock, handleEvent, and handleCall. As we are only focusing on handleEvent, delete the remaining functions.

Note that you also need to make a few other changes. First, understand that the balance.transfer event gives access to an array of data in the following format: [from, to, value].

This indicates that you can access the values as follows:

    const fromAddress = event.event.data[0];
    const toAddress = event.event.data[1];
    const amount = event.event.data[2];

Furthermore, as the Account entity (formally called the StarterEntity) was instantiated in the handleBlock function and you no longer have this, you need to instantiate it within the handleEvent function.

However, you must first test and see if this value is already in your database. The reason is that an event can contain multiple transfers to the SAME toAddress. As a result, you get the toAddress if the value is present in the database. And if it does not exist, save it to the database.

       const toAccount = await Account.get(toAddress.toString());
       if (!toAccount) {
           await new Account(toAddress.toString()).save();
       }

For the Transfer entity object, set the primary key as the blocknumber+event.idx (which guarantees uniqueness) and then set the other fields of the Transfer entity object accordingly.

    const transfer = new Transfer(`${event.block.block.header.number.toNumber()}-${event.idx}`, );
    transfer.blockNumber = event.block.block.header.number.toBigInt();
    transfer.toId = toAddress.toString();
    transfer.amount = (amount as Balance).toBigInt();
    await transfer.save();
  • The mappingHandler.ts file should look like this:
import {SubstrateEvent} from "@subql/types";
import {Account, Transfer} from "../types";
import {Balance} from "@polkadot/types/interfaces";

export async function handleEvent(event: SubstrateEvent): Promise<void> {
    {
        // The balances.transfer event has the following payload \[from, to, value\] that we can access

        // const fromAddress = event.event.data[0];
        const toAddress = event.event.data[1];
        const amount = event.event.data[2];

           // query for toAddress from DB
           const toAccount = await Account.get(toAddress.toString());
           // if not in DB, instantiate a new Account object using the toAddress as a unique ID
           if (!toAccount) {
               await new Account(toAddress.toString()).save();
           }

        // instantiate a new Transfer object using the block number and event.idx as a unique ID
        const transfer = new Transfer(`${event.block.block.header.number.toNumber()}-${event.idx}`, );
        transfer.blockNumber = event.block.block.header.number.toBigInt();
        transfer.toId = toAddress.toString();
        transfer.amount = (amount as Balance).toBigInt();
        await transfer.save();

    }
}

Step 5: Install the Dependencies

Install the node dependencies by running the following commands:

yarn
yarn install

Step 6: Generate the Associated Typescript

Next, we will generate the associated typescript with the following command:

yarn
yarn codegen

Step 7: Build the Project

The next step is to build the project with the following command:

yarn
yarn build

This code bundles the app into static files for production.

Step 8: Start the Docker Container

Run the docker command to pull the images and start the container.

docker-compose pull & docker-compose up

Step 9: Run a Query

Once the docker container is all set and running, which may take a few minutes, open up your browser and navigate to www.localhost:3000.

This will open up a “playground” where you can create your query. Copy the example below and see the results:

Query
query{
accounts(first: 3){
  nodes{
    id
  }
  }
}

The above code will query the account entity returning the id. We have defined the id here as the toAddress(also known as the receiving address).

  • You can also query for all the transfers. Copy this given code and see the results:
query
query{
transfers(first: 3){
  nodes{
    id
    amount
    blockNumber
    }
  }
}
  • Note an amazing possibility here. We can even query the account id from within the transfer query. The example below shows that we are querying for transfers where we have an associated amount and blockNumber. After that we can link this to the receiving or to address as follows:
query
query{
  transfers(first: 3){
    nodes{
      id
      amount
      blockNumber
      to{
        id
      }
      }
    }
  }

Let's have a look at the database schema and understand the working.

The accounts table is a standalone table which contains only receiving addresses(accounts.id). The transfer table contains to_id which are links or points back to the accounts.

Simply put, one account links to many transfers. In other words, each unique Polkadot address, stored in accounts.id, links to one or more than one Polkadot address, which has an associated amount and block number.

References

Lesson 2: Many to Many Entities


Exercise - Council Proposals (Many-to-Many)

Here, we will take the starter project and focus on learning how many-to-many entity relationships work. We will create a project which allows us to query for the number of votes that councillors have made and how many votes a given proposal has received.

Refer to this pageopen in new window to learn more about the Polkadot governance structure.

Pre-Requisites

Completion of Module 2.

Overview of Steps Involved

  1. Initialise the starter project.
  2. Update your mappings, manifest file, and graphql schema file by removing all the default code except for the handleEvent function.
  3. Generate, build, and deploy your code.
  4. Deploy your code in Docker.
  5. Query for address balances in the playground.

Detailed Steps

Step 1: Initialise Your Project

First of all, create a SubQuery project with the following command:

~/Code/subQuery$ subql init
Project name [subql-starter]: council-proposal
? Select a network Polkadot
? Select a template project subql-starter     Starter project for subquery
Cloning project... done
RPC endpoint: [wss://polkadot.api.onfinality.io/public-ws]:
Git repository [https://github.com/subquery/subql-starter]:
Fetching network genesis hash... done
Author [Ian He & Jay Ji]:
Description [This project can be use as a starting po...]:
Version [0.0.4]:
License [MIT]:
Preparing project... done
council-proposal is ready

Step 2: Update the Graphql Schema

Let’s first create an entity called Proposals. This proposal is an event of type council. To simply put, you will extract data from the council event. Visit this pageopen in new window for more information.

Focus on the proposed method within the council event. The proposed method is defined as:

“A motion (given hash) has been proposed (by given account) with a threshold (given MemberCount). [account, proposal_index, proposal_hash, threshold]” - sourceopen in new window

Hence, you need to add the following fields: id, index, hash, voteThreshold and block to your entity.

id => account
index => proposal_index
hash => proposal_hash
voteThreshold => threshold
block => Not a part of proposed method but useful to extract

Next, let’s create an entity object called Councillor. This object will hold the number of votes each councillor has made.

Finally, let’s create a VoteHistory entity. This will be another council eventopen in new window using the votedopen in new window method defined as:

“A motion (given hash) has been voted on by a given account, leaving a tally ("yes votes" and "no votes" respectively given as MemberCount). [account, proposal_hash, voted, yes, no]”- sourceopen in new window

You need to add the following fields: id, proposalHash, approvedVote, councillor, votedYes, votedNo, and block to your entity.

id => account
proposalHash => Proposal
approvedVote => voted
Councillor => Councillor
votedYes => yes
votedNo => no
block => Not a part of proposed method but useful to extract

Here, we have specified the type as the proposal entityNote for proposalHash. We have also introduced a new field called Councillor and allocated it the Councillor type. This has created a table where these two columns work as references to their respective tables.

This indicates that the VoteHistory entity or VoteHistory database table can link the Councillor entity to the Proposal entity. And that creates a many to many relationship. In simple terms, councillor can vote for many proposals and a proposal will have many votes.

  • The schema file should look like this:
type Proposal @entity {
  id: ID!
  index: String!
  account: String
  hash: String
  voteThreshold: String
  block: BigInt
}

type VoteHistory @entity {
  id: ID!
  proposalHash: Proposal
  approvedVote: Boolean!
  councillor: Councillor
  votedYes: Int
  votedNo: Int
  block: Int
}

type Councillor @entity {
  id: ID!
  numberOfVotes: Int
}

Step 3: Update the Manifest File (aka project.yaml)

Update the manifest file to include two Event handlers. Also update the filter method to council/Proposed and council/Voted.

  • The updated part of the project.yaml file should look simiar to as below:
dataSources:
  - kind: substrate/Runtime
    startBlock: 1
    mapping:
      file: ./dist/index.js
      handlers:
        - handler: handleCouncilProposedEvent
          kind: substrate/EventHandler
          filter:
            module: council
            method: Proposed
        - handler: handleCouncilVotedEvent
          kind: substrate/EventHandler
          filter:
            module: council
            method: Voted

Step 4: Update the Mappings File

This mapping file will contain three functions: handleCouncilProposedEvent, handleCouncilVotedEvent, and ensureCouncillor.

Let’s have a look at the first function handleCouncilProposedEvent.

4.1 handleCouncilProposedEvent

You can access the values of the event with the following code:

  const {
    event: {
      data: [accountId, proposal_index, proposal_hash, threshold],
    },
  } = event;
  • Then instantiate a new Proposal object:
  const proposal = new Proposal(proposal_hash.toString());
  • After that assign each of the events to a variable in the Proposal object and save it.
  proposal.index = proposal_index.toString();
  proposal.account = accountId.toString();
  proposal.hash = proposal_hash.toString();
  proposal.voteThreshold = threshold.toString();
  proposal.block = event.block.block.header.number.toBigInt();
  await proposal.save();
4.2 handleCouncilVotedEvent

This function follows a similar format to handleCouncilProposedEvent. The event parameters are first obtained as below:

const {
    event: {
      data: [councilorId, proposal_hash, approved_vote, numberYes, numberNo],
    },
  } = event;

However, before storing the values into the voteHistory object, a helper function is used to check if the councillorId already exists.

  await ensureCouncillor(councilorId.toString());
  // Retrieve the record by the accountID
  const voteHistory = new VoteHistory(
    `${event.block.block.header.number.toNumber()}-${event.idx}`
  );
4.3 ensureCouncillor (helper function)

This helper function checks if the councillor entity exists. If it does NOT exist, a new one is created and the number of votes is set to zero. Otherwise, the number of votes is incremented by one.

async function ensureCouncillor(accountId: string): Promise<void> {
  // ensure that our account entities exist
  let councillor = await Councillor.get(accountId);
  if (!councillor) {
    councillor = new Councillor(accountId);
    councillor.numberOfVotes = 0;
  }
  councillor.numberOfVotes += 1;
  await councillor.save();
  • The complete mapping file looks similar to as follows:
import { SubstrateEvent } from "@subql/types";
import { bool, Int } from "@polkadot/types";
import { Proposal, VoteHistory, Councillor } from "../types/models";

export async function handleCouncilProposedEvent(event: SubstrateEvent): Promise<void> {
  const {
    event: {
      data: [accountId, proposal_index, proposal_hash, threshold],
    },
  } = event;
  const proposal = new Proposal(proposal_hash.toString());
  proposal.index = proposal_index.toString();
  proposal.account = accountId.toString();
  proposal.hash = proposal_hash.toString();
  proposal.voteThreshold = threshold.toString();
  proposal.block = event.block.block.header.number.toBigInt();
  await proposal.save();
}

export async function handleCouncilVotedEvent(event: SubstrateEvent): Promise<void> {
  // logger.info(JSON.stringify(event.event.data));
  const {
    event: {
      data: [councilorId, proposal_hash, approved_vote, numberYes, numberNo],
    },
  } = event;

  await ensureCouncillor(councilorId.toString());
  // Retrieve the record by the accountID
  const voteHistory = new VoteHistory(
    `${event.block.block.header.number.toNumber()}-${event.idx}`
  );
  voteHistory.proposalHashId = proposal_hash.toString();
  voteHistory.approvedVote = (approved_vote as bool).valueOf();
  voteHistory.councillorId = councilorId.toString();
  voteHistory.votedYes = (numberYes as Int).toNumber();
  voteHistory.votedNo = (numberNo as Int).toNumber();
  voteHistory.block = event.block.block.header.number.toNumber();
  // logger.info(JSON.stringify(voteHistory));
  await voteHistory.save();
}

async function ensureCouncillor(accountId: string): Promise<void> {
  // ensure that our account entities exist
  let councillor = await Councillor.get(accountId);
  if (!councillor) {
    councillor = new Councillor(accountId);
    councillor.numberOfVotes = 0;
  }
  councillor.numberOfVotes += 1;
  await councillor.save();
}

Step 5: Install the Dependencies

Install the node dependencies by running the following command:

yarn
yarn install

Step 6: Generate the Associated Typescript

Next, generate the associated typescript using the following command:

yarn
yarn codegen

Step 7: Build the Project

Now, let's build the project with the following command:

yarn
yarn build

This code bundles the app into static files for production.

Step 8: Start the Docker Container

Run the docker command to pull the images and start the container.

docker-compose pull && docker-compose up

Step 9: Run a Query

Once the docker container is up to date and running successfully, which may take a few minutes, open up your browser and navigate to localhost:3000.

This will open up a playground where you can create your query. Copy the example below and have a look at the results:

Query
query {
    councillors (first: 3 orderBy: NUMBER_OF_VOTES_DESC) {
        nodes {
            id
            numberOfVotes
            voteHistories (first: 5) {
              totalCount
              nodes {
                approvedVote
              }
          }
        }
    }
}

The above code will query the councillors. It will return the number of votes, the totalCount, and the number of approved votes for each councillor.

Bonus

Including a Reverse Lookup on the schema file will allow you to customise the fields on which you can query.

type Proposal @entity {
  id: ID!
  index: String!
  account: String
  hash: String
  voteThreshold: String
  block: BigInt
  voteHistory_p: [VoteHistory] @derivedFrom(field: "proposalHash")
}

type VoteHistory @entity {
  id: ID!
  proposalHash: Proposal
  approvedVote: Boolean!
  councillor: Councillor
  votedYes: Int
  votedNo: Int
  block: Int
}

type Councillor @entity {
  id: ID!
  numberOfVotes: Int
  voteHistory_c: [VoteHistory] @derivedFrom(field: "councillor")
}

Here, by adding voteHistory_p and voteHistory_b, voteHistories becomes voteHistory_c.

References

Lesson 3: Reverse Lookups


Exercise - Account Transfer (With Reverse Lookup)

In this exercise, we will take the starter project and learn about the reverse lookups.

Pre-Requisites

Completion of Module 3: Lesson 1 - One to many entities.

Overview of Steps Involved

  1. Git clone the tutorials-account-transfersopen in new window project.
  2. Run it to ensure it's working fine.
  3. Add a new field in the schema and make it a reverse lookup.
  4. Requery the project with the new “virtual” field as a reverse lookup.

Detailed Steps

Step 1: Clone Account Transfer Project

Start by cloning the tutorials-account-transfers Github repository.

Note

This github project was a part of the exercise for Module 3 - Lesson 2 (See Reference in the end of the Lesson 2).

Run the following command:

git clone https://github.com/subquery/tutorials-account-transfers.git

Step 2: Confirm that Project Works

Run the basic commands to run the project and check if it's all set.

yarn install
yarn codegen
yarn build
docker-compose pull && docker-compose up

Once the docker container is running, which may take a few minutes, open up your browser and navigate to www.localhost:3000.

This will open up a playground where you can create your query. Copy the example below and see the result:

Query
query{
  accounts(first: 3){
    nodes{
      id
    }
    }
  }

The above code will query the account entity returning the id. Here. we have defined the id as the toAddress(also known as the receiving address).

  • As noted in a previous exercise(Lesson 1), we query the account id from within the transfer entity.

The example given below shows that we are querying for transfers where we have an associated amount and blockNumber. After that, we can link this to the receiving or to address as follows:

query
query{
  transfers(first: 3){
    nodes{
      id
      amount
      blockNumber
      to{
        id
      }
      }
    }
  }

Step 3: Add a Reverse Lookup

Add an extra field to the Account entity called myToAddress. Assign it the type Transfer, and add the @derived annotation.

This will create a virtual field called myToAddress, which can be accessed from the Account entity. Note that it is virtual because the database table structure does not change.

  • Allows you to do a reverse lookup in Graphql.
  • Adds a GetElementByID() on the child entities.
type Account @entity {
  id: ID! #this primary key is set as the toAddress
  myToAddress: [Transfer] @derivedFrom(field:"to")
}

type Transfer @entity {
  id: ID! #this primary key is the block number + the event id
  amount: BigInt
  blockNumber: BigInt
  to: Account! #receiving address
}

Step 4: Recompile and Test

Query
query{
  accounts(first:5){
    nodes{
      id
      myToAddress{
        nodes{
          id
          amount
        }
      }
    }
  }
}

Adding the @derivedFrom keyword to the myToAddress field allows a virtual field to appear in the Account object. You can see this in the documentation tab. This allows a Reverse Lookup where the Transfer.to field can be accessed from Account.myToAddress.

References