Detailed Notes
This is a 1st part of our look at more complex queries and sending parameters into a raw query in Django.
You can view source for today and all prior days by reviewing the tag Day 41 at this GitHub (this one includes day 41 as well): https://github.com/robbroadhead/develpreneur-pythontutorial
Transcript Text
[Music] hello and welcome back we are continuing our uh cleaning up and user experience side of this application so we are uh last time around we added this has four children so in the um roadmaps we're able to see from the top list sort of what what's been filled out and what hasn't and now let's take that down a level and we're gonna actually look at the uh time frames because at this point you have and time frames a little different from roadmap roadmap just has time frames underneath it time frame actually has two things it has if you look it has child time frames potentially and goals for the period uh one other thing i'm gonna do is i'm gonna tweak this um here so that we're not our footer is not hanging over our uh lists but i probably won't get to that this time uh but we will start with uh we're gonna add two things here we're going to have child time frames and then we're going to have tasks because a given time frame could have tasks directly attac or we'll say goals because they could be directly attied to it as we have here so we're gonna go back over this is gonna be more from our queries we're gonna do another raw query so when we list roadmaps we are now going to have uh within this when we do list well there's two things let's start here so this is on our time frame so let's go to our time frame we'll take a look at that real quick so time frame is that a specific one or is that okay so we have two we have a listing of time frames so we have them underneath we'll stick with the one underneath which is the one with an id uh that's actually edit i'm sorry oh yeah that's what we want whoops that's what we want so if we go to our goals and we hit a time frame so this is looking at a specific time frame okay so it's tf1 which is edit time frame and so let's go look at that real quick and we look in here we come in and data is just this time frame objects we're just getting a list of time frames within that so what we need to do is this instead is going to be instead of objects get it's going to be objects.raw and we're still going to have it based on there's going to be an id so we'll take a look at this because we're going to have end up having a parameter and if we look at our prior run here it was just a straight query now we're gonna actually work on adding a parameter into that as well so let's make sure i got that right object's raw yeah objects raw okay so let's figure out our query so start with this we're this time we're going to start with our time frames so we can do it most simply is this is the bulk of what we've got uh except for we're going to have a an id whoops that's something i want to flip over to so because here i want child time frame so i want everybody in this case i'm sending a one across so it's technically going to be it's going to be a dp time frame where parent id equals one so that's our parameter so here's and if we look i have first quarter second quarter goals first quarter second quarter goals but now we have to expand that out because we need to go look at what the children of that is and the way we do that it goes back to we're going to create a left join back to the same table but we're going to join where parent id equals myself essentially uh in each case this would be self and let's see so that's our left join and then i'm just going to do where like we had it before where parent id equals 1 except for now i have to go off this primary this is the main table i'm searching off of this tf2 here is the second table i'm going to be is the one that's going to be essentially the children so i want to make sure where the so where that and so this is where uh tf1 dot parent id equals that so now what i'm going to see is that neither of these have children which i think we saw that here so if i look at first quarter it doesn't have any child time frames if you go to second quarter it doesn't have any child time frames but let's do first quarter i'm going to add some time frames i'm going to add uh january and save and this is something we want to do as well we're going to want to go back and have the ability to go back to this guy's parent which we did not add here so i've got january and i'm going to create february and i'm going to create march so this will give us something to be able to look at in our query so now if we look at our query we're going to see uh same query we're going to see now that we get january february march for first quarter and then nothing for second quarter now we also want to do uh the tasks or the goals so that means we're also going to left join back into uh itp project underscore and what did we call that we called it oops over here models we call it goals called it task whoops so if we go dp project task and we're going to call it t on t dot what did we call it whoops and the parent to that is actually time frame so we go over here so we're t dot time frame equals and then this is going to be off of the parent one not the child one so equals tf1 dot id and t.time frame so i wonder if i not get that right let's see so if i look at my model task time frame well let's take a look at tasks did i not maybe i missed something here let's see if i do a make migrations let's make sure i don't nope no changes i don't think i have to migrate no migrations to apply oh let's do can i do all oh it says none so let's just make sure one more time so i do dp project yeah there's no migration surprise okay so let's go take a look back at my query uh let's see so it says unknown column t at time frame so if we describe dp project underscore task oh it's time frame id my mistake so we need to do the time frame id and so now we see it's starting to get a little bit long but we have here is a row where we're going to see that this has got january oh and there's no tasks tied to uh each of those do i have no tasks to that let's create some tasks on that guy so if i go back to oh i should have it oops let's run the server okay so running the server if i look at here i should have goals i should have multiple goals uh and this is the time frame so if i do oh and let me actually pull this out since i'm sort of working on this on the fly this is going to be here let's just do it like this for now uh so we want to left join task on its time frame id equals the primary id oh uh oh no i'm sorry this is not actually off of the time frame off the parent this is actually off of he's actually going to be the time frame id equals in this case 1 as well because i'm actually i want the tasks for this this guy not these inner guys so now if i do that i'm going to see let's see so let's do this one so now i see a much longer one and i can see i'm going to start i'm seeing duplicates all over the place because i'm doing all these left joins so this goes back to what we saw last time where we're going to do um there we go where one of the things we did is we started to do group by so where is that at so now what we want to do is take in our long query wherever that went let's just do this i think i missed it there it is okay so for here now there's two things we're gonna group by this can become a little bit annoying so we're gonna go group by first let's group by um how do we want to do this one oh well first let's do tf.star tf1.star so we're going to take everything from here so let's do that first and that's going to clean it up a little because we're going to start seeing where our duplicates exist so see we can see a bunch of duplicates here so now that means darn it let's come back over here to our view let's so the first thing we're going to group by is we've got two things we need to group by we need to group by the uh tf dot parent id so we're going to group by actually we're in group by tf2 dot id and so if we group by the tf2 dot id uh here did i yeah i get my group yeah i got it so now we're gonna see only second quarter goals plus the three first quarter because remember we have what we're going to see is we have three goals for a period so second quarter goal shows up once but then we're going to see first quarter because we have three tasks so now we have to uh and with that that means we want to uh whoops let's go reviews and then here the first things we're going to do is we're going to do count um so we have two counts one is going to be uh children and the other is going to be tasks so we need to group by tf2 id and we also need to group by the task dot id so we group by task id we're going to see it's not really going to change but we are going to count so children we need to count the tf2.id and i think we can do this and if we count the t dot id we're gonna get tasks let's see what we get here if we go back over here so we're not getting that one right so we missed something so let's go back to this so we count that we're going to group by that may have to group this twice because it may be a little bit different oh that is it so we have to gr we have to do this twice now so this one becomes a little bit more complicated so we actually have to do well let's do the children first and then we're going to come back because this is going to actually be a little more complicated which may take us into so we're probably not that's going to be a two-parter because okay so the first thing we want to do is we want to get our children and then that's going to be based off of tf2 so if we do that we go over here well oh did i lose i lost my where somewhere in there uh whoops views here it is uh so where so if i do that i think that's going to give me what i need oh now i got a count oh did i group that right let's go look back at my v select so i'm going to select star group by that so tf1 oh that needs to be count where tf1 equals that let's see i think that will get us there no we're not quite there because it's not counting us so we're not grouping something right so if we look here we're going to select tf1 we're going to left join on the time frame on tf2 dot parent id equals that so maybe we can count tf i don't want to group by tf 2 on a group by tf1 and then this is going to be tf1.star count and this is going to be children tf2.id so now we should see it there we go so now we see our children so now we're gonna since we're doing two groupings this is kind of we're gonna actually build on this and uh we're gonna take care of that up next episode it's gonna be a nice little two-parter so i'm gonna wrap this one up we've gone a little long i forgot how complicated this one was gonna be that's what happens when you do stuff on the fly so uh we'll come back around to it but until then go out have yourself a great day a great week and we will talk to you next time you
Transcript Segments
[Music]
hello and welcome back
we are continuing our uh
cleaning up and user experience side of
this
application so we are uh last time
around
we added this has four children so
in the um roadmaps we're able to see
from the top list sort of what
what's been filled out and what hasn't
and now let's take that
down a level and we're gonna actually
look at the uh
time frames because at this point you
have and time frames a little different
from roadmap roadmap just has
time frames underneath it time frame
actually has two things
it has if you look it has child time
frames potentially
and goals for the period uh one other
thing i'm gonna do is i'm gonna tweak
this um here so that we're not our
footer is not hanging over
our uh lists but i probably won't get to
that this time
uh but we will start with uh we're gonna
add two things here we're going to have
child time frames and then we're going
to have tasks because a given time frame
could have tasks
directly attac or we'll say goals
because they could be directly attied to
it as we have here so we're gonna go
back over this is gonna be more
from our queries we're gonna do another
raw query so when we
list roadmaps we are now going to have
uh within this when we do list well
there's two things
let's start here so this is on our time
frame so let's go to
our time frame we'll take a look at that
real quick so
time frame is that a specific one or is
that
okay so we have two we have a listing of
time frames so we have them underneath
we'll stick with the one underneath
which is the one with an id
uh that's actually edit i'm sorry
oh yeah that's what we want whoops
that's what we want so if we go to our
goals
and we hit a time frame so this is
looking at a specific time frame
okay so it's tf1 which is edit time
frame
and so let's go look at that real quick
and we look in here we come in
and data is just this time frame objects
we're just getting a list
of time frames within that
so what we need to do is this instead is
going to be
instead of objects get it's going to be
objects.raw
and we're still going to have it based
on there's going to be an id so we'll
take a look at this
because we're going to have end up
having a parameter
and if we look at our prior run
here it was just a straight query now
we're gonna actually work
on adding a parameter into that as well
so
let's make sure i got that right
object's raw yeah
objects raw okay
so let's figure out our query so start
with this
we're this time we're going to start
with our time frames
so we can do it most simply is
this is the bulk of what we've got uh
except for we're going to have a
an id whoops
that's something i want to flip over to
so because here
i want child time frame so i want
everybody
in this case i'm sending a one across
so it's technically going to be it's
going to be a dp time frame where
parent id
equals one so that's our parameter so
here's
and if we look i have first quarter
second quarter goals
first quarter second quarter goals but
now we have to expand that out because
we need to go
look at what the children of that is
and the way we do that it goes back to
we're going to create a left join
back to the same table but we're going
to join where parent id
equals myself essentially uh
in each case this would be self
and let's see so that's our left join
and then i'm just going to do where
like we had it before where parent id
equals 1 except for now i have to go off
this primary this is the main table
i'm searching off of this
tf2 here is the second table i'm going
to be is the one that's going to be
essentially the children
so i want to make sure where the
so where that and so this is where uh
tf1 dot parent id
equals that so now what i'm going to see
is that neither of these have children
which i think we saw that here so if i
look at first quarter it doesn't have
any child time frames
if you go to second quarter it doesn't
have any child time frames
but let's do first quarter i'm going to
add some time frames
i'm going to add uh january
and save and this is something we want
to do as well we're going to want to go
back
and have the ability to go back to this
guy's parent
which we did not add here so i've got
january and i'm going to create february
and i'm going to create march
so this will give us something to be
able to look at
in our query so now if we look at our
query we're going to see
uh same query we're going to see now
that we get january february march
for first quarter and then nothing for
second quarter
now we also want to do uh the
tasks or the goals so that means we're
also going to left join back
into uh itp project
underscore and what did we call that we
called
it oops over here models
we call it goals called it task
whoops so if we go dp project task
and we're going to call it t
on t dot what did we call it whoops
and the parent to that is actually time
frame
so we go over here so we're t dot time
frame
equals and then this is going to be off
of the parent one not the child one
so equals tf1 dot id
and t.time frame
so i wonder if i not get that right
let's see
so if i look at my model
task time frame well let's take a look
at tasks did i not maybe i missed
something here let's see
if i do a make migrations
let's make sure i don't nope no changes
i don't think i have to migrate
no migrations to apply oh
let's do can i do all
oh it says none
so let's just make sure one more time so
i do dp project
yeah there's no migration surprise okay
so let's go take a look back at my query
uh let's see so it says
unknown column t
at time frame
so if we describe dp project
underscore task
oh it's time frame id my mistake so we
need to do the time frame id
and so now we see it's starting to get a
little bit long but we have
here is a row where we're going to see
that this has got january
oh and there's no tasks tied to
uh each of those do i have no tasks to
that let's create some tasks on that guy
so if i go back to oh i should have it
oops let's run the server
okay so running the server
if i look at here i should have goals i
should have
multiple goals uh and this is the time
frame so
if i do
oh and let me actually pull this out
since i'm sort of working on this on the
fly
this is going to be here let's just do
it like this for now
uh so we want to left join task on its
time frame id
equals the primary id
oh uh oh no i'm sorry this is not
actually off of the time frame off the
parent this is actually
off of he's actually going to be the
time frame id equals
in this case 1 as well
because i'm actually i want the tasks
for this
this guy not these inner guys
so now if i do that i'm going to see
let's see so let's do this one so now i
see a much longer one
and i can see i'm going to start i'm
seeing duplicates all over the place
because i'm doing all these left joins
so this goes back to what we
saw last time where we're going to do
um
there we go where one of the things we
did is we started to do
group by
so where is that at
so now what we want to do is take in our
long query wherever that went
let's just do this i think i missed it
there it is
okay so for here now there's two things
we're gonna group by this can become a
little bit
annoying so we're gonna go group by
first
let's group by um
how do we want to do this one oh well
first let's do tf.star
tf1.star so we're going to take
everything from here so let's do that
first
and that's going to clean it up a little
because we're going to start seeing
where our duplicates exist
so see we can see a bunch of duplicates
here
so now that means
darn it let's come back over here to our
view let's so the first thing we're
going to group by is we've got two
things we need to group by we need to
group by
the uh
tf dot parent id so we're going to group
by actually
we're in group by tf2 dot
id
and so if we group by the tf2 dot id
uh here
did i yeah i get my group yeah i got it
so now
we're gonna see only second quarter
goals plus the three first quarter
because remember
we have what we're going to see is we
have three goals for a period
so second quarter goal shows up once but
then we're going to see
first quarter because we have three
tasks so now we have to
uh and with that that means we want to
uh whoops
let's go reviews and then here the first
things we're going to do is we're going
to do
count um
so we have two counts one is going to be
uh
children and the
other is going to be tasks
so we need to group by tf2 id and we
also need to group
by the task dot id
so we group by task id we're going to
see it's not really going to change
but we are going to count so children we
need to count the tf2.id
and i think we can do this and if we
count the
t dot id we're gonna get tasks let's see
what we get here
if we go back over here
so we're not getting that one right so
we missed something so let's go back to
this so we count that we're going to
group by that
may have to group this twice because it
may be a little bit different
oh that is it so we have to gr we have
to do this twice now so this one becomes
a little bit more complicated
so we actually have to do
well let's do the children first
and then we're going to come back
because this is going to actually be
a little more complicated
which may take us into so we're probably
not that's going to be a two-parter
because okay so the first thing we want
to do is we want to get our children and
then that's going to be
based off of tf2
so if we do that we go over here
well
oh did i lose i lost my where somewhere
in there
uh whoops views
here it is uh so where
so if i do that
i think that's going to give me what i
need
oh now i got a count
oh did i group that right let's go look
back at my v
select so i'm going to select star
group by that
so tf1 oh that needs to be count
where tf1 equals that
let's see i think that will get us there
no we're not quite there because it's
not counting us so we're not grouping
something right
so if we look here we're going to select
tf1
we're going to left join on the time
frame
on tf2 dot parent id equals that so
maybe we can count tf
i don't want to group by tf 2 on a group
by tf1
and then this is going to be tf1.star
count
and this is going to be children
tf2.id so now
we should see it there we go so now we
see our children
so now we're gonna since we're doing two
groupings this is kind of we're gonna
actually build on this
and uh we're gonna take care of that up
next episode it's gonna be a nice little
two-parter
so i'm gonna wrap this one up we've gone
a little long i forgot
how complicated this one was gonna be
that's what happens when you do stuff on
the fly
so uh we'll come back around to it but
until then
go out have yourself a great day a great
week and we will talk to you
next time
you