CODEWITHSUNDEEP
javascriptnode.jsbookshelf.js
Nicholas Gati
Nicholas Gati

Reputation: 99

How can I sort the results of a Bookshelf.js query based on a column of the join table?

I am trying to get the results from a Bookshelf.js query and sort them by a column from the join table. However, when adding 'withRelated', I am only able to get the target table and not the join table itself. Below is the code that I have so far. 

MyTable.forge().query({where: {id: req.params.id}})
  .fetch({
    withRelated: [
      {'children.children': (qb) => { qb.orderBy('position', 'asc'); }}
    ]
  }).then(result => {
    res.send(JSON.stringify({theResult: result}));
  });

The 'orderBy' is supposed to order by the column 'position' which is only in the join table. It seems that 'qb' only returns results from the target table and not the join table. I have attempted 'withPivot' but am not getting the correct result.

Upvotes: 0

Views: 973

Answers (1)

Nicholas Gati
Nicholas Gati

Reputation: 99

So after looking through the returned object and reading through some more documentation I found that the attribute to orderBy is changed by Bookshelf.js to '_pivot_position'. 'pivot' is added to the column name and that is what you want to use. However, this only seems to work for the following.

withRelated: [
      {'children': (qb) => { qb.orderBy('_pivot_position', 'asc'); }}
    ]

As shown above, I had to exclude 'children.children' and just use 'children'. I now wonder if Bookshelf.js allows you to specify multiple 'orderBy' columns from child elements.

Upvotes: 1

Related Questions