orderBy on relationalship


#1

my post model

class Post extends Model {
  user() {
    return this.belongsTo("App/Models/User")
  }
}

i have query like this

 const posts = await use(`App/Models/Post`)
      .query()
      .select(['id','user_id','post_title'])
      .with("user", builder => {
        return builder
        .select(["id", "username"])
      })
      .limit(3)
      .fetch()

the result is

[{
"id": 1,
"user_id": 12,
"post_title": "Ahi tekosir famrij lamdapup uc evotow hefamwon hibfupida gulepuh to revama vob kehaczep et ladubti wijuek.",
"user": {
   "username": "andof",
   "id": 12
 }
},
{
"id": 2,
"user_id": 5,
"post_title": "Fudmop kogon bezeap al zogmun lilifzet ipubavi agouv heeg pi uhudotu ci cidamne ob puv gen.",
"user": {
   "username": "nutik",
   "id": 5
 }
},
{
"id": 3,
"user_id": 8,
"post_title": "Vubotej ujcisopu durmezfub comjiir hul ko ugimir nev moluj umleeg an deumujak umiwilor gimzefir.",
"user": {
     "username": "usecu",
     "id": 8
 }
}]

my questions how to sort order the results by username desc using relationalship, so the post with username “usecu” in top of results.

I try to achive this using relationalship but failed

const posts = await use(`App/Models/Post`)
      .query()
      .select(['id','user_id','post_title'])
      .with("user", builder => {
        return builder
        .select(["id", "username"])
      })
      .orderBy('user.username','desc')
      .limit(3)
      .fetch()

#or this
 const posts = await use(`App/Models/Post`)
      .query()
      .select(['id','user_id','post_title'])
      .with("user", builder => {
        return builder
        .select(["id", "username"])
        .orderBy('username','desc')
      })
      .limit(3)
      .fetch()

# or this 
 const posts = await use(`App/Models/Post`)
      .query()
      .select(['id','user_id','post_title'])
      .with("user", builder => {
        return builder
        .select(["id", "username"])
      })
      .whereHas("user", builder => {
        return builder
        .orderBy('user','desc')
      })
      .limit(3)
      .fetch()

is it only work with database? Im trying to achive the query using relationalship

const posts = Database.select('p.post_title','u.username')
    .from('posts as p')
    .leftJoin('users as u','p.user_id','u.id')
    .orderBy('u.username','desc')
    .limit(3)

Package version

@adonisjs/ace”: “^5.0.4”,
@adonisjs/auth”: “^3.0.6”,
@adonisjs/bodyparser”: “^2.0.4”,
@adonisjs/cors”: “^1.0.6”,
@adonisjs/fold”: “^4.0.9”,
@adonisjs/framework”: “^5.0.8”,
@adonisjs/ignitor”: “^2.0.7”,
@adonisjs/lucid”: “^6.0.1”,
@adonisjs/mail”: “^3.0.8”,
@adonisjs/session”: “^1.0.26”,
@adonisjs/shield”: “^1.0.7”,
@adonisjs/validator”: “^5.0.3”


#2

solved, I need leftJoin first to sort all posts by username

 const posts = await use(`App/Models/Post`)
      .query()
      .select(['id','user_id','post_title'])
      .with("user", builder => {
        return builder
        .select(["id", "username"])
      })
        .leftJoin('users as u','posts.user_id','u.id')
       .orderBy('u.username','desc')
      .limit(3)
      .fetch()