Querying with Lucid

The all and find methods from the previous lesson cover simple cases. For queries with conditions every model has a query builder: the query is built with a chain of methods and runs on await.

Conditions and ordering

// app/controllers/posts_controller.ts
import Post from '#models/post'
 
const posts = await Post.query()
  .where('is_published', true)
  .orderBy('created_at', 'desc')
  .limit(10)

Post.query() starts a query on the posts table. The result is an array of models: the ten latest published posts. Note that the builder methods take database column names (is_published), not model properties.

Variants of where

Conditions written in a row are combined with AND.

A single record

// app/controllers/posts_controller.ts
const post = await Post.query().where('slug', 'hello-adonis').firstOrFail()

first returns the first matching model or null, while firstOrFail throws an exception when there is no record, and the visitor sees a 404.

Searching the blog

Let us build an index method that searches posts by a string from the address /posts?q=...:

// app/controllers/posts_controller.ts
import type { HttpContext } from '@adonisjs/core/http'
import Post from '#models/post'
 
export default class PostsController {
  async index({ request, view }: HttpContext) {
    const q = request.input('q', '')
 
    const posts = await Post.query()
      .where('is_published', true)
      .if(q, (query) => query.whereLike('title', `%${q}%`))
      .orderBy('created_at', 'desc')
 
    return view.render('pages/posts/index', { posts, q })
  }
}

The if method adds a condition only when its first argument is truthy. Open /posts?q=adonis — only posts with "adonis" in the title remain.

Pagination

// app/controllers/posts_controller.ts
const page = request.input('page', 1)
const posts = await Post.query().orderBy('created_at', 'desc').paginate(page, 10)
posts.baseUrl('/posts')

paginate returns an object with the current page of posts and information about the pages. In a template you can loop over it and print links:

{{-- resources/views/pages/posts/index.edge --}}
@each(post in posts)
  @!post.card({ post })
@end
 
@each(anchor in posts.getUrlsForRange(1, posts.lastPage))
  <a href="{{ anchor.url }}">{{ anchor.page }}</a>
@end

Under the list you get links /posts?page=1, /posts?page=2 and so on.

Counting and queries without a model

// app/controllers/posts_controller.ts
import db from '@adonisjs/lucid/services/db'
 
const total = await db.from('posts').where('is_published', true).count('* as total')
console.log(total[0].total)

The db service builds queries against any table and returns plain objects, not models. This is handy for counting and reports.

Common mistake

Without await the query does not run: const posts = Post.query().where(...) gives you a builder object, not an array of posts, and @each in the template prints nothing. Finish every database query with await.