Detailed Notes
This is a 2nd 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 40 as well): https://github.com/robbroadhead/develpreneur-pythontutorial
Transcript Text
[Music] okay welcome back and we are continuing going into some sort of complex uh raw queries we're getting a little drifting i apologize i guess in a sense we're drifting into some more complex sql which is not really in our general django python tutorials but it's something that's going to be useful for us so last time around we were able to get uh we're looking at building out some information here actually i'm sorry here at the roadmap levels i want to be able to see four time frames what are the child time frames and tasks doing so we've got this time we've got the child time frames if we look at this guy he's got annual and professional goals uh oh i'm looking at the road map i'm sorry here so this is the one i'm looking at and what i need to do uh did i get that oh so i probably want to i'm going to probably want to pull these guys actually here so let's actually look up uh well we'll fix that putting in the wrong place but that's okay uh based on this whoops so oh here we go roadmap has seven children which is interesting that i've managed to somehow break that one because now he's looking at all of the children in there so interesting and oh because they're all under that road map not under so it's under all of these time frames because i have here i have a child time frame and i have some other time frames oh anyways so right now what we're getting is we're getting oops we're here we're getting these two time frames for this one and we have counted the children but now we need to count the tasks and what we end up having to do here is we're going to end up taking this big thing and we're going to wrap it we're going to call it source i guess we're going to call it uh we'll call it main and then what we're going to end up doing is we're going to select star from that i'll actually do i'll do main.star from this query from this query which is going to give us essentially this information and then what we need to do um actually wait i want to do it with the group by so i'm going to get all the way here and then i'm gonna have to left join on tp project whoops underscore task i'm gonna call it t on t dot and what do we call that uh time frame id equals main dot what are we calling it it's going to be main oh it's actual id so we can actually keep it here so we're gonna call it uh because it comes back here so main dot oh we may not have to do that so we need to do it here i think we can do that so let's do this t dot star so we're going to bring all the tasks in for each of those so if we do that let's see what we get it may not take it okay generally speaking we need to take a select star for well let's do this let's take it in pieces so we'll come back first we're going to do select star from uh the time frame and then we would left join him on oh actually we can just do that and actually i think we can do it here where he equals tf1.id and then we're going to have our where and if we look at that actually we do want no because we want this to be and uh time frame equals one let's just take that for now uh did we oh data time frame id oh let's look at what we did we typed something here oh my mistake so take him and this is going to give us our tasks or not uh where tf one not parent id equals this oh it's not this is going to be t f1 dot uh time frame id oh alright got it private state so if i go here uh what am i missing on that oh i have that see on let's join that on that there we go foolish sequel issue okay so there's all my tasks within that time frame and so i'm seeing this here oh it's because it goes up to the road map so it's not where the road map is oh yes because this can be all tasks under a road map for a given one oh that should work because i'm going to get everybody where the time frame and actually i guess all the tasks are probably tied up to this guy right now if i look at my i don't know i have task of task lists here we go yes i'm going to actually see all of these guys i'll see them twice because i'm going to see them based on the id so now what i need to do is i need to find a way to left join these things and what i probably want to do let's see is i'm based on here so if i look at a roadmap i'm not going to bother with it but i am going to do within here so for this given id so this is actually and this is actually in roadmap so we're going to fix that that's we're going to move our query so given an id of this guy which is a time frame i want to do my tasks which should be three that are tied to that task alone so let's go back and look at this so if i select from the time frame oh that's right i had where that was everybody now i build this back and this is going to look a little better clean this up so now i'm just seeing uh first quarter i've got three tasks second quarter i've got three tasks so they're not actually assigning right one i've actually because i've got these three tasks and it's by a quarter i'm trying to figure out because i'm doing left join task on that so it's everywhere that the time frame id so these guys didn't get their time frame ids built right i don't think oh i bet i have oh maybe i do do i have tasks so he's got uh let's see so if i go back and look here if i look at second quarter goals i don't have any tasks if i look at first quarter goals i do have tasks uh create application version one for example oh but that's not what i'm looking at i should see my mistake i should see up here for this one i should see these three so i see those three but i see them across the two time frames so let's go look at our query here let's see so if i go here i'm going to select star from time frame i'm going to left join the task on that where time frame parent id equals one but it's actually uh where so let's look at this real quick looking at my results i really need where uh if i look at those tasks so first quarter if i look at these guys what's the differentiating that is this id so i don't think he's coming up right so the task should be oh that's what it is i'm looking at the task based on the children and i don't want it there i want it actually here uh so i want the time frame id equals to one i don't know oh yeah we're paranoid equals one no this is where time frame dot id one so i can actually do it uh t dot time frame id equals tf1.id so i can do it like this my mistake and so now whoop now i'm gonna see my three so i've got my three tasks whoopsie so let's flip over here if i look real quick at this i can see so this one gives me my children this one gives me my tasks or my test child thing so what i can do here is i'm going to make this my main and then what i need to do is left join actually it's going to be an inner join i believe well we can probably do a left joint it's going to get really complicated really fast so i'm going to left join this guy uh this will be goals on goals dot id equals main dot id let's see how this works out and so this one is just going to be um i want to do uh this is going to be t f dot id as id comma well we'll leave it as id and then we're going to do the second which is going to be t dot id is going to be tid and so uh this is gonna be t let's do that let's do main dot star comma uh the second guy goals and we'll do goals.star and then we're gonna have to do the wear again so where [Music] main dot id equals one because this one i'm gonna have our guy again uh i don't think i need that let's see did we get it okay it's an empty set so let's go look at our two queries so our first query gives us this and we have children so we have a period we have an id and then what we need to do oh he's actually not going to be in there so we have to within each of these oh goals is actually a separate count by mistake all right so then we're going to do this okay i'm jumping ahead a little bit because there's a lot of complex stuff there that was in the sql that i wanted to work through what we ended up with is we do have a nice little query here that gives us a number of children so we need to do is uh we plug that in and uh it ends up we're tying it up to the roadmap so it's going to be where the roadmap id equals this value that we're going to send in it's just something a little new so we've got our raw query we go ahead and we have a we're going to tag our or uh our order by into the sql and then that allows us to do our order by which we'll see here by name and then what we do is we do within this raw you do comma and then you're going to give it an array of values we just have one so it's going to be id and it's going to plug id into the percent s if we had a second one like we could do percent s and then we could do maybe another percent s here then we do id comma whatever that second value is so now with that and our query then all we need to do is update our uh oops is update our roadmap list i'm sorry roadmap edit and we're going to add children and we're going to take this extra column we just add which is children itself uh we called it children you see here and we're going to do that to display so now we're going to see this query i'm going to see that pop over on this screen and we can see that we have two children actually let's change that a little bit and we're gonna say has just to make it a little cleaner has that children and we could do tasks uh but we'll wait let's see we'll just keep it as a period so now we'll see is how many children it has and then we're going to go into this and i don't know if i want to deal with goals or not we'll sort of see how that looks uh but it does mean we need to update this one essentially the same and so we'll come back around next time and we're going to do a couple of those things where i do that and actually probably going to change up a little bit of our navigation so we can add goals and time frames and do it in a way that makes a little more sense so it's not that painful thing that we did here where we kept having to do back arrows and stuff like that so we're continuing to clean it up getting there it's almost done as far as general functionality and then we'll be doing more user experience stuff but that's it for this part of raw queries and using some parameters as always out there have yourself a great day a great week and we will talk to you next you
Transcript Segments
[Music]
okay welcome back
and we are continuing going into some
sort of complex
uh raw queries we're getting a little
drifting i apologize i guess in a sense
we're drifting into some more complex
sql which is not really in our
general django python tutorials but
it's something that's going to be useful
for us so
last time around we were able to get uh
we're looking at building out some
information
here actually i'm sorry here at the
roadmap levels i want to be able to see
four time frames what are the child time
frames and tasks
doing so we've got this time we've got
the child time frames
if we look at this guy he's got annual
and professional goals
uh oh i'm looking at the road map i'm
sorry here
so this is the one i'm looking at and
what i need to do
uh did i get that oh so i probably want
to i'm going to probably want to pull
these guys
actually here so let's actually look up
uh
well we'll fix that putting in the wrong
place but that's okay
uh based on this whoops
so oh here we go roadmap has seven
children
which is interesting that i've managed
to somehow break
that one because now he's looking at all
of the children
in there so interesting and oh because
they're all under that road map not
under
so it's under all of these time frames
because i have here i have a child time
frame
and i have some other time frames
oh anyways so right now what we're
getting is we're getting oops we're here
we're getting these two time frames for
this one
and we have counted the children but now
we need to count the tasks
and what we end up having to do here is
we're going to end up taking
this big thing and we're going to wrap
it
we're going to call it source i guess
we're going to call it
uh we'll call it main
and then what we're going to end up
doing is we're going to select star
from that i'll actually do
i'll do main.star
from this query from this query which is
going to give us
essentially this information
and then what we need to do
um actually wait i want to do it with
the group by
so i'm going to get all the way here and
then
i'm gonna have to left join on
tp project whoops
underscore task i'm gonna call it t
on t dot and what do we call that uh
time
frame id
equals main dot
what are we calling it it's going to be
main
oh it's actual id so we can actually
keep it here
so we're gonna call it
uh because it comes back here so main
dot
oh we may not have to do that so we need
to do it here
i think we can do that so let's do this
t dot star so we're going to bring all
the tasks in for each of those
so if we do that let's see what we get
it may not take it okay
generally speaking we need to take a
select star for well let's do this
let's take it in pieces
so we'll come back first we're going to
do select star from
uh the time frame
and then we would left join him on oh
actually we can just do that
and actually i think we can do it here
where he equals tf1.id
and then we're going to have our where
and if we look at that
actually we do want no because we want
this to be and
uh time frame equals one
let's just take that for now
uh did we
oh
data time frame id
oh let's look at what we did we typed
something here
oh my mistake
so take him and this is going to give us
our tasks
or not
uh where tf one not parent id equals
this
oh it's not
this is going to be t f1 dot
uh time frame
id oh alright got it
private state so if i go here
uh what am i missing
on that oh
i have that see on
let's join that on that there we go
foolish sequel issue okay so there's all
my tasks
within that time frame
and so i'm seeing this here
oh it's because it goes up to the road
map so it's not where the road map
is oh yes
because this can be all tasks under a
road map
for a given one
oh that should work because i'm going to
get everybody
where the time frame and actually i
guess all the tasks are probably tied up
to this guy right now
if i look at my i don't know i have task
of task lists here we go
yes i'm going to actually see all of
these guys i'll see them twice because
i'm going to see them
based on
the id
so now what i need to do is i need to
find a way to left join these things
and what i probably want to do let's see
is i'm based on here so if i look at a
roadmap
i'm not going to bother with it but i am
going to do within here
so for this given id
so this is actually and this is actually
in roadmap so we're going to fix that
that's we're going to move our query so
given an id
of this guy which is a time frame
i want to do my tasks which should be
three that are tied to that task
alone so let's go back and look at this
so if i select from the time frame
oh that's right i had where
that was everybody
now i build this back and this is going
to look a little better clean this up
so now i'm just seeing uh first quarter
i've got
three tasks second quarter i've got
three tasks so they're not
actually assigning right one i've
actually because i've got these three
tasks and it's
by a quarter
i'm trying to figure out because i'm
doing left join task on that
so it's everywhere that the time frame
id so these guys didn't get their time
frame ids built right i don't think
oh i bet i have oh maybe i do do i have
tasks
so he's got
uh let's see so if i go back and look
here if i look at second quarter goals i
don't have any tasks
if i look at first quarter goals i do
have tasks
uh create application version one for
example
oh but that's not what i'm looking at i
should see my mistake i should see up
here
for this one i should see these three so
i see those three but i see them across
the two
time frames so let's go look at our
query here
let's see so if i go here i'm going to
select star from time frame
i'm going to left join the task on that
where
time frame parent id equals one but it's
actually
uh where
so let's look at this real quick looking
at my results i really need
where
uh if i look at those tasks
so first quarter if i look at these guys
what's the differentiating that is
this id
so i don't think he's coming up right so
the task should be
oh that's what it is i'm looking at the
task based on the children and i don't
want it there i want it actually here
uh so i want the time frame id
equals to one i don't know
oh yeah we're paranoid equals one
no this is where time frame dot id
one so i can actually do it
uh t dot time frame id equals
tf1.id so i can do it like this
my mistake and so now whoop now i'm
gonna see my three so i've got my three
tasks
whoopsie so let's flip over here if i
look real quick
at this i can see so this one gives me
my children this one gives me my tasks
or my
test child thing so what i can do here
is i'm going to make this my main
and then what i need to do is
left join
actually it's going to be an inner join
i believe
well we can probably do a left joint
it's going to get really complicated
really fast
so i'm going to left join this guy
uh this will be goals on
goals dot id
equals
main dot id let's see how this works out
and so this one is just going to be
um i want to do
uh this is going to be t f dot id
as id comma well we'll leave it as id
and then we're going to do the second
which is going to be
t dot id is going to be tid
and so uh this is gonna be
t let's do that let's do main dot star
comma uh the second guy
goals and we'll do goals.star
and then we're gonna have to do the wear
again so where
[Music]
main dot id
equals one because this one i'm gonna
have our guy again
uh
i don't think i need that let's see
did we get it
okay it's an empty set
so let's go look at our two queries so
our first query
gives us this
and we have children so we have a period
we have an id
and then what we need to do
oh he's actually not going to be in
there so we have to within
each of these oh goals is actually a
separate count
by mistake all right so then we're going
to do this
okay i'm jumping ahead a little bit
because there's a lot of complex
stuff there that was in the sql that i
wanted to work through
what we ended up with is we do have a
nice little query here
that gives us a number of children so we
need to do
is uh we plug that in
and uh it ends up we're tying it up to
the roadmap
so it's going to be where the roadmap id
equals
this value that we're going to send in
it's just something a little new so
we've got our raw query
we go ahead and we have a we're going to
tag our or uh
our order by into the sql
and then that allows us to do our order
by
which we'll see here by name
and then what we do is we do within this
raw you do comma and then you're going
to give it
an array of values we just have one
so it's going to be id and it's going to
plug id into the percent s
if we had a second one like we could do
percent s and then we could do
maybe another percent s here then we do
id comma whatever that second value is
so now with that and our query
then all we need to do is update
our uh oops is update our roadmap
list i'm sorry roadmap edit
and we're going to add children and
we're going to take this extra
column we just add which is children
itself uh we called it children you see
here
and we're going to do that to display so
now we're going to see this query
i'm going to see that pop over on this
screen and we can see
that we have two children
actually let's change that a little bit
and we're gonna say
has
just to make it a little cleaner has
that children
and we could do tasks uh but we'll wait
let's see we'll just keep it as a period
so now we'll see is how many children it
has
and then we're going to go into this and
i don't know if i want to deal with
goals or not we'll sort of see how that
looks
uh but it does mean we need to update
this one essentially the same
and so we'll come back around next time
and we're going to do a couple of those
things where i do that and actually
probably going to change up a little bit
of our navigation so we can add goals
and
time frames and do it in a way that
makes a little more sense
so it's not that painful thing that we
did here where we kept having to do back
arrows and stuff like that so
we're continuing to clean it up getting
there it's almost done
as far as general functionality and then
we'll be doing more user experience
stuff but
that's it for this part of raw queries
and using some parameters as always out
there have yourself a great day
a great week and we will talk to you
next
you