hello everyone and welcome to another subquery video
where we're going to talk about one to many entity relationships stick around to find out more
so let's go into the documentation link here and we'll go into the create a project link on the
left-hand side and under graphql schema you'll see that here we define the shape of our data
in a file called schema.graphql and in the past we've looked at very simple
objects or entities such as this one here where we've got the likes of id name
and address however as you start building more and more complicated projects you'll find that
you'll need to use the likes of one-to-one relationships one-to-many or many-to-many
here we're going to focus on the one-to-many relationship because this is very popular the
example here you can see we've got one person that can have multiple accounts so this is the one to
many relationship, first of all, i'm going to set up a project using the standard starter technique
okay now that we've got this set up let's go ahead and open our schema.graphql
file i want to delete this and create my own i'm going to copy this from a workbook just to
save some time so i'm not typing all of this out and i'll explain what's happening here
first of all we're defining an object or an entity called account and this account has the
mandatory id field which is actually the primary key of the database table that will be created
and it's set as the to address the other object is called transfer and in here we are recording not
only the id but the amount the block number and also this to address this is a receiving address
which is actually of type account so what this means is that in this database table effectively
there's going to be a column which is going to point back or refer back to the account id
so this is where your single account can have many transfers and again if you're still not sure what
this means when i show you the database table that gets created it'll help make things clearer
the next step is to look at the manifest file and make some modifications
now we're going to just use an event handler which means we can delete the call and the block handler
and we're not going to filter on balances deposit but it's going to be balances transfer
and this is something that you'll learn over time next let's jump into our mapping handler
this is really the engine room or the code that's going to do
all the hard work let's go ahead and delete the code that we do not need like so
and what I'll do is I'm going to copy the code in again just to save time and I'll explain how
this works now we don't have our starter entity anymore instead we've got accounts and transfer
but to make this available we need to, first of all, build the object so let's go yarn code gen
this will create the associated typescripts that you would have seen many times before the account
and transfer now I can refer to this up here and import them in and the key thing here is to know
that balances.transfer has the following payload of from to and value as an array this means that
we can access these values using event.event.data and then the position in the array
we're going to ignore the from address just for now and just look at to address and the amount
next up we need to instantiate a new account object that you see here however unlike defining
the transfer object in this case we need to check if this value is in the database first of all
this code here attempts to grab the to address from the database and if
nothing is found or if it's null we can then save it to the database
the reason for this is that one account can have many transfers and inside those transfers,
you could have duplicate keys and if you don't do this you'll end up with an error
saying that the primary key is not unique and again you'll understand this a bit more
when you see the database structure that gets created down below for the transfer object what
we're doing is again in the argument here we are defining a primary key that is comprised of the
block number dash and then event dot idx idx is an integer that is unique and represents a specific
event for block number for the 2id and amount we extract it as we've seen before
and then we save everything to the database so with this let's go ahead and build the code
and what I'm going to do now is run this on my local node instance
first of all I need to specify a database name so I'll say
now what will happen here is I've got an error saying that the database does not exist and
that's good because I'm doing a cross-check now I'll open up my database here and I'll create one
while this node is running in the background let's go into our database schema where you'll
see I've got public being created and also subquery dash one the node has started so
if I drill into this and look at my tables you'll see I've got accounts and transfers
this aligns with my schema file and you can see it's already fetching blocks so if I go
into my accounts table and look at the columns I've got an id column which is this one here
and if I go down to my transfers
I've got id amount block number and two ids which relate to the fields we've got in our transfer
entity now the key here is because we're already fetching blocks i can view this table
and this is the result so hopefully, this should put a lot more context into the data that's coming
back and how this all works because you can see the primary key is actually the block number
one nine seven six eight one dash four and four is the event number I've got the actual amount
and this field here two ids is actually the unique Polkadot cryptocurrency address
that is being stored and referenced to in the accounts table so there are many of these two ids
in this particular table but in the accounts table if we view this
the two ids here is unique and this is an example of one too many
now as this is synchronizing what I'm going to do is open up another tab here and I want to
run the graphql playground or the front end so let me do some pre-setup
now this is the query engine that I want to run and I can pass it a name
the name is actually the name of the project which I can grab here
now what will happen is I'm going to get an error and that is because my port 3000 is already in use
so let me export a different port number and you may well also encounter this error
let's rerun this and now we should see the query engine up and running let's now go into a browser
okay let's now make an example query
we'll start off by going to query and then i'll query for accounts
and then I'll query for nodes
and i'll say return all the ids back to me i hit run and now you can see i've got from the accounts
table all the ids showing now there's quite a few of them so let me just increase the speed
of this by saying first five say i'll run that again and there you have it
now i can change this to transfers so it's very similar to a select query if you're used to sql
where i'm saying give me all the data in my transfer table and just show me the id
so this is like select star from transfers or select id from transfers
what i can also do is choose to show the amount and also the block number as well
now this is a very simple query but the key is around the relationship that we talked about
because this is indeed one too many what this means is that from my transfer i can actually
go ahead and discover or link or do a join to the accounts table or the accounts object and
i can do this by saying also give me two because this was available you may recall in the schema
but if i run this you'll see that i get an error because my two field has a type of account
so what this means is i actually need to go and specify the actual id
if I hit run you can see here now for these transfers I've got an associated id which is
actually the to address or the receiving address so to read this we've got this amount of dot
at this block number sent to this address so there you have it a very simple example
showing a one-to-many entity relationship for account transfers catch you on the next video
you