how to work on order by relationship column in adonisJs v5?

[mdjamilaj]

I have implement the following query in adonisjs v4 :

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

It's work fine.

how to implement the relation column order_by in adonis v5 preload() relation.

[tsulatsitamim]

do you need order services or categories in each services?

[mdjamilaj]

@tsulatsitamim i having try to show service list orderBy depend on categories list id desc.

[tsulatsitamim]

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

[tomgobich]

The second argument of preload is a callback function that's provided the query builder for the relationship:
https://docs.adonisjs.com/guides/models/relationships#preload-relationship

So, with that in mind, you can do something like the following:

const services = await Service
  .query()
  .preload('categories', query => query.orderBy('id', 'desc')) // <- anything done in the callback will apply to the categories query.

[mdjamilaj]

@tomgobich your solution only sort categories. but I am trying to sort services Descending order depend relationship categories id

you can see here. In adonisjs v4 it can be easily done.

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

but with adonisjs v5 I am kind of stack here.

[thetutlage]

I don't think the code you shared can work with v4 as well. What you need is to sort after join. The following is a rough untested example.

const services = await Service
  .query()
  .preload('categories')
  .join('categories', 'categories.id', 'services.category_id')
  .orderBy('categories.id', 'desc')
  .select('services.*')