Intro to the Query Builder & It's Where Statements
In this lesson, we'll introduce the query builder. We'll spend most of our time looking at its various where statement options and how we can use them to build queries.
- Author
- Tom Gobich
- Published
- Nov 26, 23
- Duration
- 6m 39s
Developer, dog lover, and burrito eater. Currently teaching AdonisJS, a fully featured NodeJS framework, and running Adocasts where I post new lessons weekly. Professionally, I work with JavaScript, .Net C#, and SQL Server.
Adocasts
Burlington, KY
Transcript
Intro to the Query Builder & It's Where Statements
-
(dramatic music)
-
- So while the static query methods on our Lucid models
-
provide quick and easy query access,
-
we can also build out queries
-
using the actual Query Builder.
-
Now, the Query Builder with AdonisJS
-
is built on top of ConnectsJS.
-
It adds in some additional functionalities,
-
especially around relationships.
-
The Query Builder also allows you to write,
-
insert, update, and delete operations
-
in addition to querying your data from your database.
-
And it also has a raw Query Builder,
-
which lets you write raw queries in a safe way
-
that will protect you from SQL injection attacks.
-
Now, there's two different versions of the Query Builder.
-
There's the one attached directly to the database module,
-
and then there's also one that you get for each model
-
that you have within your application,
-
referred to as the Model Query Builder.
-
And we already have our model imported,
-
so let's go ahead and start with the Model Query Builder.
-
So we have our query here querying all of our topics,
-
as well as a find many.
-
Let's take a look at what these two queries would look like
-
if we were using the Query Builder.
-
So instead of querying all topics
-
directly off of our model,
-
we can instead build this query using the Query Builder
-
by doing const topics equals await topic.
-
We can access the Model Query Builder
-
by doing .query here,
-
and now we have access to the entire Query Builder
-
for the model.
-
However, if we just stop it here,
-
we now have the equivalent of our topic.allQuery,
-
and we'll take a look at the results here
-
for that in a second.
-
As for our find many here,
-
we last left off with this querying as no results.
-
So let's go ahead and switch this to actually having results
-
and then we'll comment this out.
-
And we can rebuild this query by doing
-
const find many equals await topic.
-
Again, we can reach for that .query
-
to access the Query Builder.
-
And then we can do a where statement,
-
and there's a couple of different options.
-
We'll take a look at just a basic where statement
-
here first off.
-
Where, and here we're searching for an ID of one or two.
-
So we can specify the column here as ID,
-
specify the value as one.
-
And then in order to also include the record
-
with the ID of two,
-
we can do .or where,
-
and then re-specify ID as a value of two.
-
So let's go ahead and give this a save.
-
Let's jump into Insomnia
-
and let's execute this query again.
-
So now you can see we have a find many with two results,
-
an ID of one and an ID of two,
-
as well as our topic array,
-
which includes all of our topics,
-
which is one, two, and four.
-
So that's how we can rebuild these two queries.
-
However, as I mentioned,
-
there's several different ways that we can go about
-
actually building this one particular statement.
-
Let's go ahead and comment this out.
-
We can do const,
-
let's do find many again,
-
equals await topic.query to get our Query Builder.
-
And instead of doing a where and then an or where,
-
we can do a single where called where in,
-
and this accepts the column that we wanna search against
-
as the first argument.
-
So let's do ID there.
-
And then it accepts an array of values
-
as the second argument.
-
So we can do an array with our IDs of one and two there.
-
Let's give that a save,
-
jump back into Insomnia and send this off again.
-
And you can see our find many results here
-
come back the exact same as we had prior
-
with our IDs of one and two.
-
For this particular query statement,
-
if we were specifically looking to replicate find many
-
with the Query Builder,
-
this where in statement would be the solution
-
that I would reach for.
-
So for our next query example,
-
let's go ahead and take a look at all of our topics here.
-
Let's say that we wanna match for our find many result,
-
the topics that have script somewhere within the slug.
-
So that should be JavaScript and TypeScript.
-
If we jump back into our code base here,
-
let's go and comment out our find many.
-
Let's do const find many equals await topic.query
-
to reach for that Query Builder again.
-
And we can do a where statement
-
and let's specify the slug as the column
-
that we wanna search against.
-
And now instead of doing a direct value here,
-
where we would specify that we wanna match against script,
-
if we were to give this a run,
-
let's go ahead and see what this looks like.
-
Let's see if we get back zero results.
-
And that's because we're doing a strict where statement here
-
we're saying where the slug equals script
-
and we don't have any results for that.
-
So if we were to jump back into here
-
and specify a third argument in between our column
-
and our value here,
-
we can say that we want to specify where like scripts.
-
And now we have access to add in the percent sign
-
to designate that we want to match anything
-
that ends with script,
-
or we can do starts with script,
-
or we can do has script somewhere in the string.
-
Now for our case, both of ours end with script.
-
So let's take a look at what that looks like.
-
So let's give this a save,
-
jump back into Insomnia, send this off.
-
And then you can see we get back
-
both JavaScript and TypeScript as expected.
-
Now there's also an alternative way
-
that we can go about this one here as well.
-
So we can do const findMany equals 08 topic.query
-
to get our query builder.
-
And we can do where like specify the key.
-
And now all that we have to do is specify the like value.
-
So we can do percent script
-
to specify anything that ends with the word script.
-
Give that a save, jump back into Insomnia,
-
send this off again, and we get back the same result.
-
In addition to that, there's also where I like,
-
where you can do a case insensitive search.
-
And I'll take a step back here for a second
-
on our where statement.
-
Whenever you do provide three arguments,
-
you have the first argument as the column name,
-
the second argument as the comparison,
-
and then the third argument as the comparison value.
-
Whenever you only provide two arguments
-
to the where statement, as we are up here,
-
the comparison will default to an equals check.
-
So it will perform where slug equals script.
-
So two arguments, it will default to an equals check.
-
With three arguments, you can then provide in
-
what you want that comparison logic to be.
-
So let's say that you wanna stack where statements,
-
you wanna do multiple and statements.
-
So let's do const find many equals await topic dot query.
-
I'm gonna break this down into a separate line
-
and let's do where like slug contains script.
-
To stack an additional where statement to do and where,
-
we can do dot, and then there's a method called and where.
-
In addition to this, you can also just call where again,
-
and that will default to saying and where automatically.
-
Whichever method you end up using is completely up to you.
-
They will both function completely the same.
-
They both act as an additional where statement
-
when stacked upon one another.
-
So we'll go ahead and just do where here.
-
So we do and where, and let's do ID is two.
-
So what we should expect is for this
-
to just return back JavaScript as the slug contains script,
-
and the ID is two.
-
So let's give this a run,
-
and our find many is now just returning back JavaScript.
-
Now there's also an alternative way
-
that we can write our where statements.
-
We can nest these inside of a callback function.
-
And what this will essentially do is inside of our SQL,
-
just wrap anything inside of that callback function
-
in parentheses.
-
So what that allows us to do
-
is write and or statements together.
-
So we could do our where like here,
-
and then we can do .where query,
-
which is our callback method.
-
And we just build off of our query statement
-
inside of this callback.
-
So we can do where ID is two.
-
And this is essentially going to act the exact same
-
as what we just had before,
-
where we had just a where ID of two,
-
except this additional where statement
-
is now wrapped in the parentheses.
-
So let's give this a run real quick.
-
And there we go.
-
We have back just JavaScript there.
-
But in addition to that,
-
we can also chain off of an additional or where
-
to say ID of four.
-
And now we should get back to the JavaScript and TypeScript
-
as we do here,
-
because it's wrapping everything
-
inside of this callback function
-
for our SQL inside of parentheses.
-
So essentially the underlying query
-
that we're running here is where slug ends with script
-
and where ID is two or four.
-
Obviously for this specific query,
-
there's much cleaner ways that we can go about it,
-
but this gives you an idea
-
of how you can go about nesting your queries
-
to add in additional and or statements
-
on top of one another.
-
Introduction
-
Routing
-
2.0Routing Introduction6m 14s
-
2.1Dynamic Routing with Route Parameters14m 1s
-
2.2Moving & Organizing Routes4m 19s
-
2.3Naming, Grouping, & Prefixing Routes8m 28s
-
2.4Multi-File Route Grouping Strategies5m 31s
-
2.5Generating URLs and Signed URLs13m 50s
-
2.6Extending the Adonis Router and Route Matchers15m 44s
-
2.7The Middleware Mountain12m 44s
-
-
Controllers
-
Service Providers
-
Lucid ORM
-
5.0Introducing, Installing, and Configuring Lucid ORM10m 0s
-
5.1Migrations & Understanding the Flow of Migrations16m 35s
-
5.2What To Know Before Altering Your Database with Migrations10m 26s
-
5.3What Is A Model?12m 44s
-
5.4Database Schema to Migration11m 56s
-
5.5Database Migrations To Lucid Models4m 59s
-
5.6Defining Model Relationships13m 4s
-
5.7Creating Records with Lucid ORM12m 38s
-
5.8Password Hashing & Model Hook Overview8m 42s
-
5.9Easy Querying with Static Model Query Methods7m 41s
-
-
Query Builder
Join The Discussion! (0 Comments)
Please sign in or sign up for free to join in on the dicussion.
Be the first to Comment!