CODEWITHSUNDEEP
node.jspostgresqlknex.js
coinhndp
coinhndp

Reputation: 2471

Knex alias where

I use knex to query my backend

knex('project AS p')
 .select('p.name as projectName')
 .where('p.name', 'abc')

If i use p.name = 'abc' then the code works

However I got error when I use:

.where(projectName, 'abc') 
.where(p.name, 'abc')
.where({projectName: abc})

I want to use alias to query not the p.name because when the frontend send a request, tehy send ?projectName='abc' not 'p.name'='abc'. Do you know how to do it

Example

SELECT 
    cp.id AS cId,
    (CASE WHEN cp.id = 7 THEN 'abc' ELSE 'NOT abc' END) AS abc
FROM customer_project AS cp
;

How I query like this

SELECT 
    cp.id AS cId,
    (CASE WHEN cp.id = 7 THEN 'abc' ELSE 'NOT abc' END) AS abc
FROM customer_project AS cp
WHERE abc = 'abc'
;

Got error column abc does not exits. But I really want to query on abc column (although it does not exits on schema but I create it)

Upvotes: 1

Views: 1702

Answers (1)

Mikael Lepistö
Mikael Lepistö

Reputation: 19718

Use .toSQL() to check out what kind of queries knex generates in your case. Your examples in post are not working (there are even syntax errors), so I cannot tell exactly what you are doing wrong.

However this should work correctly also with knex:

> const Knex = require('knex');
undefined
> const knex = Knex({
...   client: 'pg',
... });
undefined
> 
> console.log(
... knex('project AS p')
...  .select('p.name as projectName')
...  .where('p.name', 'abc')
...  .where('projectName', 'abc')
...  .toSQL()
... );
{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 'abc', 'abc' ],
  __knexQueryUid: 'c1a458e3-9fc4-4f9b-a206-eee7ecb5b897',
  sql: 'select "p"."name" as "projectName" from "project" as "p" where "p"."name" = ? and "projectName" = ?' }
undefined
>

And to me that generated SQL looks just fine.

Upvotes: 1

Related Questions