Knex, Objection.js, How to sort by number of associations

Question

I have an Express API using Postgres via Knex and Objection.

I want to set up a Model method or scope that returns an array of Parent model instances in order of number of associated Children.

I have looked through the Knex an Objection docs

I have seen the following SQL query that will fit, but trying to figure out how to do this in Knex:

SELECT SUM(O.TotalPrice), C.FirstName, C.LastName
  FROM [Order] O JOIN Customer C 
    ON O.CustomerId = C.Id
 GROUP BY C.FirstName, C.LastName
 ORDER BY SUM(O.TotalPrice) DESC

Answer 1

This is how it should be done with knex (https://runkit.com/embed/rj4e0eo1d27f):

function sum(colName) {
  return knex.raw('SUM(??)', [colName]);
}

knex('Order as O')
  .select(sum('O.TotalPrice'), 'C.FirstName', 'C.LastName')
  .join('Customer C', 'O.CustomerId', 'C.Id')
  .groupBy('C.FirstName', 'C.LastName')
  .orderBy(sum('O.TotalPrice'), 'desc')

// Outputs: 
// select SUM("O"."TotalPrice"), "C"."FirstName", "C"."LastName" 
//   from "Order" as "O" 
//   inner join "Customer C" on "O"."CustomerId" = "C"."Id" 
//   group by "C"."FirstName", "C"."LastName" 
//   order by SUM("O"."TotalPrice") desc

But if you are really using objection.js then you should setup models for your Order and Customer tables and do something like this:

await Order.query()
  .select(sum('TotalPrice'), 'c.FirstName', 'c.LastName')
  .joinRelated('customer as c')
  .groupBy('c.FirstName', 'c.LastName')
  .orderBy(sum('TotalPrice'), 'desc')

Answer 2

In case anyone is interested, I just included the raw SQL query as follows:

router.get('/leaderboard', async (req, res) => {
  let results = await knex.raw('SELECT users_id, username, COUNT(acts.id) FROM acts INNER JOIN users ON acts.users_id = users.id GROUP BY users_id, username ORDER BY COUNT(acts.id) DESC');
  console.log(results);
  res.json(results.rows);
});