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.

Created by: @tomgobich
Published: 2 years ago

Join the Discussion 0 comments

Be the first to comment!

Transcript

- 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.

Let's Learn AdonisJS 5

29 Lessons

5h 3m

Introduction

Routing

Controllers

Service Providers

Service Providers & The IoC Container

18m 45s

Lucid ORM

Query Builder

Stop losing your best ideas!

Use our integrated note feature to capture and organize your key insights. Take a note at any timestamp and jump instantly back to that moment in the video with a single click.