How to create values list with KnexJS

Question

I want to generate a dynamic table to be used in a query. To do this I want to use PostgreSQL VALUES list feature documented here: https://www.postgresql.org/docs/13/queries-values.html

The SQL would look something like this:

with my_values (id, "name") as (
    values (1, 'one'), (2, 'two'), (3, 'three')
)
select *
from my_values mv
join some_other_table sot
    on sot.value_id = mv.id

I can't find anything in the KnexJS documentation that supports generating the above SQL besides just using raw for the whole query which kind of defeats the point of using KnexJS.

Is there a way to create a values list for a part of the query in KnexJS?

Answer 1

In order to construct the following query

with my_values as (
    Select *
    from (values(1, 'one'), (2, 'two'), (3, 'three')) as inner (id, name)
)
select *
from my_values mv
join some_other_table as sot on sot.value_id = mv.id

The problem is that knex doesn't supports column list definition as you've written. Postgress, supports this

SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);

So, the query above can be built like:

const vals = [
  { id: 1, name: 'one' },
  { id: 2, name: 'two' },
  { id: 3, name: 'three' },
];

const params = vals.reduce((result, { id, name }) => result.concat(id, name), []);


await db
  .with(
    `my_values`,
    db.raw(
      `Select * from (values ${vals.map(() => `(?, ?)`).join(',')}) as inner (id, name)`,
      params
    )
  )
  .select('*')
  .from('my_values mv')
  .join('some_other_table', 'some_other_table.value_id', 'mv.id');

It's generates

{
  bindings: [ 1, 'one', 2, 'two', 3, 'three' ],
  sql: 'with `my_values` as (Select * from (values (?, ?),(?, ?),(?, ?)) as inner (id, name)) select * from `my_values_mv` inner join `some_other_table` on `some_other_table`.`value_id` = `mv`.`id`'
}