hey everyone welcome to another sub query video where i'm going to talk about
many to many relationships want to learn more stick around and find out
now as always let's jump into the documentation website on the left hand side jumping into create
a project and a graphql schema now on the right hand side here we've looked at the
likes of one-to-many relationships already and reverse lookups let's jump to many too many
and understand what this is all about so first of all this can be achieved by implementing a
mapping entity to connect to other entities in the example down below you can see we've got a person
entity or an object or an actual fact that's you can think of it as a table in sql or postgres
and then we've got the group entity down below as well and this middle entity you can think of
is the one that connects the two other entities together so for example if you've got the person
entity or object i can get the name of this person
and then i can use the groups which is of type person group to come down to this particular table
because this is the group down here and then i can link or query for persons
which is of type person which comes down here which will then give me the name of the group
so still confused well the best way to understand something new is to jump into
an example so let's do that now in this example here we're going to create a project called
council proposal that's going to be full of voting and it's going to demonstrate a mini to
many relationship project so first of all let's go ahead and initiate a standard subql project
and then we'll call this council proposals
let's now go ahead and open up the graphql schema file and we're going to remove this entity and
create our own one first of all let me paste in the first object here it's called proposal
and we're going to have an id index account hash vote threshold and block now you might be thinking
where did i get these from well the key here is to look at the documentation so let's jump in
and i'm going to jump straight to the events documentation here and it's under counsel
that we want to focus our attention on and under propose you can see here
that i've got some fields that i want to access such as account proposal index proposal hash
and threshold and it's these values that you'll also see again when we create our mapping file so
just remember this and create this particular entity here the next one that we'll create is
the vote history so again let me copy this in and again very similar here where we've got approved
vote we've got voted yes voted no and this one again going back to the documentation if we go
to the voted method you'll see we've got account proposed hash voted yes and no and the last entity
that we'll create is this one called counselor and this one simply just holds the number of votes
so from here you can see that we've got three entity objects and when i do my query
i can go ahead and ask for the counselor id the number of votes and then there's
an automatic field that gets created allowing me to join to the vote history
then i can query based on my voted yes voted no then i can connect the proposal hash which is
of type proposal to the proposal entity and then again access the likes of vote threshold and block
and of course i can query the other direction from proposal down to vote history down to counselor
so this middle table is like the glue or the join between the two other tables
proposal and counsellor the next thing i want to do as always is to jump into the
manifest file and i want to update this i'm just going to copy the manifest file from the notebook
just to save a bit of time and paste this in what you can see is i've got two functions here
the handle council proposed event which filters on the council module and the proposed method
so this should be familiar now because in the documentation we were looking at the council event
and the proposed method and the other one would be the voted method which you should see down here
okay so let's go now and create our mappings file for my mappings file i'm just going to
delete everything and copy the final code from the workbook and i'll walk you through
this you can see we've got our first function at the top which is of type substrate event
and again these fields here should be familiar because it comes from the documentation
and this is the proposal event data and again understanding of this will help
create the schema file so what this is doing is making the data from the blockchain available so
that we can create or instantiate a new proposal object and then take each one of these parameters
and the associated data and assign it to our proposal object as a variable and then at the end
we save this and in a similar manner down below here we've got the handle council voted event
and this is the payload and down below what we're doing is before we store the information into our
object we're doing a quick little check this await here calls a little helper function down below
and what this does is it double checks if this is in the database if it is not then it goes
ahead and instantiates a new object and sets the number of votes to zero otherwise it just
increments the vote count so now that we have this let's go ahead and build this first
and we'll see this in action let's now try and run this and there are two ways there's the standard
docker compose command that i could run like so but i'm going to run this locally on my sub query
node and the reason why is i want to show you what the database looks like so let's run sub query
node use the f flag and the dot for the current directory that i'm in hit enter and this is my
database here using pg admin and you can see down below my node has started so if i do a refresh
you'll see i've got sub query underscore 15 so this is the 15th time i've tried this and
if i scroll down the tables you can see i've got counselor proposals and vote history so
this is actually what happens under the covers and if we jump into the schema graphql you can
do a comparison so for counselor you can see in my columns i've got id and a number
of votes which corresponds to the fields in my schema file and of course the same for proposal
id index account etc
and of course vote history as well
now with this node started let's go ahead and run the playground now what you'll notice is
that when i do run this locally i'm going to have to run the query engine or query service
but if i run this i'll get an error because i do need to set some parameters
this one here is that the database sean does not exist so i'll have to rearrange that
and then also the port is already in use
and now it should be working
therefore next up let's go ahead and jump into the playground let's go local host
and here we are let's go and check out the schema first of all or the docs actually make this bigger
and what you can see is that i can query for my counselors
go into nodes and then go into the likes of number of votes so let's start off very
simply by looking at let me delete this my counselors nodes and a number of votes
and there we have it so we know that this is working we're getting back the id of the counselor
and the number of votes now what i want to do next is i can actually do a number of things
actually from counsellor i can go into my vote histories and then traverse to
my nodes and then get access to all the fields inside the vote history so to do this let's go
vote histories and the autocomplete is fantastic let's go nodes these are just standard commands
and again in my nose i've got the likes of let's say approved vote and voted yes voted no
i'll run this and there you go so we've got for each councillor the number of votes that the
councillor has made and the boolean on whether the approved vote was true or false and then
voted yes or voted no now again in conjunction with this we can then jump from vote history
and look at proposal hash so remember if we jump back into the code here we're looking at going
from counselor into the vote history object and then linking or jumping across into the proposal
so if we go ahead and inside my vote history go to proposal hash
and then if i run this i should get an error because proposal is looking for subfields
so again if we follow the documentation here let's go proposal hash and then i've got
access to the various fields that we should be used to from our proposal entity or object
and there we go so this is an example of
many-to-many relationships where we're traversing the table or the schema from counselor
to vote histories into proposal hash now the question you might be asking is this is great but
what does it actually mean well we can go into let me run this again go into the likes of sub scan
and let's see if we can do a quick cross check let's copy this out here
and i'll paste it into subscan and you can see we've got 60 extrinsic so let's view them all
and the extrinsics you can see are balance transfer democracy vote what
we are really interested in is the council vote because remember this is from the council
object or the method so that's first of all a filter on council of which we have 11
and then what i want to do is actually include a block number so let's go inside vote history
block hit run and you can see here i'm on block 2.29 million
so across here i've got block 2.29 million so you can see that if i expanded this
we've got an index that approved is true so this is a this is the true here the index i
can also pull out as well i believe it's on the proposal hash so let's go index run this
and there you see i've got index as a 33 this is the 33 here my proposal is 0x03804
and that will be my proposal id so let me pull that out as well
there we go the id there is 038 double four so again this allows us to grab the information
from the actual substrate blockchain around council votes and also in a similar fashion
around council proposal as well so what i also want to note is that in the documentation here
if we look at vote history this is actually an automatic field that's provided to us
because if we want to rename this field we can do so using a reverse lookup and this
was talked about in a previous video or lesson and what i'll do is let me quickly
show you this as well before i do the field down below this very long one is auto generated
and note that it says it's a mini to mini connection so this is an extra field that
is auto generated that we can also make use of which i may explain in a future video
so as a bonus let me go ahead and change this and vote histories that's plural
by jumping into the code again and i'll go ahead and add this particular field in
it's called vote history underscore p for proposal
and this is using the property at derived from which means i'm going to grab this from
proposal hash which is down here and then i'll also add another reverse lookup field
down here called vote history c and this is going to derive its data from counselor which is up here
so let me save this because i have made a modification
to my schema file let me cancel the nodes down below
and i'll have to re generate the code so let's go code agent and let's then re-run this
and what you can see now is in the documentation going through counselors nodes i've got my vote
history underscore c so basically it just renames what was vote histories plural to a name that i
have customized to and this is the neat part about the reverse lookup so you don't actually need it
it's not mandatory but if you want to overwrite the default naming field that's provided to you
this is how you do it so of course there's lots of opportunities to play around with this particular
many-to-many project you can go and traverse this from counsellors to vote histories to
proposal hash you can also do the reverse and there's many more other things you can do
such as aggregate but hopefully this has been an interesting video giving you an insight
into many-to-many relationships and building your knowledge
of creating sub-query projects and your knowledge of substrate as well catch you on the next video
you