Querying Our Movies with the Query Builder

In This Lesson

We'll introduce both the database and model query builder. We'll learn the differences between the two and the basics of how to use them.

Created by: @tomgobich
Published: 1 year ago

Join the Discussion 2 comments

@cortesbr
10 months ago

Hi! I noticed that in your tutorial, you're able to directly use the movie objects returned from queries, but in my Adonis.js v6 project, I had to use .toObject() to handle the Lucid ORM proxy objects. Is this because the tutorial was written for an earlier version of Adonis? Could you clarify this difference in behavior between versions?

1
1. Responding to cortesbr
  
  @tomgobich
  9 months ago
  
  Hi cortesbr! You shouldn't need to call toObject() to access the model's properties; you should be able to directly reference them via movie.id, for example. Please make sure you're using at least NodeJS v20. If you are using NodeJS v20 or later, please feel free to share your repository and I can see if anything stands out.
  
  0

Transcript

So now that we have both our start seeder and fake seeder defined and we've executed them, we actually have data inside of our database that we can work with now. So let's go ahead and boot up our server here,
jump into our terminal and run npm run dev. Let's jump into our browser and take a look at that. We have three movies showing up here. Love Hangover, which I'm not sure if you ever could,
Streets of Philadelphia and Smells Like Teen Spirit. Remember within Faker, we're using song lyrics for the titles for our movies here. And then just some warm text here for the summary.
If we click into one of these, we see the actual movie's details. So here's the abstract, the title. And then within our URL, we're currently using a UUID
in place of our human legible slug with our factory. So everything appears to be working A-OK. I also got rid of our two links right up here
as they're no longer applicable because we're no longer going to be working with our locally hosted movies. OK, so let's hide our browser back away and let's get back to our text editor here. And let's jump back into our movies controller.
So right here is where all of that magic is happening. Whenever we're viewing our home page, we're getting all of our movies back via the movie.all method, which will query all of our movies.
And whenever we click into the movie show page, that's where we're running our find by or find by or fail method to get back the movie detail results.
Remember that find by and find by or fail will run the same thing. The only difference is find by will not error out, but instead return back null if it cannot find a result,
as noted by the type right up here. Whereas find by or fail will fail with a 404 if it cannot find a result, meaning that the movie is not nullable as an error will occur if it cannot find it.
Now, the static query methods that we've been using so far are fantastic for simplistic use cases to getting to your data. But there's going to be more complex scenarios
where you need to maybe even filter down your data using dynamic filters to get to the underlying data that you want to display to your users. And that's where the model query builder
or just a query builder in general comes into play. So if we dive into our browser here, let's open up the Lucid documentation. So that's lucid.adonisjs.com.
And there is a query builder section right here. There's one for selecting data, inserting data, and then raw queries as well. We'll be focusing on the select query builder here today.
And what they're showing us here is the database modules query builder. We haven't talked about the database module quite yet, but essentially it's raw access going outside the realms of our models
to your database. For example, if we do db.from users, we would be running a query against our users table.
And the results that we would get back would be the column names and all of its data as it's defined inside of our database, rather than how it's defined inside of our model.
For example, we can open up a REPL session. So let's stop our server, node, ace, REPL. We'll do .ls to see our available commands.
Okay, so we see we have load models as well as load db. This db is the database module that we're taking a look at right here.
So if we go ahead and await load db, and while we're here, we'll also await load models. So let's compare the two results just using our movies.
So let's do await db, as we can see we have available right here by loading our db, .from to query from our movies.
And I will do .first just so that we get back a single result here. We'll hit enter, and you see that we get back our movies as it's defined inside of our database
using snake case for our columns as our database structure is doing. However, if we do await models.movie,
we can do .query to access the models query builder, and we'll just run first there as well. And just as we were working with before, you'll see that we get back an instance of our model.
The underlying attributes that we have at our disposal are as defined inside of our model. So they're using camel case rather than snake case as our database is using.
And we also have all of the fun attributes that Lucid uses to keep track of changes, transactions, and all of that fun stuff with our model as well. So whenever we mentioned the db module,
that's kind of raw access to our database going outside the realms of our models. And whenever we mentioned the model query builder or models in general,
that's going to work with model instances and the like. Now, while we're here, there is a method on the model query builder that will allow us to get to that raw database information,
and that's called Poyo. So if we scroll down, hit up to go to our last REPL command and add in .pojo to the end of our query builder, hit enter on that.
Oh, whoops, we need to run that before our first. So let's go back over to here, .poyo, hit enter there. There we go. You'll see that although now we're using
our model query builder, this Poyo command is giving us raw access to the results as it would be defined inside of our database. So we're all back to Snake Case. Cool. So now we know the difference between the two query builders,
and we know how we can get back to the raw database query results using the model query builder. Let's continue onward with taking a look at the query builder as a whole. So here on the right hand side, we see all of the different methods
that we're going to have at our disposal using the query builder as a whole. Primarily, the only difference and exclusion here is the from method whenever we're working with the model query builder,
because it's working specifically with a model. So it already knows where we want to run the query from. Remember, the from method on the database query builder is just specifying the table that we want to run the query against.
That's primarily going to be the main difference between the two whenever you're looking at this select query builder page. Everything else we should have at our disposal on both the query builders here.
And you're going to see it's a lot of different methods that we can use to actually build out queries. It's just going to keep going on and on and on. And the query builder that AdonisJS uses
is actually built on top of Connects.js. So if we head over to the Connects.js documentation, go into the view guide, we can scroll down to the query builder section here.
And this documentation here should for the most part be applicable to the AdonisJS query builder as well, since it's built on top of this query builder.
So you do have these two different documentations at your disposal should you need them. For the most part, I would reach for the Lucid one first, since it will have some variations from the Connects.js documentation
in terms of its application. So I'm going to go ahead and hide that back away. And there's a lot of different methods that we could do. Most of these are going to be outside the realm of your typical use case,
but some of them you will need for almost every single query. And that's where statements, order bys, limitations. Before we jump into that, let's scroll down a little bit more to the model section
inside the documentation. There's a query builder section here as well. This specifies methods that the model query builder has that the database query builder does not.
For the most part, these are going to be relationship based. So preload with count with aggregates has, where has, side loaded. Those are all going to be relationship based.
With scopes and apply are specific to scopes, which are outside the realm of this particular lesson. And we've already talked about Poyo. And then we have paginate,
which will allow us to get back paginated results. And we can access a reference to the model as well. Okay, cool. So let's hide that back away. And inside of a REPL session here,
I'm just going to go ahead and hide our text editor back away there too. Let's learn how we can use where statements to filter down on the data that we're querying. So we can do await models, movie dot query
to access the model query builder. And we previously ran the first. So we just grabbed the first result out of the database. Instead, let's do where to specify a where clause.
And we can specify a column name as the first argument here. So we could do title. And then we can do one of two things here. We can either specify three arguments or two arguments as a whole.
If we specify three, the middle argument is going to be the comparison logic. By default, if we specify just two arguments, that'll be an equals check. So we'll do equals there.
And then we'll provide in the value as the third argument. So we'll do smells like teen spirit. We can hit enter here to run that query.
And we're going to get back a proxied result of our model instance, which is going to wrap up our attributes. So we can actually see the underlying results that we got back.
For that reason, we'll end this with our Poyo, which will return back the data as we're seeing inside of the database. So I enter there and there we go. Okay, cool.
So we do indeed get back our smells like teen spirit result. And if we remove that third argument, we'll see that we get back the exact same thing
because it will default to just doing an equals check. There we go. But maybe we want to do a likeness check instead. So we could provide that in place as our middle argument there.
And then maybe we could do where the title starts with smells. So we'll take this back, hit percent there to say anything can be after smells.
We'll run this and we get back the exact same result. We could try switching this to something different. So maybe love as I think we had love hangover as a result. Yep, there we go.
And we get back love hangover as a result there because it starts with love. We could do something similar. So we could do where created at is greater than
or you could do equals to if you wanted to 2024, 02. And maybe we'll do 24, which is a day before these results were created.
We hit enter there and we get back all three of our results because we created all three movies on the same day.
If however, we switch this to 26 a day after they were created, we'll get back no results. But there's different variants to the where statement here as well.
So if we go back up a couple of steps to where we were doing our where like, instead of providing three arguments to get to the like, we could provide two and instead use the where like method,
where now the default comparison logic will be a like statement. So if we run this, we'll get back our result as expected.
And there is also a where I like to do a case insensitive likeness check. So for example, if we switch our love to a lowercase L,
hit enter there, we still get back a result. But if we get rid of that I and the where I like, we won't get back a result because there's no capitalization matches for the love check here.
If we dive back into the loose documentation, scroll back up to the select query builder statements, there is a bunch of different variations of the where statement.
So you have where I like, where like, where, where column, where in, where null, where exists, where between, where raw, where JSON.
And on top of each and every single one of those, there is also an and and an or variation. So for example, if we wanted to do where like title,
we'll switch this back to a capital L so that we actually have a match. Starts with love and the status ID is one. We could add in an and where,
specify the column as a status ID and the value as one, run that. And now we're going to get back results where the title starts with love and where the status ID is one.
Now, the default behavior, whenever you chain multiple where statements together is going to be to do an and check. So we could just run where here again,
and it will be the exact same thing as our results before, but there's also an or variation as well. So we could do or where status ID is one.
All of our movies currently have a status ID of one. So because of this, we're going to get back all three movies as our results here, because they all have a status ID of one.
And we ran where title starts with love or where the status ID is one. If we want to just two results back, we could limit the results. So let's go back to our previous query.
Dot limit to, to limit the results to just two. So if we hit enter there, now we just have two results that came back. The third one was omitted and we can order these as well.
So we can do order by, maybe created at, we can either do ascending or ASC, which will be the default sort,
or we can do descending to go in the inverse. So ascending is going to be an alphabetical order. It's going to be A to Z, one to 100, the first to the 30th.
Descending would then be the inverse order. So it would be Z to A, 100 to one and the 30th to the first. So if we go ahead and run this in descending order,
they were all created in almost the exact same time. We look at the very last character here, we're going to see a very slight difference. The first result that we got back was dot 761 Zulu time.
And the second result that we limited to was dot 760. So very, very slight difference, but it is indeed going in descending order.
If we switch this now to ascending, so ASC, or that'll be default so we can get rid of that argument there altogether. Hit enter. A little bit bigger of a difference here.
We can see we have 754 Zulu time as our first argument, and then it's going upwards to 760 Zulu time. So now we're going in ascending order based off of our created at.
So there's just way too much to be able to cover everything with these query builders. But there's a couple of things that I do want to touch on because they're very useful.
So first, you can pass a callback argument to your where statement, and that will essentially wrap that statement in parentheses in terms of the underlying SQL that's generated.
For example, we can await models dot movie dot query to get our query builder for a movie dot where.
Let's say status ID is one, which matches all of our results. We can do an additional where statement to say,
and pass in a callback function to now do query dot where within parentheses here.
And we can now do ID is one or where ID is two. So if we run this, we didn't add Poyo on there. So we're getting back. Let's do that real quick.
So Poyo so that we can actually see the objects. Run that. Now we get back two results where the ID is one and two. All three of our movies have a status ID of one.
So all three match this result. But now we have a separate parenthesized statement where the movie has an ID of one or two.
If we run that again, but change our first where statement slightly to something that just one of these results matches. So maybe where the title starts with streets.
So we can do where like switch our column to title and our value to streets with a percent sign afterwards to say that anything can come after streets.
Run this. We'll just get back one result where the title starts with streets and the ID is either one or two. And we're getting back just the one result here because the callback function
that we're providing to the where statement here is wrapping our where and our or where in parentheses. So it needs to match one of these two and our where like,
which is different than if we just did wait models, movie query,
where like title starts with streets, where ID is one or where ID is two.
Actually, let's switch the or where there, make it a little bit more evident where ID is two or one. And we'll do OYO so that we can actually see the results. Run that. And now you see that we get back both results
because this statement is different than this one. This one's wrapping this where and or where for our ID check in parentheses.
So it needs to match either ID of one or two and a title that starts with streets. Whereas this one has our second where statement outside of parentheses.
So it needs to have a title starting with streets and an ID of two or just an ID of one. So you can see how the context kind of changes there depending on whether or not
we're providing our where statement inside of a callback, which will wrap it in parentheses or just as a straight chain.
Additionally, we can do an if check to conditionally apply a where as well. So let's do let title filter equal an empty string.
We'll define that. So now if we print out title filter, we'll get back an empty string. Cool. So we can do a wait models, movie dot query to access the query builder.
And instead of just doing where title has the value of our title filter, which will return back in the results because we don't have any titles that are an empty string,
we can instead provide this where statement inside of an if check. So we do if title filter provide the second argument as a callback function query, just like so.
With this if check, if title filter is truthy, it will run the first argument that we have that callback.
Otherwise, we can add in an else statement as well as a third argument that's also a callback to do query dot where something else.
Don't really have anything to do an else for in this particular use case. So we'll run this and you'll see that we get back all three of our results.
But if we switch title filter to equal love hangover, which is one of our movies, and we try to run that query again, you'll see that we just get back love hangover.
We can't see it, but if we add Poyo to the end of that, we'll be able to see that we do indeed just get back love hangover.

Let's Learn AdonisJS 6

113 Lessons

14h 15m

Introduction

Fundamentals

Building Views with EdgeJS

Database and Lucid ORM Basics

Lucid ORM Relationships

Working With Forms

Authentication & Middleware

Filtering and Paginating Queries

User Watchlist

User Profiles

Admin Panel

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.