javascript - Select from an existing query in knex.js - Stack Overflow

admin管理员组

文章数量:1310099

I'm building a query using knex.js, given an existing sub query. Following this answer, and this thread in GitHub, I tried the following:

const knex = require("knex")({client: 'pg'});

const subQuery = knex.queryBuilder().select(1);

const query = knex.queryBuilder().select('*').from(subQuery);

console.log(query.toString());

But the result was:

select * from select 1

Which obviously has a syntax error. My expected result is:

select * from (select 1)

Why doesn't it add the parentheses, and how can I change it?

I'm building a query using knex.js, given an existing sub query. Following this answer, and this thread in GitHub, I tried the following:

const knex = require("knex")({client: 'pg'});

const subQuery = knex.queryBuilder().select(1);

const query = knex.queryBuilder().select('*').from(subQuery);

console.log(query.toString());

But the result was:

select * from select 1

Which obviously has a syntax error. My expected result is:

select * from (select 1)

Why doesn't it add the parentheses, and how can I change it?

• javascript
• node.js
• knex.js

Share Improve this question Follow edited May 23, 2017 at 12:26 CommunityBot 111 silver badge asked Feb 8, 2017 at 15:00 GilZGilZ 6,47755 gold badges3232 silver badges4040 bronze badges

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 7

Your way, how you did it seems correct and I would say that it is bug in knex why it doesn't work (I'm collaborator in knex).

Anyways there are couple of ways to do it...

const knex = require("knex")({client: 'pg'});

const subQuery = knex.select(1).as('t1');
const query = knex.select('*').from(subQuery);
console.log(query.toSQL());

{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [],
  __knexQueryUid: '69d240ad-f5f8-4bc4-8c1d-fb9432af1da2',
  sql: 'select * from (select 1) as "t1"' }

Or you can use older style with function() subquery, which doesn't require .as(), but supports it...

const query = knex.select('*').from(sq => sq.select(1));
console.log(query.toSQL());

{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [],
  __knexQueryUid: '31beb080-c89a-43b2-b112-546077330e82',
  sql: 'select * from (select 1)' }

An ugly (but working) solution I found was using knex.raw and subQuery.toString:

const query = knex.queryBuilder()
    .select('*')
    .from(knex.raw(`(${subQuery})`);

I don't believe this is the best answer, and I'm sure I'm missing something, so I don't accept this answer yet.

本文标签： javascriptSelect from an existing query in knexjsStack Overflow