Query builders allow us to build everything from simple to complex queries consisting of where clauses, selects, joins, and more. Additionally, as we'll see in the next lesson, they also allow us to integrate with our model's relationships.
They allow us to build these queries using an easy-to-read method chain in TypeScript. Lucid then takes the query we've built, translates it to SQL, executes it, and returns our results to us.
There are two different, high-level, types of query builders:
Database query builder (select/insert)
The model query builder uses our models as a translation layer between our query and the database. Converting, for example, camel case property usage in our queries to the appropriate snake case column name on our table. They also allow us to use our model relationships as well. Unless otherwise specified, the model query builder will always return an instance of the model as well.
The database query builder is a more raw query builder that doesn't integrate with our models at all. Instead, it goes directly to the database. It also requires manual typing if you wish for it to have a type other than any.
Building Queries
Let's start simple by replacing the Challenges.all() query from our ChallengesController.index method to instead use our Challenge model's query builder. To start a query builder, all we need to do is call the query() method on our model.
export default class ChallengesController { /** * Display a list of resource */ async index({ view }: HttpContext) { const list = await Challenge.query() return view.render('pages/challenges/index', { challenges: list }) } // ... }Copied!
- app
- controllers
- challenges_controller.ts
What we've just done is a one-for-one swap, as both Challenge.all() and Challenge.query() will return all records in our challenges table. However, unlike all(), we can chain additional methods off of query() to alter our results.
For example, if we wanted to limit our challenges to those with a point value higher than 50, we could add a where() method. This, in turn, adds a WHERE clause to the SQL our builder will generate and execute.
export default class ChallengesController { /** * Display a list of resource */ async index({ view }: HttpContext) { const list = await Challenge.query().where('points', '>', 50) return view.render('pages/challenges/index', { challenges: list }) } // ... }Copied!
- app
- controllers
- challenges_controller.ts
The first argument is the column/property name, the second argument is the comparison operator, and the third is the comparison value. This will add a WHERE points > 50 clause to the underlying SQL that'll be executed, limiting the returned challenges that we can see reflected on our page. We can also sort our results as well! Let's sort our challenges by their text.
export default class ChallengesController { /** * Display a list of resource */ async index({ view }: HttpContext) { const list = await Challenge.query().where('points', '>', 50).orderBy('text') return view.render('pages/challenges/index', { challenges: list }) } // ... }Copied!
- app
- controllers
- challenges_controller.ts
The default sort direction will be ascending (A-Z). We can switch this to descending (Z-A) if we prefer, though.
export default class ChallengesController { /** * Display a list of resource */ async index({ view }: HttpContext) { const list = await Challenge.query().where('points', '>', 50).orderBy('text', 'desc') return view.render('pages/challenges/index', { challenges: list }) } // ... }Copied!
- app
- controllers
- challenges_controller.ts
Let's filter a little more, by adding an additional where() method to our query, we're saying to get challenges where the points are greater than 50, and the text is whatever the first item on our page is, like "input solid-state feed."
export default class ChallengesController { /** * Display a list of resource */ async index({ view }: HttpContext) { const list = await Challenge.query() .where('points', '>', 50) .where('text', 'input solid-state feed') .orderBy('text', 'desc') return view.render('pages/challenges/index', { challenges: list }) } // ... }Copied!
- app
- controllers
- challenges_controller.ts
With the where() method, when we only provide two arguments, column and value, an equality check will be performed by default. Note, similar to the findBy() method, this can also be an object with key/value pairs if all you need are equality checks.
export default class ChallengesController { /** * Display a list of resource */ async index({ view }: HttpContext) { const list = await Challenge.query() .where('points', '>', 50) .where({ text: 'input solid-state feed' }) .orderBy('text', 'desc') return view.render('pages/challenges/index', { challenges: list }) } // ... }Copied!
- app
- controllers
- challenges_controller.ts
If we need a likeness check, we can pass LIKE or ILIKE as the comparison operator and add the percent sign (%) as needed to our value.
export default class ChallengesController { /** * Display a list of resource */ async index({ view }: HttpContext) { const list = await Challenge.query() .where('points', '>', 50) .where('text', 'LIKE', 'input%') .orderBy('text', 'desc') return view.render('pages/challenges/index', { challenges: list }) } // ... }Copied!
- app
- controllers
- challenges_controller.ts
This will give us all challenges where the points are greater than 50, and the text starts with "input", for example. So far, we've been doing AND WHERE statements, but what if we need OR WHERE? Well, there's an orWhere() method just for that, and it works just like where(), changing just the comparison type.
export default class ChallengesController { /** * Display a list of resource */ async index({ view }: HttpContext) { const list = await Challenge.query() .where('points', '>', 50) .orWhere('text', 'LIKE', 'input%') .orderBy('text', 'desc') return view.render('pages/challenges/index', { challenges: list }) } // ... }Copied!
- app
- controllers
- challenges_controller.ts
So, this will now give us challenges with points greater than 50 or text starting with input. By the way, if you prefer the explicitness of the orWhere() and would prefer to have that with and comparisons, there is also an andWhere() you can use as an alternative.
Okay, what about mixing and/or statements? For this, the where() methods can also accept a callback function that is provided a nested version of the query builder. This nested version will be translated to SQL wrapped in parentheses, allowing us to build more complex where clauses.
export default class ChallengesController { /** * Display a list of resource */ async index({ view }: HttpContext) { const list = await Challenge.query() .where('points', '>', 50) .where((query) => query .where('text', 'LIKE', 'input%') .orWhere('text', 'LIKE', 'index%') ) .orderBy('text', 'desc') return view.render('pages/challenges/index', { challenges: list }) } // ... }Copied!
- app
- controllers
- challenges_controller.ts
Now, we'll get challenges with points greater than 50 and having a text value starting with input or index. In turn, generating SQL like:
SELECT * FROM challenges WHERE points > 50 AND ( text LIKE 'input%' OR text LIKE 'index%' )Copied!
We aren't using the createdAt nor updatedAt at the moment, so if we wanted to save a little, we could omit those columns from our query using the select() method.
export default class ChallengesController { /** * Display a list of resource */ async index({ view }: HttpContext) { const list = await Challenge.query() .where('points', '>', 50) .where((query) => query .where('text', 'LIKE', 'input%') .orWhere('text', 'LIKE', 'index%') ) .select('id', 'text', 'points') .orderBy('text', 'desc') return view.render('pages/challenges/index', { challenges: list }) } // ... }Copied!
- app
- controllers
- challenges_controller.ts
This will still return an instance of our model, but will now only have the id, text, and points properties populated. Also, unlike SQL, note that it doesn't matter what order we add our query methods in. In SQL, the SELECT comes first, but we can put that after our where clauses as we have above with the query builder. It won't impact the SQL it ultimately generates.
If we need to sort by more than one column, we can do that as well by passing an array into the orderBy() method. All we need to do is specify the column and order for each item we'd like to sort by. It'll then sort in the order of the items in our array.
export default class ChallengesController { /** * Display a list of resource */ async index({ view }: HttpContext) { const list = await Challenge.query() .where('points', '>', 50) .where((query) => query .where('text', 'LIKE', 'input%') .orWhere('text', 'LIKE', 'index%') ) .select('id', 'text', 'points') .orderBy([ { column: 'text', order: 'desc' }, { column: 'points', order: 'asc' }, ]) return view.render('pages/challenges/index', { challenges: list }) } // ... }Copied!
- app
- controllers
- challenges_controller.ts
This will now sort by our text in descending order, then by our points in ascending order.
Joins
Joins allow us to include another table's records in our query via our relationship columns. Note, these are not the model relationships we'll be discussing in the next lesson, but rather a direct query statement being added into our generated SQL. In most cases, model relationships will eliminate the need to use joins altogether.
To add a join, we can use the join() method. This accepts the table we want to join, followed by the columns we want to compare to match the relationship rows. For example, in a join, we want to map challenges.creator_id to users.id. Joins are lower-level and aren't model away, so we do need to use the column names from our table. Once joined, we can use columns as needed from the joined table.
export default class ChallengesController { /** * Display a list of resource */ async index({ view }: HttpContext) { const list = await Challenge.query() .where('points', '>', 50) .where((query) => query .where('text', 'LIKE', 'input%') .orWhere('text', 'LIKE', 'index%') ) .join('users', 'users.id', 'challenges.creator_id') .select('challenges.id', 'text', 'points', 'users.full_name') .orderBy('users.full_name') return view.render('pages/challenges/index', { challenges: list }) } // ... }Copied!
- app
- controllers
- challenges_controller.ts
Here we're joining our users table with our challenges table using a comparison between the users.id and the challenges.creator_id. We're selecting and ordering by the creator's full_name from our users table. I've prefixed full_name with users.full_name, not because it needs to be, but rather because it makes it clear that the column is coming from a join. It'll make it easier to know the column needs to be removed if the join is ever removed.
I am, however, prefixing our select's id with challenges.id for a very specific reason. Both our users and challenges table have an id column. Since both id columns are now present within our query, we need to be explicit about which we're referencing. Without it, we'd get an ambiguous error as SQL wouldn't know whether to use challenges.id or users.id.
Despite our joining in our users table and selecting the users.full_name column, we will still get back instances of our Challenge model for our result. Lucid will populate the Challenge model's columns as usual, and put anything extra from the query that's been selected into an $extras object.
So, if we were to console log the $extras from each of our models, we'd see the creator name for each of our challenges!
export default class ChallengesController { /** * Display a list of resource */ async index({ view }: HttpContext) { const list = await Challenge.query() .where('points', '>', 50) .where((query) => query .where('text', 'LIKE', 'input%') .orWhere('text', 'LIKE', 'index%') ) .join('users', 'users.id', 'challenges.creator_id') .select('challenges.id', 'text', 'points', 'users.full_name') .orderBy('users.full_name') console.log({ extras: list.map((item) => item.$extras) }) return view.render('pages/challenges/index', { challenges: list }) } // ... }Copied!
- app
- controllers
- challenges_controller.ts
If you want to include the creator on each challenge or filter by the creator, we'll see a much cleaner way to do that using model relationships in the next lesson.
Limiting Results
What if we're after a specific number of records? For example, in our ChallengesController.show method, we only want one result, so how could we do that using the query builder? First, let's replace our Challenge.findBy() with our query builder.
export default class ChallengesController { // ... /** * Show individual record */ async show({ view, params }: HttpContext) { const challenge = await Challenge.query().where('id', params.id) const editUrl = urlFor('challenges.edit', { id: params.id }) return view.render('pages/challenges/show', { challenge, editUrl }) } // ... }Copied!
- app
- controllers
- challenges_controller.ts
Then, for starters, to restrict the number of results, we can use the query builder's limit() method. This takes in the number of records we want to get back, and it will query for the first X amount up to our limit. So, to get just one, we could use limit(1).
export default class ChallengesController { // ... /** * Show individual record */ async show({ view, params }: HttpContext) { const challenge = await Challenge.query().where('id', params.id).limit(1) const editUrl = urlFor('challenges.edit', { id: params.id }) return view.render('pages/challenges/show', { challenge, editUrl }) } // ... }Copied!
- app
- controllers
- challenges_controller.ts
However, limit() will still return an array. If we're after just one result, we have better options, like the first() method. This will apply a limit of one and give us our model instance back directly, instead of it being wrapped in an array!
export default class ChallengesController { // ... /** * Show individual record */ async show({ view, params }: HttpContext) { const challenge = await Challenge.query().where('id', params.id).first() const editUrl = urlFor('challenges.edit', { id: params.id }) return view.render('pages/challenges/show', { challenge, editUrl }) } // ... }Copied!
- app
- controllers
- challenges_controller.ts
Similar to our static model method, there is also firstOrFail(). This will attempt to find the first result, and if it can't, will throw a 404 Not Found exception. This is more appropriate for our ChallengesController.show as if a challenge can't be found, then there's nothing to show.
export default class ChallengesController { // ... /** * Show individual record */ async show({ view, params }: HttpContext) { const challenge = await Challenge.query().where('id', params.id).firstOrFail() const editUrl = urlFor('challenges.edit', { id: params.id }) return view.render('pages/challenges/show', { challenge, editUrl }) } // ... }Copied!
- app
- controllers
- challenges_controller.ts
Building with the Builder
Finally, we don't need to directly chain methods consecutively with the query builder. In fact, the query won't actually execute as SQL until it is awaited or we call a terminating method, like firstOrFail(). This gives us the ability to systematically build on the query as needed. A simple example, but the following gives you a sense of what I mean.
export default class ChallengesController { // ... /** * Show individual record */ async show({ view, params }: HttpContext) { // start a query builder const query = Challenge.query() // add statements as needed query.where('id', params.id) // firstOrFail is terminating, meaning we can't chain further on it // it'll return Promise<Challenge> const challenge = await query.firstOrFail() const editUrl = urlFor('challenges.edit', { id: params.id }) return view.render('pages/challenges/show', { challenge, editUrl }) } // ... }Copied!
- app
- controllers
- challenges_controller.ts
Let's move to our index method for a more detailed look.
export default class ChallengesController { /** * Display a list of resource */ async index({ view }: HttpContext) { // start a query builder const query = Challenge.query() // add or chain statements as needed query.where('points', '>', 50) query.where((q) => q.where('text', 'LIKE', 'input%').orWhere('text', 'LIKE', 'index%')) query.join('users', 'users.id', 'challenges.creator_id') query.select('challenges.id', 'text', 'points', 'users.full_name') query.orderBy('users.full_name') // await the query to execute const challenges = await query return view.render('pages/challenges/index', { challenges }) } // ... }Copied!
- app
- controllers
- challenges_controller.ts
Here we're getting the query builder and adding our statements to it individually, one at a time. Though these statements return back our query builder, we don't need to overwrite query, they'll be internally applied to the query we're building. Finally, we aren't using any terminating statements, so the query is translated to SQL and executed only when we await the query.
Okay - I'm going to undo all of that to get us back to where we were.
export default class ChallengesController { /** * Display a list of resource */ async index({ view }: HttpContext) { const challenges = await Challenge.query() .where('points', '>', 50) .where((query) => query .where('text', 'LIKE', 'input%') .orWhere('text', 'LIKE', 'index%') ) .join('users', 'users.id', 'challenges.creator_id') .select('challenges.id', 'text', 'points', 'users.full_name') .orderBy('users.full_name') return view.render('pages/challenges/index', { challenges }) } // ... /** * Show individual record */ async show({ view, params }: HttpContext) { const challenge = await Challenge.query().where('id', params.id).firstOrFail() const editUrl = urlFor('challenges.edit', { id: params.id }) return view.render('pages/challenges/show', { challenge, editUrl }) } // ... }Copied!
- app
- controllers
- challenges_controller.ts
