0

Hello dev community :)

Stack: nodejs, Postgresql, npm: pg

How can I add to my query 'and' statement in 'where' clause for variables which can be valid or can be undefined?

Example

`select col1, col2 from table where col1='${test1}' ` <-- add here 'and col2='${test2}'

how to add "and col2=${test2}" dynamically when test 2 is not undefined and leave things as is when it is undefined. How to do the same for multiple entries like 'and col3=test3 and col4=test4....and colN=testN'?

Currently Postgresql returns error if I add 'and' as string in concatenation. What is the proper way to concatenate it?

Thank you all ;)

Ivan Kolyhalov
  • 902
  • 11
  • 16
  • First things first: use [parameterised queries](https://node-postgres.com/features/queries/#Parameterized%20query)! – Bergi Dec 23 '19 at 14:37
  • Please post the code of your attempt that leads to the error. – Bergi Dec 23 '19 at 14:38
  • parmetrised queries require 'and' to be included, i.e.: "select col1,col2 from table where col1=$1, [values]" or "select col1,col2 from table where col1=$1 and col2=$2, [values]". Either way it is a separate query each time. But I need a dynamic one. Otherwise I will end up with lots of dupes. – Ivan Kolyhalov Dec 23 '19 at 15:35
  • Sure, you can build the query dynamically. With simple string concatenation. But if you don't show us your code, we can't tell you what's wrong with it. – Bergi Dec 23 '19 at 15:47
  • But I just showed it in the example in the post. Well ok, I also tried to do something like `select foo1,foo2 from table where foo1='${bar1}' and foo2='${bar2}'` finally if bar2 is undefined postgresql returns "syntax error at end of input". Also the problem is when there's no need for 'and' because all variables are undefined. So can you make a query where say we have 2 columns and 2 variables at which one is undefined and query works? And please show how do you concatenate 'and' into query when variable is not undefined. – Ivan Kolyhalov Dec 23 '19 at 16:09
  • No, you didn't. `<-- add here` is pseudo-code at best, and if implemented correctly, it would work. Please show the actual js code you are using. – Bergi Dec 23 '19 at 16:25
  • Yes I did. The actual code is too huge to put it here. Let's put it another way. let var1=123; let var 2=undefined; let var 3=undefined Please construct a concatenated query where it checks var2 for undefined (you can skip it) and concatenates it into query like 'select col1, col2, col3 from table where....` Thanks. – Ivan Kolyhalov Dec 23 '19 at 16:29
  • If the code is simple enough to write as an answer, surely you can put your approach into the question? – Bergi Dec 23 '19 at 16:32
  • 1
    I mean, it really is as simple as `const query = "select foo1,foo2 from table where foo1=$1"; if (bar2 === undefined) return pgClient.query(query, [bar1]); else return pgClient.query(query+" and foo2=$2", [bar1, bar2]);`. So if you can't show us what you are doing, we can't tell you where the problem is. It might be something as trivial as a misplaced semicolon. – Bergi Dec 23 '19 at 16:35
  • Your approach looks very promising. I'll try it and get back to you. Maybe you would like to put it as an answer, so we could comment it and accept it. Thanks. – Ivan Kolyhalov Dec 23 '19 at 17:41
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/204747/discussion-between-ivan-kolyhalov-and-bergi). – Ivan Kolyhalov Dec 23 '19 at 17:52

1 Answers1

-1

Because the query contains order by, limit,offset direct concatenation with parametrized query wouldn't work, so I found another solution concatenating outside of query. Yes it has some speed issues while it has to scan more, still it works.

let var1 = 123
let var2 = undefined
let query = ('select col1,col2,col3 from foo order by col1 limit 100 offset 25')

if (var1 && var2){
   query='select * from ('+query+')t where t.col1=var1 and t.col2=var2'
} else {
   query='select * from ('+query+')t'
}

}
Ivan Kolyhalov
  • 902
  • 11
  • 16