Subquery in "from" clause using another instance of knex

[sandrocsimas]

Hi!
I have this query:

var scores = Bookshelf.knex('audition_vote').sum('voting_power as score').groupBy('audition_id')

and i want to wrap this query like:

var topScores = Bookshelf.knex.distinct('score').from(function() {
  // This works but i want to use "scores" variable
  this.sum('voting_power as score').from('audition_vote').groupBy('audition_id').as('scores'); 
});

console.log(topScores.toString()) prints

select distinct `score` from (select sum(`voting_power`) as `score` from `audition_vote` group by `audition_id`) as `scores

How to use the variable "scores" inside from() ????

[tgriesser]

What's the query you're ultimately looking to generate?

[sandrocsimas]

The last one:

select distinct `score` from (select sum(`voting_power`) as `score` from `audition_vote` group by `audition_id`) as `scores`

The query is right, but i want to reuse the variable "scores"

[tgriesser]

Oh, you can just do:

var scores = Bookshelf.knex('audition_vote')
   .sum('voting_power as score')
   .groupBy('audition_id')
   .as('scores');
var topScores = Bookshelf.knex.distinct('score').from(scores.clone());

The .as is ignored unless it's within a subquery, and the clone is only necessary if you're mutating the scores query elsewhere.

Or you could do:

var scores = Bookshelf.knex('audition_vote')
   .sum('voting_power as score')
   .groupBy('audition_id');
var topScores = Bookshelf.knex.distinct('score').from(scores.clone().as('scores'));