CODEWITHSUNDEEP
sqlpostgresqlknex.jspostgresql-9.5
Merey Nurlan
Merey Nurlan

Reputation: 305

How to append columns conditionally inside .select() in knex.js

What I am trying to achieve is to add column name inside .select() when conditions is met. So my code is like below:

const type = req.query.type;

knex('some_table').select('id','name',knex.raw('case '+ type +'when 1 then some_table.type else null end'))

Problem with this code is that it returns null when type != 1 but I want it to return nothing.

So basically, it should add 'type' column if type = 1 if not then add nothing.

Any thoughts?

Upvotes: 0

Views: 4485

Answers (2)

ABCD.ca
ABCD.ca

Reputation: 2495

How about building up an array of column names dynamically however you want to add them (ex. array.push) then spreading them as arguments into the select call?

const myColumns = ['column1']

if (conditionIsTrue){
 myColumns.push('column2')
}

knex('some_table')
  .select(...myColumns) // <--- the ... here turns the array into individual args

Upvotes: 0

Rich Churcher
Rich Churcher

Reputation: 7654

So, I suspect that you're not actually after a CASE statement. These are used to check column values and manipulate the result set. For example:

knex('some_table')
  .select(
    'id',
    'name',
    knex.raw(`CASE WHEN ${type} = 'Apricots' THEN 'Apricots!' ELSE 'Not an apricot!' END`)
)

or to use the simpler form,

`CASE ${type} WHEN 'Apricots' THEN 'Hey, it's an apricot.' END`

What you seem to be after, though it's not entirely clear, is conditionally selecting a column based on a querystring. This could be achieved prior to issuing the query:

const columns = [
  'id',
  'name'
]

if (req.query.type) {
  columns.push(req.query.type)
}

knex('some_table').select(columns)

I hope it should be clear that allowing users to specify which columns come back in a result set is open to abuse, so you should be very careful which kind of data can be specified.

If this wasn't what you intended, you might need to clarify your question a little.

Upvotes: 4

Related Questions