Now that we've seen all of these different types of relationships, we've created these relationships.
We've done a whole bunch of work around how to join the tables, how to set up the relationships, primary
keys, foreign keys.
Now it's time to do a little bit of struggling and actually apply all of that knowledge into a real
exercise where we have to think and where we have to come up with the results by ourselves, which is
the part where we convert information into knowledge.
So something that I'm telling you into, something that you know yourself.
Time for some struggle.
Continue into the family travel tracker dot zip file and take a look inside the files.
We have some of the files that we had from before, such as a lot of the code that we had left off with
at the end of the travel tracker project.
But this new travel tracker that we're going to create is now a family travel tracker.
So this is what we're going to be building a upgrade to the travel tracker.
It's now a family travel tracker.
So let's say in your family you have four members you, your husband, your son, your daughter, etcetera.
And you want to track everybody's travels across the world.
Well, you now need to establish relationships between your tables.
And this is what we can end up with if we apply all our new knowledge.
I can click on the Angela tab.
It shows me all the places that Angela has been.
I can click on the next person's tab and it will show me the places the other person has been, and
depending on which tab I am in, I can add different countries.
So let's say that Jack has now been to Canada and I click add.
Then that populates the Jack tab and his countries.
So he's been to a total of three countries now.
But if I go back to Angela you can see that it's unchanged for Angela.
And she has been to four countries now.
We also have this additional page add a family member.
So when you click on that tab we can add a new member.
Let's create somebody.
Let's say we have Amy in our family as well.
So I can pick a color for her let's say red click add.
And we now have Amy in our list of family members.
Let's say that Amy recently went to Greenland.
So let's add that country and you can see the total countries works.
The colors work.
It's completely unique to Amy and it doesn't affect any of our other users.
This is the goal that we're trying to achieve.
Now this is not an easy challenge by any stretch of the imagination.
So I have added some things to make it a little bit easier.
For example, in the index.js under the views folder, I've actually created all of the existing code
that you need for everything to work.
So as long as you pass in something called users that contains all of these users as well as new users
that you update to this array, then this should work.
If you stop the previous process and use Nodemon to run index.js, which is your starting file, you'll
see that it already works with two new members.
Now, unfortunately, not a lot of the other parts work.
So for example, when I click on this button, which as you can see here is going to hit up the root
forward slash user with the value of new under the name of add.
Then we don't actually handle that yet in our index.js.
And that is of course for you to do.
And the other parts which are incomplete is the new root.
So when we create a new user in this new JS, it's going to hit up the new root, make a Post request
and add various things such as the name and also the color with the color that corresponds to a CSS
color as the value.
And hopefully you'll be able to handle that in this route and also make some adjustments as needed to
the other routes such as the forward slash get, etcetera.
Just one thing to note in the syntax highlighting for this JS file.
The index.js.
For some reason, in my case it keeps on highlighting this as an error and you'll see the file.
The whole thing is red.
Don't worry, this is actually perfectly functioning code.
I think the syntax highlighting is just getting a little bit confused between the angle brackets inserted
into the middle of a string, it really prefers it when you have the double quotes either side of these
brackets.
But rest assured, everything inside index.js is perfectly functioning and working.
Despite all of these errors and warnings, it's just a syntax highlighting thing.
Don't worry about it.
Ignore it.
You don't need to touch anything inside index.js other than look at it to know what are the routes that
we're hitting up, what are the names?
What are the values so that you know how to handle it?
Now, before you get started attempting this exercise, remember what we learned about relationships
in Postgres and have a think about how you need to change the tables and add the relationships as necessary.
So if we think about it, we'll need a users table that sets up each user with their name and the color
that they're going to be represented on the map.
And these colors should represent CSS colors.
So good examples are teal, powder blue, red, blue, green, yellow.
And it should probably have a primary key that can be linked to the visited countries table.
Now the visited countries table probably needs to be updated or deleted and recreated so that we have
some sort of way of establishing a relationship between the countries that have been visited and the
user who did the visiting.
Have a think about how you might create those tables from scratch if you need to delete any of the tables,
because altering it is complicated, you can always right click and go to delete.
If you really get stuck.
Remember you can go into the queries SQL and if you scroll down, you'll see the SQL solution for the
exercise.
And you can copy the entire line of code in order to set up the database to have it the same way as
what I've got here.
So I've got all the sample data and the relationships and the tables recreated for you.
Have a think about giving it a go.
Maybe you could delete the table, recreate it.
Try some of the SQL commands you've learnt.
If you get stuck then come back and we'll go through the solution together.
It's a difficult challenge, but hopefully by running the solution, you'll see what it is that you
need to achieve.
The whole functionality and try to figure out how to use what you've learned about relationships, about
Postgres, and using Node and Express to complete this project.
I'll leave you to it.
Aim to spend about 1 or 2 hours on this and then come back afterwards.
If you get stuck and we'll go through the solution together.
All right, let's go through the solution together.
Now, one of the first things we have to think about is to establish the relationship between our tables
of users and visited countries, and in this case, what we have is a one to many relationship, because
there is one user who can visit many countries.
And in order to establish this relationship, we can either alter the visited countries table to add
a foreign key, or we can simply drop our previous two tables, and you can also delete them in Pgadmin
as well.
You can simply right click on a table and also ask it to be deleted.
Now the challenge is do you remember how to establish a one to many relationship?
And the way we do it is by creating a foreign key that points from the many side of the one to many
relationship over to the primary key on the one of the one to many relationship.
So this is the one and this is the many.
In this way, we're creating a relationship where one user can have many, many visited countries.
And this foreign key I've called user underscore id.
It's an integer because the primary key for the user id is also an integer.
References is what sets up this particular field as the foreign key.
And I've pointed it towards the table of users and the property that is ID which is the primary key
here.
So we've seen this previously when we were working with simpler examples.
Now we're applying this to our actual tables in the Family Travel tracker.
If you didn't manage to get this far, or you forgot to do this or struggled with it, then feel free
to go into the queries SQL and copy all of these lines, and you can paste it into Pgadmin going into
the query tool on your world database, making sure that you have it changed from your previous school
database.
And now you can simply just right click on the database you want and then go into the query tool for
that database.
Just check the connection there and then we can paste all of that in.
And hopefully if I've got all the semicolons in the right places, you can simply just hit run.
And the last command after I've created the two tables, added the relationship, inserted some data,
so two users and some countries for each of the users.
Then I've made a select all from visited countries joining the users where the users tables ID matches
that foreign key user ID, and we end up with this table that shows us the join.
So this is our visited countries table and this is our users table.
Each user has been to many places.
So we see the users repeating here.
So Angela's been to France and Great Britain and Jack has been to Canada and France.
And this table is created using the match between the user ID on the visited countries table and the
ID in the users table.
Now we can get hold of, for example, all the countries that a particular user has been to, or what
is the color of that user to be represented on our map.
And essentially we haven't really changed that much other than taking into account the relationships.
Hopefully if everything went well for you, then you've got your tables set up with the correct relationship,
the one to many relationship, and you've got some sample values in there that doesn't have to match
what I've got here.
Now, once we've got our database set up, the next step is to look at how we can modify our index.js
in order to add that multi-user functionality.
There are quite a lot of changes between the index.js and solution JS code files.
And I want to show you a cool way where in VS code you can compare code.
Right click on index.js and select for compare this option, and then right click on the solution dot
js and select the other one which is compare with selected.
So now what's going to happen is we're going to have a split screen.
Where on the left we've got the index.js.
On the right we've got the solution dot JS.
And the code has very cleverly figured out which lines of code are subtracted or modified, and which
lines of code are added compared to the other file.
And this is handy because it allows us to compare the index versus the solution.
And I can show you exactly what was changed in between.
So the first thing that was changed is our check visited function.
Now check visited gets called the first time the user tries to hit up the home root.
So it's in our app.get when they hit up localhost 3000 or the home root of your website.
And this is where we need to get hold of all the countries that a user has visited now previously,
it was easy enough because we could simply select all of the country codes from our table of visited
countries, because visited countries represented only one user, so it would contain the two letter
country codes of all the places that the single user has been to.
Now, because we have multiple users and we've updated the way that our visited countries works where
we have that one to many relationship with the users table, we now need to change the code a little
bit in order to fully take advantage of our more advanced database situation.
So let me walk you through the code here.
What's going on?
This part of our DB dot query has been completely changed.
The previous part that we had is just this, where we had select country code from visited countries,
and the rest of this query is all new.
So what does it do?
Well, this we have already seen in the previous lesson we.
Which allows us to join two tables based on some sort of criteria.
And because our two tables, the user table and the visited countries table, have this many to one
relationship, we can use this criteria where the users table.id that's the primary key.
Remember of the users table.
When that value matches the user ID of the other table, the visited countries table.
This is the foreign key and that is the criteria that we want to join the users table with our visited
countries.
If this is confusing, I recommend simply selecting it and then running it in Pgadmin, and you'll see
a visual representation of what kind of data you get back.
In this case.
What's happened is we've joined the visited countries with the users table.
We've matched up the user ID on the users table with the user ID on the visited countries table, and
we've created a new table where we are just selecting on that field of country code.
Essentially what we have here now is a new table that contains all of the countries that all of our
users have been to.
So we need to narrow that down a little bit more.
And that's where our query comes in.
So using the where keyword we're checking to filter the results to only include where the user ID is
equal to the first option that we're passing in right here using dollar one.
And that first option is the current user ID.
By default I've set the current user ID to be one, and that is because when a user first lands on our
home page, what I want to show them is the first user's countries that they've visited.
So we start from the first tab essentially.
Now if we take this other option and add it into our queries here.
So select country code from visited countries joining the users table on this particular criteria.
And then we add our final criteria.
And I change this to the current value which is set to default as one.
And I run that.
You can see we now have the country codes of only the first user.
And you can see this also if I expand this table a little bit change it to select all.
And you can see we're now only selecting on the cases where the user ID is equal to one.
That's what that is going to select on.
But we're simply narrowing it down only to the country code, which is really the only part of the data
that we're interested in from this database.
Once we get those countries back, then it gets populated into this constant called countries, which
is now a list of two letter codes.
And just as before we pass it over to index.js, we pass over the countries, the countries dot length,
which if this represents all the countries the user's been to, the number of items in there will represent
the total number of countries a user has been to, which gets displayed down here.
The only part that changes a little bit is we also need to pass over the color that represents that
user's countries.
So for example Angela is represented by teal here.
And Jack has this sort of powder blue.
Now where do we get that color from.
Well we've updated our users table to contain a color that's associated with that user.
So the user has an ID, a name and a color.
And we want to use that data to update this list that we've got here.
Now I've created this list of users just to be able to visually show you how we want this list to be
structured.
We want to have all of the properties that's in that table ID name and color, and we're going to use
it to pass it over to the index.js rendered file.
And we do it right here.
This is new compared to previously because previously we only had one user and we didn't need to pass
over this list.
But now we do because we want to represent all the users up here in the tab so that we can select them
on the website.
In addition, we need the color that represents that user so that we can change the color of the button.
And also the country's displayed.
So where do we get all of that information.
Well this is where the second change happens.
This new function compared to before I called it get current user.
But of course you can call it anything you want, but essentially it makes a very simple query to the
database.
Select everything from the users table.
And once we get hold of those results, we put it into the users list right here.
And of course, as you can see, I could probably have this as empty.
And it wouldn't really change very much because I would just populate it from right here.
But the reason why I do have it included is so that we can visualize the structure of our data more
easily, where it's a list of dictionaries with these three properties, and that helps us plan for
how we want to use that list.
So always make things easier for yourself by visualizing it and seeing it, rather than holding it in
your head or imagining it.
That's where bugs start to creep in.
Now that we've populated that users list, updated it with what's stored in the database, we can find
the current user from that list of users by checking for where the user's ID matches the current user
ID.
Notice how right now, the current ID is set to user number one?
And what we're trying to do is we're trying to go through the list of users and find the record where
the ID here matches that current user's ID, and then we pull out that entire record right here and
set it as a current user.
And we do that right here.
Get current user, set it to this current user, and then we can get hold of the color that is associated
with that current user.
Now this line of code is quite short and concise, but if you're not familiar with how JavaScript does
map filter reduce or in this case, the find function, then you could have also created just a simple
for loop that loops through the array of users, and then checks each of the IDs of each of the users
against the current user ID.
When we do have a match, then we pull out that particular user and we return it as the output of this
function.
Now, one important thing to note here.
Notice how I've got two equal signs here.
Instead of three equal signs.
Three equal signs is not going to work as well for you, because then it means that the data types of
the left hand side must match the right hand side.
In most scenarios, this user ID will be a number and the current user ID will be a number.
But there are cases where if we're getting the data passed in through our form, it can come in as a
string and you'll see this fail.
So if you have some bugs, see if this might be the issue.
I suspect it might be a problem for a lot of you guys.
And if in doubt, as always, you can always use the console logs to check data types.
So if you were debugging, you might do a console log and then do a typeof.
And then inside the curly brackets you put the left hand side that you're checking and put it right
here.
And then on the next line you can console, log and type check the other one that you're trying to match.
If these two are of different data types, say string and number, then this match is not going to work
with three equal signs.
This is a precise equal, whereas the two equal signs just checks for whether if the values are equal.
Ignoring the data types, this can get you out of trouble with this particular issue here.
Now, once we've gotten the current user and we pass over the color, the next part we need to think
about is where all of this data lands and it goes into the index.js.
So if we open up that file inside the views folder, we can see how we're using the data that gets passed
in.
So for example we have our tabs at the very top of the website.
And what I'm doing here is looping through each of the users that gets passed in.
So that array of users, and we populate different parts of the tab using information from that particular
list of dictionaries.
So remember, users is passed over as a list of dictionaries.
And the dictionary contains ID, it contains the name and it contains the color.
And then we basically just have multiple of these entries for each of the users that we have.
So that is our list.
In this loop we're looping through each of these items and we are populating different parts of the
tab.
For example, we're putting the user ID into the value of the input so that when we submit this form
using a Post request, we'll be able to catch the request and see the value in the name set to whatever
the ID is chosen by the user.
The other part that we're using is the user color property to set the background color of these tabs.
So this one is powder blue and this one is teal.
We're also using the user dot name in order to put the name of that user into the labels of these tabs,
so that the user can see which one they're selecting on.
Finally, this entire form gets submitted whenever the user chooses one of these inputs.
We'll have as many of these inputs as there are users.
And then finally there is an extra one that goes to a different page.
So let's follow the action here and go to the forward slash users route back in our index.js or solution
dot JS.
And you can see it hits up this route right here inside the post.
So we have an app dot post that hits up the forward slash users route.
And what we're doing is we are setting the current user ID to be equal to whichever tab was selected.
So remember this request dot body uses body parser and gets hold of whatever value is sent over from
the form in the user.
And this is set to the user id.
So it could be one or 2 or 3.
And then we update our current user ID up here to the value that we're selected.
So we're reflecting on which user is being selected to show the countries that they've been to.
On the other part of the if and else statement, you'll see that if the request dot body dot add.
So instead of tapping into user, we're tapping into something that has an input with a name of add
which is right here.
If that is equal to new, then in that case it means that the user hit this button and instead we should
render this page Nujabes Nujabes was already created for you and it's in the views folder.
And it's a very simple form.
It hits up a route called forward Slash New and it asks the user to type in their name.
So for example, Amy and then to pick a color.
And then once we click the add button, then that's going to submit the form.
And it's going to hit up this route, making a Post request that contains the name that the user typed
in, and also the radio button containing the color that they selected.
So the value could be any of these colors.
And we can tap into it using the name color.
Now these colors correspond to CSS named colors.
And we can store that in the database to represent that user.
Going back over here let's see where this ends up.
So forward slash new is right here.
So this is yet another new post request that we have in our solution.
When this route is hit up we can get hold of the name of the user.
So that was into the field where they entered the name right here.
And we can also get hold of the request dot body dot color which comes from those radio buttons right
here.
So whichever one they picked, then we have enough data to start creating a new entry to add to our
user table.
I'm writing here insert into users table, and the fields I want to populate is the name and color.
So the name and color fields and the values I'm passing in is firstly the name that comes from right
here, and secondly the color that also comes from right here.
So from the body of our form.
And then finally I have this thing called returning everything.
So what is this returning asterisks do.
Well let's try it out in pgadmin and see what we get as the result.
If we create a new query and we say insert into users a name and a color.
So let's change this to Amy and the color.
Let's change it to red.
Now if I go ahead and add the returning keyword and add an asterisk to say, return everything after
I hit run, you see, what happens is it returns the new record that was added from that insert.
And this is what we need in order to get hold of the current user ID.
Once we have the current user ID, then we can assign it to that value that we have up here.
And once we've changed that so now it's become three for example.
Then the next step in this is to redirect to the home route which remember uses that current user ID
to get all of the countries that that user has been to.
Not only does check visited uses current user ID, but get current user also uses current user id and
it fetches the latest and most up to date information on that particular user that we just added.
And what this allows us to do is to create a new user and give them a color, click add, and then we
can see them in the list of users and we can start adding countries for them.
That brings us to the final part of our code that required changing, which is our post request to the
route forward slash add.
Pretty much all of the behavior inside the index.js is the same as before for our add button.
We hit up the add route whenever a user types a new inputs with the country that they've been to, and
then once they hit submit, it submits to this route right here.
The only parts that have changed is instead of simply just adding to the table where we just do an insert
into visited countries, the new country code that we found this time we have to insert it along with
the user's ID, because there are now multiple users.
The first thing we do is we get hold of the current user, reusing that function up here, and this
will give us the user's ID, the user color, etcetera, the user's name.
And then we change our insert into visited countries by passing in in addition to the country code,
but also a user ID, and that is the current user ID from right here.
So if the current user is number three, then that's what's going to be inserted into the visited countries
table.
And it's going to populate this part right here with the user ID.
So now we have a country code matched to the user who entered it.
And once all of this is done, then we can go ahead and redirect back to the home page showing the updated
country that was added.
Because remember, the home route actually checks for all of the latest countries and bases it on the
current user.
There you have it.
You now have a website where you can add new family members.
You can select a color for them and add them to our list of family members.
And we can view the countries that each of the members of our family have been to, and add any new
countries they've been to, just unique to that person.
And we're able to do this using the power of relationships in PostgreSQL.
So hopefully this is interesting.
And I know it was a really, really tough challenge.
And there is zero shame in looking at the solutions.
If it helps you to understand what's going on, and you're able to then take what you've learned from
the solutions and go back and fix your code to make it work the way it's supposed to.
There you have it.
You've built a family travel tracker, and if you have shared your family travel tracker with your own
family and you have some crazy stories, then do share it in the Q&A.
I'd love to hear how your own products are being used out in the world.
Hope you had fun with me in this lesson.
I look forward to seeing you in the next.