Order Bookshelf.js fetch by related column value

Question

I'm using Bookshelf.js/Knex.js, fetching a model (call it user) with a related child model (call it company).
Can I order by a field on the child model - company.name?

Also, if that's possible, can I multi sort, say company.name descending then lastName ascending

Here's my current code, which only works on root model fields. qb.orderBy('company.name', 'desc') doesn't work.

users.query(function(qb) {
  qb.orderBy('lastName', 'asc');
})
.fetch({withRelated: ['company']})
.then(success, error);

Answer 1

Try the following:

users
.fetch({withRelated: [
     {
         'company': function(qb) {
             qb.orderBy("name");
         }
     }
]})
.then(success, error);

I got the idea from https://github.com/tgriesser/bookshelf/issues/361

Answer 2

I think I solved it by doing this:

let postHits =
    await posts
        .query(qb => qb
            .innerJoin('post_actor_rel', function () {
                this.on('post.id', '=', 'post_actor_rel.post_id');
            })
            .innerJoin('actor', function () {
                this.on('post_actor_rel.actor_id', '=', 'actor.id');
            })
            .orderByRaw('actor.name ASC')
            .groupBy('id')
        )
        .fetchPage({
            withRelated: ['roles', 'themes', 'activity_types', 'subjects', 'educational_stages', 'images', 'documents', 'actors'],
            limit,
            offset
        },
    );

I modify the query by inner joining with the desired tables and after sorting (using orderByRaw since I will need to add some more sorting that I think is not possible with orderBy) I group by the post's id to get rid of the duplicate rows. The only problem is that it's not defined which actor name (of several possible) is used for the sorting.

Answer 3

You can do it like this without the need of a function:

users.query(function(qb) {
  qb.query('orderBy', 'lastName', 'asc');
})
.fetch({withRelated: ['company']})
.then(success, error);

Found here: Sort Bookshelf.js results with .orderBy()