9 hours 41 minutes
I welcome back to module five. This is less than 5.5 function. This lesson we're gonna describe implementing a my simple function within our migration files were also going to discuss a couple additional concerns with the PHP are Titian process when we begin implementing additional my sequel objects,
So let's go ahead and begin moving through these bullet points. So we need to create a function that accepts the user i D and return to their grade point average or G p A.
So to implement this function, we're gonna need to utilize the grades table because that's where all our students grades exist.
In addition, we need to respect the composite key. So even though the first bullet point says, except a user, I d return their grade point average. We need to be aware that there's also the concept of different schools that could have users with the same user i d.
And ignoring this would cause multiple students to average together if more than one student had the same I d. So if you had three students at three different schools that happened tohave the same, I d. We would accidentally average all their graves together all those three students and return that we don't want to do that.
So that means we're gonna have an additional
parameter. And that is the school I D. So when this function is implemented, it's gonna accept two parameters the school I d and the user i d
and the additional concerns that we need to be aware of when we begin to implement these additional my sequel objects is that we can no longer use PHP are Titian migrate fresh And that was the reason we cannot use this command is because this command automatically drops all tables and runs the up
function in migration files. Because this is a function dropping all tables will have no effect on it.
So then once it drops on the tables and it gets to the up function call off, this migration file is going to fail because it's going to say that function already exists. And that is true for functions, views, triggers and procedures that he used in these migration files. So we want to switch to migrate, refresh
What this does is it runs all the down functions and then runs the up functions.
So we won't will not run into that problem.
So in the next section of this lesson, we're going to begin writing our query that will return the average great point there. The grade point average of the students based on the school I d and the user, I d. And it isn't. Exercise it. Be a good idea to go ahead and see if you can write that query that would that would use the school I d and the user i d
before looking at that part of the video and seeing if we come up with the same quarry
and once we designed that, we're gonna go ahead and go through the process of implementing it in a migration file.
So let's go ahead and head over to our virtual machine.
All right, let's start reading that quarry. So I'm gonna start with a select statement from his star. I know. I need the grades tables. I'm just gonna do from grades
where school I d equals one and user. I d equals one. I've hard coded. These values were gonna soft code. Um, when we movement to the function and what that means is that they will be dynamically provided parameters.
So let's from the average function.
And I think it's great Score. Sure is.
We go ahead and run this
getting no back. Let's go to the Great State will make sure that makes sense.
So I have this in ascending order. There's no user idea of one, so I'm not going to get anything back. But I have a user idea of to at school I d one.
So let's go ahead and switch the user. I d to two
and I'm gonna run that again.
That makes sense. This user only has one score, so the average of that scored is that score itself
so something we can do? Indeed. Beaver, if you're using D beavers, we can go into the procedures we can right click,
create new procedure.
We select a type of function. I'm called this Get underscore G p. A one hit. Okay,
Nothing saved in the database yet.
I'm just gonna get the basic framework for my function out of this.
More switches to a decimal cause the GPS typically investment
make it a length of eight with a precision of two. So two decimal places
when I put in our return statement wanna put brackets? And then I would have pace my quarry in there.
And I'm just going to rearrange this a little bit.
I put my semicolon at the end here.
I'm going to place in the parameters. So we have school,
Big ant. You want to use the same data types that are on the tables? Otherwise, you can get very ambiguous airs. If the size of manager were to go out of the scope of one of the objects.
So user with an underscore because it's the key word.
returns the decimal eight to Okay, that looks good.
We also need to put a
deterministic keyword here.
Let's go ahead and click. Save. I'm not going to run this from here. I'm just gonna copy the code.
So that looks like that's our code for the function. I'm gonna hit. Cancel. We'll go back to my script. Pain
paste it in there.
I'm gonna go ahead and switch to my terminal. I'm in a ssh session with my vag Great machine. I'm in the right directory, so I'm going to have PHP artist in
make colon migration
get underscore GP a function
when I use a decent naming naming scheme so I can tell what's probably in the file before actually look in it
and it looks like it ran that.
So I'm gonna go ahead and open that file
right Click at it.
I have my up function have my down function.
So what we need to do is we need to make a call to DB Colon Colon, un prepared.
And that means it's just gonna run the
actual sequel statement that creates this.
However, we need to be careful because there are some slight differences
and that we don't need,
um the change of the, uh
the same Michael into the dollar sign or some other symbol to reference a terminating statement.
Some to go ahead and paste this out starting at the end, or I'm gonna copy it first.
You go back to that file,
paste it in there
so I don't need this semi colon at the end here,
and I believe that is correct. So I'll just save it.
And then on the down function, I need to put d d b colon, colon unprepared again
I'm gonna take this call
and put it in there,
and I'm gonna save the file.
I believe this will work. Now, let's go ahead and run the migration.
So I'm gonna do a piece. P art Ishan,
Okay, The air here is I forgot a semicolon.
So I forgot to semicolon right there. And then it got to this symbol and he got confused. So let's go ahead and run that again.
We're using refresh instead of fresh, because fresh will not function correctly when we go to. And Stan, she ate the objects again because it won't run the down part of the It will not run this part.
And that will cause this to get an object already exists air. And it looks like I forgot to do something here. I forgot to use the variables instead. So I need to use school
And be sure to not give these the same name as the column name. Otherwise, my sequel gets confused and is not sure which one you're referencing and it won't give you an air will just return bad data. So let's go in and save that.
Let's go ahead and run the refresh Command again.
So that's gonna run the down part of all your files and then run the up part of all your files,
which is what we want.
And we have that g p a function.
So let's go ahead and refresh
here. It ISS
looks good. Let's give it a test. A good way to test. It's just to select,
and dual. And this is just a key word that means I want to select a record from a non existent table. So if I run this,
I'll get a result back. And what we can do with this if we can say get G p A
11 user, one school, one
run that no back. But we had a
A user i d of two that had a great back. So let's school one user to
that right that I reversed that.
So a school first user
grades, You know, I I have to see the database again.
Let's go ahead and run that cedar.
Okay? It's seated correctly. So now if I refresh I have data. I could go back here and I can run this test
so school one user to doesn't have anything
so have a user idea of one
cool of three. So
switch this to a
or reversed it.
It should be 31
There we go. All right, so it looks like it's working. Now let's go ahead and head over to the summary,
and that brings us to the lesson 5.5 functions summary. So what did we do in this lesson? Recreated a g p. A function that accepted two parameters. The user I d and the school i d. The architecture of the function was an embedded query.
And within our migration file, the function is now automatically created when we run our migrations. However, we need to use my great refresh when we re implement our migrations because we need to ensure that the down function of the files are run. Because my great fresh does not run the down part of the files
it would run into a object already exists air.
So that completes this lesson. And I hope to see you in the next. Thank you
The Microsoft Azure Test is a premium Cybrary assessment created by Interview Mocha. The exam ...
AWS Data Engineering
The AWS Data Engineering test is a premium Cybrary assessment test created by Interview Mocha. ...