00:00
(dramatic music)
00:03
- So while the static query methods on our Lucid models
00:04
provide quick and easy query access,
00:06
we can also build out queries
00:08
using the actual Query Builder.
00:10
Now, the Query Builder with AdonisJS
00:12
is built on top of ConnectsJS.
00:14
It adds in some additional functionalities,
00:16
especially around relationships.
00:18
The Query Builder also allows you to write,
00:19
insert, update, and delete operations
00:20
in addition to querying your data from your database.
00:23
And it also has a raw Query Builder,
00:24
which lets you write raw queries in a safe way
00:26
that will protect you from SQL injection attacks.
00:29
Now, there's two different versions of the Query Builder.
00:30
There's the one attached directly to the database module,
00:33
and then there's also one that you get for each model
00:35
that you have within your application,
00:36
referred to as the Model Query Builder.
00:38
And we already have our model imported,
00:40
so let's go ahead and start with the Model Query Builder.
00:42
So we have our query here querying all of our topics,
00:44
as well as a find many.
00:46
Let's take a look at what these two queries would look like
00:48
if we were using the Query Builder.
00:49
So instead of querying all topics
00:50
directly off of our model,
00:51
we can instead build this query using the Query Builder
00:53
by doing const topics equals await topic.
00:56
We can access the Model Query Builder
00:58
by doing .query here,
01:00
and now we have access to the entire Query Builder
01:02
for the model.
01:03
However, if we just stop it here,
01:04
we now have the equivalent of our topic.allQuery,
01:08
and we'll take a look at the results here
01:09
for that in a second.
01:11
As for our find many here,
01:12
we last left off with this querying as no results.
01:14
So let's go ahead and switch this to actually having results
01:16
and then we'll comment this out.
01:18
And we can rebuild this query by doing
01:19
const find many equals await topic.
01:22
Again, we can reach for that .query
01:24
to access the Query Builder.
01:25
And then we can do a where statement,
01:27
and there's a couple of different options.
01:29
We'll take a look at just a basic where statement
01:31
here first off.
01:31
Where, and here we're searching for an ID of one or two.
01:35
So we can specify the column here as ID,
01:37
specify the value as one.
01:39
And then in order to also include the record
01:42
with the ID of two,
01:43
we can do .or where,
01:44
and then re-specify ID as a value of two.
01:47
So let's go ahead and give this a save.
01:48
Let's jump into Insomnia
01:50
and let's execute this query again.
01:52
So now you can see we have a find many with two results,
01:54
an ID of one and an ID of two,
01:56
as well as our topic array,
01:58
which includes all of our topics,
01:59
which is one, two, and four.
02:01
So that's how we can rebuild these two queries.
02:03
However, as I mentioned,
02:04
there's several different ways that we can go about
02:05
actually building this one particular statement.
02:07
Let's go ahead and comment this out.
02:09
We can do const,
02:10
let's do find many again,
02:11
equals await topic.query to get our Query Builder.
02:14
And instead of doing a where and then an or where,
02:16
we can do a single where called where in,
02:19
and this accepts the column that we wanna search against
02:21
as the first argument.
02:22
So let's do ID there.
02:24
And then it accepts an array of values
02:26
as the second argument.
02:27
So we can do an array with our IDs of one and two there.
02:30
Let's give that a save,
02:31
jump back into Insomnia and send this off again.
02:33
And you can see our find many results here
02:34
come back the exact same as we had prior
02:36
with our IDs of one and two.
02:38
For this particular query statement,
02:40
if we were specifically looking to replicate find many
02:43
with the Query Builder,
02:44
this where in statement would be the solution
02:46
that I would reach for.
02:47
So for our next query example,
02:48
let's go ahead and take a look at all of our topics here.
02:50
Let's say that we wanna match for our find many result,
02:53
the topics that have script somewhere within the slug.
02:56
So that should be JavaScript and TypeScript.
02:58
If we jump back into our code base here,
03:00
let's go and comment out our find many.
03:02
Let's do const find many equals await topic.query
03:05
to reach for that Query Builder again.
03:06
And we can do a where statement
03:08
and let's specify the slug as the column
03:10
that we wanna search against.
03:11
And now instead of doing a direct value here,
03:14
where we would specify that we wanna match against script,
03:16
if we were to give this a run,
03:18
let's go ahead and see what this looks like.
03:19
Let's see if we get back zero results.
03:21
And that's because we're doing a strict where statement here
03:23
we're saying where the slug equals script
03:26
and we don't have any results for that.
03:27
So if we were to jump back into here
03:29
and specify a third argument in between our column
03:32
and our value here,
03:33
we can say that we want to specify where like scripts.
03:36
And now we have access to add in the percent sign
03:39
to designate that we want to match anything
03:41
that ends with script,
03:43
or we can do starts with script,
03:45
or we can do has script somewhere in the string.
03:47
Now for our case, both of ours end with script.
03:50
So let's take a look at what that looks like.
03:51
So let's give this a save,
03:52
jump back into Insomnia, send this off.
03:54
And then you can see we get back
03:55
both JavaScript and TypeScript as expected.
03:58
Now there's also an alternative way
03:59
that we can go about this one here as well.
04:01
So we can do const findMany equals 08 topic.query
04:05
to get our query builder.
04:06
And we can do where like specify the key.
04:09
And now all that we have to do is specify the like value.
04:11
So we can do percent script
04:12
to specify anything that ends with the word script.
04:14
Give that a save, jump back into Insomnia,
04:16
send this off again, and we get back the same result.
04:19
In addition to that, there's also where I like,
04:21
where you can do a case insensitive search.
04:23
And I'll take a step back here for a second
04:24
on our where statement.
04:25
Whenever you do provide three arguments,
04:27
you have the first argument as the column name,
04:29
the second argument as the comparison,
04:31
and then the third argument as the comparison value.
04:34
Whenever you only provide two arguments
04:35
to the where statement, as we are up here,
04:37
the comparison will default to an equals check.
04:40
So it will perform where slug equals script.
04:43
So two arguments, it will default to an equals check.
04:45
With three arguments, you can then provide in
04:47
what you want that comparison logic to be.
04:49
So let's say that you wanna stack where statements,
04:51
you wanna do multiple and statements.
04:52
So let's do const find many equals await topic dot query.
04:56
I'm gonna break this down into a separate line
04:58
and let's do where like slug contains script.
05:01
To stack an additional where statement to do and where,
05:04
we can do dot, and then there's a method called and where.
05:06
In addition to this, you can also just call where again,
05:09
and that will default to saying and where automatically.
05:12
Whichever method you end up using is completely up to you.
05:14
They will both function completely the same.
05:16
They both act as an additional where statement
05:18
when stacked upon one another.
05:19
So we'll go ahead and just do where here.
05:21
So we do and where, and let's do ID is two.
05:24
So what we should expect is for this
05:25
to just return back JavaScript as the slug contains script,
05:28
and the ID is two.
05:29
So let's give this a run,
05:30
and our find many is now just returning back JavaScript.
05:32
Now there's also an alternative way
05:34
that we can write our where statements.
05:35
We can nest these inside of a callback function.
05:37
And what this will essentially do is inside of our SQL,
05:39
just wrap anything inside of that callback function
05:42
in parentheses.
05:43
So what that allows us to do
05:44
is write and or statements together.
05:46
So we could do our where like here,
05:48
and then we can do .where query,
05:50
which is our callback method.
05:51
And we just build off of our query statement
05:53
inside of this callback.
05:54
So we can do where ID is two.
05:57
And this is essentially going to act the exact same
05:59
as what we just had before,
06:00
where we had just a where ID of two,
06:02
except this additional where statement
06:04
is now wrapped in the parentheses.
06:05
So let's give this a run real quick.
06:07
And there we go.
06:07
We have back just JavaScript there.
06:09
But in addition to that,
06:10
we can also chain off of an additional or where
06:12
to say ID of four.
06:14
And now we should get back to the JavaScript and TypeScript
06:17
as we do here,
06:18
because it's wrapping everything
06:19
inside of this callback function
06:20
for our SQL inside of parentheses.
06:22
So essentially the underlying query
06:24
that we're running here is where slug ends with script
06:27
and where ID is two or four.
06:29
Obviously for this specific query,
06:31
there's much cleaner ways that we can go about it,
06:32
but this gives you an idea
06:34
of how you can go about nesting your queries
06:35
to add in additional and or statements
06:37
on top of one another.
Join The Discussion! (0 Comments)
Please sign in or sign up for free to join in on the dicussion.
Be the first to Comment!