node.js - Produce parentheses-enclosed list of values with pgpromise - Stack Overflow

admin管理员组

文章数量:1277896

I am using a queryfile to produce a query:

SELECT
  lst.${layerTitleColumn:name}
FROM
  (VALUES(${ids:list})) AS ids(id)
LEFT JOIN
  ${tableName:name} AS lst
ON
  ids.id = lst.${layerIdColumn:name}
ORDER BY
  ids.id

via

const result = await db.any(query, {
  tableName: lst.name,
  layerIdColumn: lst.layerIdColumn,
  layerTitleColumn: lst.layerTitleColumn,
  ids: request.body
})

where request.body is of the type ['searchstring1', 'searchstring2'...]

My problem is that this is converted to

SELECT lst."public_title" 
FROM (VALUES('searchstring1','searchstring2')) AS ids(id) 
LEFT JOIN "layer_specification" AS lst 
    ON ids.id = lst."name" ORDER BY ids.id

which makes only the first match the result. I would like for the values list to indicate a list of rows, ('searchstring1'), ('searchstring2')

I think I could create a valid query string outside of a queryfile, manually in the case of the array values (searchstrings) but I was wondering whether what I wish to perform is possible via pgpromise helpers/formatters/variables.

I am using a queryfile to produce a query:

SELECT
  lst.${layerTitleColumn:name}
FROM
  (VALUES(${ids:list})) AS ids(id)
LEFT JOIN
  ${tableName:name} AS lst
ON
  ids.id = lst.${layerIdColumn:name}
ORDER BY
  ids.id

via

const result = await db.any(query, {
  tableName: lst.name,
  layerIdColumn: lst.layerIdColumn,
  layerTitleColumn: lst.layerTitleColumn,
  ids: request.body
})

where request.body is of the type ['searchstring1', 'searchstring2'...]

My problem is that this is converted to

SELECT lst."public_title" 
FROM (VALUES('searchstring1','searchstring2')) AS ids(id) 
LEFT JOIN "layer_specification" AS lst 
    ON ids.id = lst."name" ORDER BY ids.id

which makes only the first match the result. I would like for the values list to indicate a list of rows, ('searchstring1'), ('searchstring2')

I think I could create a valid query string outside of a queryfile, manually in the case of the array values (searchstrings) but I was wondering whether what I wish to perform is possible via pgpromise helpers/formatters/variables.

• node.js
• postgresql
• pg-promise

Share Improve this question Follow edited Feb 24 at 17:06 ValNik 5,94611 gold badge77 silver badges1414 bronze badges asked Feb 24 at 16:11 David IDavid I 7111 silver badge66 bronze badges 3

• Everything in your query seems to be correct. Show an example of data where you are not getting the expected result. And what you want get. – ValNik Commented Feb 24 at 17:11
• 1 It needs to be ... (VALUES('searchstring1'), ('searchstring2')) .... As you have it now it resolves to: SELECT * FROM (VALUES('searchstring1','searchstring2')); searchstring1 | searchstring2. In other words one row with two values. In the changed version you get two rows each with a single value. – Adrian Klaver Commented Feb 24 at 18:18
• @AdrianKlaver Yep that's it, that's what I was trying to show with the result "I would like for the values list to indicate a list of rows". I'm seeking a possible pgpromise way of producing this part of the query. – David I Commented Feb 25 at 7:53

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

The simplest in this case is to convert the list of values into that format, via Custom Type Formatting:

const listValues = (ids: Array<string>) => ({
    toPostgres: () => ids.map(a => pgp.as.format('($1)', a).join(),
    rawType: true
})

Pass it in as ids: listValues(request.body), and remove :list filter, as no longer needed.

---

For a more complex scenario, when each row isn't just a string, but a set of various-type values, use ColumnSet, plus values helper.

Maybe you mean something like that:
You can't get
VALUES('searchstring1'), ('searchstring2')
instead of
VALUES('searchstring1','searchstring2')

If so, use CROSS JOIN LATERAL and string_to_table

create table test (id varchar(10),val varchar(10));
insert into test values
 ('key1','value1')
,('key2','value2')
,('key3','value3')
,('key4','value4')
;

query example

SELECT *
FROM (VALUES('key1,nokey,key2')) AS idstr(idlist)
cross join lateral string_to_table (idlist,',') ids(id)
LEFT JOIN test AS lst
ON ids.id = lst.id
ORDER BY ids.id

idlist	id	id	val
key1,nokey,key2	key1	key1	value1
key1,nokey,key2	key2	key2	value2
key1,nokey,key2	nokey	null	null

Or

SELECT *
FROM string_to_table ('key1,nokey,key2',',') ids(id)
LEFT JOIN test AS lst
ON ids.id = lst.id
ORDER BY ids.id

In your query

SELECT
  lst.${layerTitleColumn:name}
FROM 
  string_to_table(${ids:list}) AS ids(id)
LEFT JOIN
  ${tableName:name} AS lst
ON
  ids.id = lst.${layerIdColumn:name}
ORDER BY
  ids.id

fiddle

Short example with CROSS JOIN LATERAL and string_to_table

SELECT   *
FROM (VALUES('key1,nokey,key2')) AS ids(idlist)
cross join lateral string_to_table (idlist,',') sl(id)

idlist	id
key1,nokey,key2	key1
key1,nokey,key2	nokey
key1,nokey,key2	key2

Of course, we are not talking about injection.

本文标签： nodejsProduce parenthesesenclosed list of values with pgpromiseStack Overflow