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);

score 2 · Answer 1 · answered Jun 04 '14 at 16:08

2

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

answered Jun 04 '14 at 16:08

wbkang

2,136
1
15
22

score 1 · Answer 2 · edited May 23 '17 at 10:30

1

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()

edited May 23 '17 at 10:30

Community

1
1

answered Dec 01 '15 at 16:01

mezod

2,313
3
21
31

score 0 · Answer 3 · answered Apr 27 '18 at 06:39

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.

Order Bookshelf.js fetch by related column value

3 Answers3

Linked