1

I have a tag area where 0...N tags can be chosen. The tag area gives a comma separated list which will then be sent to the server for further processing. For example, the tag csv-list can look like this:

var taglist = ['important', 'less important']

I create an array out of it for it to be processed by LIKE ANY.

 var taglistArray = pgp.as.array(taglist);

There is a possible way with LIKE ANY but I could not get it to work.

 select * from $1:name WHERE importance LIKE ANY $4:list
 ...
 ['tablename', field2, field3, taglistArray]

error: syntax error at or near "'array[''important'',''less important'']'"

There seem to be several issues if the tag area is empty (hence the query should return everything and not nothing) and the ability to query a list instead of just one keyword.

How can a list of tags be translated into a SQL query in the best way using pg-promise?

codebird456
  • 505
  • 8
  • 19
  • Are you using `pgp.as.array` and then pass it in as a string? That would be wrong, the array is supposed to be passed in directly, if you are using filter `:list`. Also PostgreSQL syntax for `ANY` is with parentheses, i.e. `ANY($4:list)`. – vitaly-t Oct 22 '18 at 12:58

1 Answers1

1

As per documentation examples, you use arrays directly, and not pre-convert them into strings:

var taglist = ['one', 'two'];
db.any('select * from $1:name WHERE importance LIKE ANY($2)', ['important', tagList])

And your code formats the array twice, and hence the double-escaping issue. It is only for IN($2:list) you would use the :list filter, but for ANY you need the array directly, i.e. ANY($2), without any filter.

Also, the correct syntax for ANY is with parentheses - ANY($2).

vitaly-t
  • 24,279
  • 15
  • 116
  • 138
  • Thanks. I could get IN ($2:csv) working. However, I still struggle with LIKE ANY ($2:csv) when I just change IN into LIKE ANY. Do you know how that is supported? – codebird456 Oct 22 '18 at 16:40
  • 1
    @codebird456 I wouldn't speculate on the syntax of queries you generate. The right way to approach this - start using [pg-monitor](https://github.com/vitaly-t/pg-monitor), so you can see the actual queries being executed, and then they are easy to diagnose. – vitaly-t Oct 22 '18 at 16:44
  • Thanks. I will do that. But I have not found any info in the documentation you linked above about the combination of LIKE ANY ($2:csv). I could find just IN ($2:csv). Is that supported? – codebird456 Oct 22 '18 at 16:57
  • 1
    @codebird456 Yes, for `ANY` syntax do not use any filter. I have corrected the example ;) See also: https://stackoverflow.com/questions/34627026/in-vs-any-operator-in-postgresql – vitaly-t Oct 22 '18 at 17:00
  • Hey thx. It works with ($2:raw) as well when passing pgp.as.array(xxx) as parameter to match LIKE ANY (array['term1,'term2','term3']) from postgres console – codebird456 Oct 22 '18 at 17:18
  • 1
    @codebird456 Yes, the effect in this case would be the same as simply not use any filter and pass in the array directly. – vitaly-t Oct 22 '18 at 17:23