How to order Lucid Models results based on relation?

Question

What I want is to order all Service model results based on a relation (that belongsTo the same ServiceCategory) like Laravel does, something like this:

const services = await Service
  .query()
  .orderBy('categories.id', 'desc')
  .fetch()

The problem is that Lucid doesn't understand it and gives the following error:

select * from services order by categories.id asc - ER_BAD_FIELD_ERROR: Unknown column 'categories.id' in 'order clause'

This is the Service model:

const Model = use('Model')

class Service extends Model {
  categories() {
    return this
      .belongsToMany('App/Models/ServiceCategory')
      .withTimestamps()
  }
}

module.exports = Service

This is the ServiceCategory model:

const Model = use('Model')

class ServiceCategory extends Model {
  services() {
    return this
      .belongsToMany('App/Models/Service')
      .withTimestamps()
  }
}

module.exports = ServiceCategory

How can I make it work?

Answer 1

Try to use with()

const services = await Service
  .query()
  .with('categories')
  .orderBy('categories.id', 'desc')
  .fetch()

if don't work, use innerJoin