SQL join statement between table and selection in Knex

Question

I have SQL statement :

   select from resources 
          left join ( select resource_id, sum(price) as PostScoreSum from 
                      prices where '2019-06-8' < dateto and '2019-06-15' > 
                      datefrom group by resource_id ) BB on 
                     resources.resources_id = BB.resource_id")

Using Knex, I can write this statement as knex.raw('.....'), but after this knex statement I cannot used modify (to have chain of statements, knex.raw('...').modify...is not posible). Is it possible to write this join in Knex, between table and selection without using raw.

Answer 1

Not clear what actually your issue is but following will generate your above query-

const sql = knex('resources')
   .leftJoin((query) => {
      query
         .columns([
            'resource_id',
            knex.raw('sum(price) as PostScoreSum')
         ])
         .from('prices')
         .where('dateto', '>', '2019-06-8')
         .where('datefrom', '<', '2019-06-8')
         .groupBy('resources_id')
         .as('BB')
   }, 'resources.resources_id', 'BB.resource_id')
   .toSQL();
console.log(sql) ;