How i can use a orderBy passing a column of the relationship in Lucid Models?

I have two tables: BookUnit and ClassBookHistoric.

How i can order by this query by the value of the column book_unit.unit?

I try something like:

const queryClasseLivroHistorico = ClasseLivroHistorico
      .query()
      .where('class_id', request.params.class_id)
      .where('book_id', request.params.book_id)
      .with('user')
      .with('book_unit')
      .orderBy('book_unit.unit','book_unit_sequence', 'desc')

There’s a way to do this using Lucid Models?

I’m receiving this error:

select * from "class_book_historics" where "class_id" = $1 and "book_id" = $2 order by "book_unit"."unit" asc limit $3 - missing FROM-clause entry for table "book_unit

In ClassBookHistoric i define this relationship:

class ClassBookHistoric extends Model {

    static get table () {
        return 'class_book_historics'
    }

    book_unit () {
        return this.belongsTo('App/Models/BookUnit')
    }

    user () {
        return this.belongsTo('App/Models/User')
            .select('id', 'username')
    }

}
1 Like

Hey, you should use joins in order to ordering with related table.

For example, let assume we have to related table which are users and user_posts;

await User
      .query()
      .select('users.*')
      .leftJoin('user_posts', 'user_posts.user_id', 'users.id')
      .with('posts')
      .orderBy('user_posts.title', 'desc')

I guess unless you join the related table, you can’t order by related table.

Please be careful to select only main table’s columns.

2 Likes