node-postgres: как выполнить запрос "WHERE col IN (<список динамических значений>)"?
Я пытаюсь выполнить запрос, как это:
SELECT * FROM table WHERE id IN (1,2,3,4)
Проблема в том, что список идентификаторов, по которым я хочу фильтровать, не является постоянным и должен отличаться при каждом выполнении. Мне также нужно было бы избежать идентификаторов, потому что они могут быть получены из ненадежных источников, хотя я бы на самом деле избежал всего, что идет в запросе, независимо от достоверности источника.
node-postgres работает исключительно со связанными параметрами: client.query('SELECT * FROM table WHERE id = $1', [ id ]); это будет работать, если у меня было известное количество значений (client.query('SELECT * FROM table WHERE id IN ($1, $2, $3)', [ id1, id2, id3 ])), но не будет работать с массивом напрямую: client.query('SELECT * FROM table WHERE id IN ($1)', [ arrayOfIds ]), так как нет никакой особой обработки параметров массива.
Динамическое построение шаблона запроса в соответствии с количеством элементов в массиве и расширение массива идентификаторов в массив параметров запроса (который в моем конкретном случае также содержит другие параметры, кроме списка идентификаторов) представляется необоснованно обременительным. Жесткое кодирование списка идентификаторов в шаблоне запроса также кажется нежизнеспособным, поскольку node-postgres не предоставляет методов, позволяющих избежать значений.
Это похоже на очень распространенный вариант использования, поэтому я предполагаю, что на самом деле я что-то упускаю, а не то, что невозможно использовать общий IN (values) Оператор SQL с нод-постгрес.
Если кто-то решил эту проблему более элегантно, чем те, что я перечислил выше, или если я действительно что-то упустил из-за node-postgres, пожалуйста, помогите.
9 ответов
Мы уже видели этот вопрос в списке проблем GitHub. Правильный способ - динамически генерировать список параметров на основе массива. Что-то вроде этого:
var arr = [1, 2, "hello"];
var params = [];
for(var i = 1; i <= arr.length; i++) {
params.push('$' + i);
}
var queryText = 'SELECT id FROM my_table WHERE something IN (' + params.join(',') + ')';
client.query(queryText, arr, function(err, cb) {
...
});
Таким образом вы получите параметризованный выход postgres.
Похоже, что вы, возможно, были близки, основываясь на вашем комментарии к ответу @ebohlman. Ты можешь использовать WHERE id = ANY($1::int[]), PostgreSQL преобразует массив в тип, к которому приведен параметр $1::int[], Итак, вот надуманный пример, который работает для меня:
var ids = [1,3,4];
var q = client.query('SELECT Id FROM MyTable WHERE Id = ANY($1::int[])',[ids]);
q.on('row', function(row) {
console.log(row);
})
// outputs: { id: 1 }
// { id: 3 }
// { id: 4 }
Лучшее решение, которое я нашел, состояло в том, чтобы использовать ANY функция с приведением массива Postgres. Это позволяет сопоставить столбец с произвольным массивом значений, как если бы вы выписали col IN (v1, v2, v3), Это подход в ответе Перо, но здесь я показываю, что производительность ANY такой же как IN,
запрос
Ваш запрос должен выглядеть так:
SELECT * FROM table WHERE id = ANY($1::int[])
Этот бит в конце, который говорит $1::int[] может быть изменен в соответствии с типом вашего столбца "id". Например, если тип ваших идентификаторов uuidты бы написал $1::uuid[] привести аргумент к массиву UUID. Смотрите здесь список типов данных Postgres.
Это проще, чем написание кода для построения строки запроса, и он безопасен от SQL-инъекций.
пример
С помощью node-postgres полный пример JavaScript выглядит следующим образом:
var pg = require('pg');
var client = new pg.Client('postgres://username:password@localhost/database');
client.connect(function(err) {
if (err) {
throw err;
}
var ids = [23, 65, 73, 99, 102];
client.query(
'SELECT * FROM table WHERE id = ANY($1::int[])',
[ids], // array of query arguments
function(err, result) {
console.log(result.rows);
}
);
});
Спектакль
Один из лучших способов понять производительность SQL-запроса - посмотреть, как его обрабатывает база данных. Пример таблицы содержит около 400 строк и первичный ключ с именем "id" типа text,
EXPLAIN SELECT * FROM tests WHERE id = ANY('{"test-a", "test-b"}');
EXPLAIN SELECT * FROM tests WHERE id IN ('test-a', 'test-b');
В обоих случаях Postgres сообщил об одном и том же плане запросов:
Bitmap Heap Scan on tests (cost=8.56..14.03 rows=2 width=79)
Recheck Cond: (id = ANY ('{test-a,test-b}'::text[]))
-> Bitmap Index Scan on tests_pkey (cost=0.00..8.56 rows=2 width=0)
Index Cond: (id = ANY ('{test-a,test-b}'::text[]))
Вы можете увидеть другой план запроса в зависимости от размера таблицы, индекса и вашего запроса. Но для запросов, подобных приведенным выше, ANY а также IN обрабатываются таким же образом.
Используя pg-обещание, это хорошо работает через фильтр CSV (значения через запятую):
const values = [1, 2, 3, 4];
db.any('SELECT * FROM table WHERE id IN ($1:csv)', [values])
.then(data => {
console.log(data);
})
.catch(error => {
console.log(error);
});
И для решения проблемы различных типов данных, :csv Модификатор сериализует массив в csv, преобразуя все значения в их надлежащий формат PostgreSQL, в соответствии с их типом JavaScript, даже поддерживая пользовательское форматирование типов.
И если у вас есть смешанные значения типа этого: const values = [1, 'two', null, true], вы все равно получите правильно экранированный SQL:
SELECT * FROM table WHERE id IN (1, 'two', null, true)
ОБНОВИТЬ
Начиная с v7.5.1, pg-обещание начал поддерживать :list как сменный псевдоним для :csv фильтр:
db.any('SELECT * FROM table WHERE id IN ($1:list)', [values])
Другим возможным решением является использование UNNEST функционировать так:
var ids = [23, 65, 73, 99, 102];
var strs = ['bar', 'tar', 'far']
client.query(
'SELECT * FROM table WHERE id IN(SELECT(UNNEST($1))',
[ids], // array of query arguments
function(err, result) {
console.log(result.rows);
}
);
client.query(
'SELECT * FROM table WHERE id IN(SELECT(UNNEST($1))',
[strs], // array of query arguments
function(err, result) {
console.log(result.rows);
}
);
Я использовал это в хранимой процедуре, и она отлично работает. Поверьте, это должно работать также из кода node-pg.
Вы можете прочитать о функции UNNEST здесь.
Другое возможное решение, например, для REST API в NODE JS:
var name = req.body;//Body is a objetc that has properties for example provinces
var databaseRB = "DATABASENAME"
var conStringRB = "postgres://"+username+":"+password+"@"+host+"/"+databaseRB;
var filter_query = "SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features FROM (SELECT 'Feature' As type, ST_AsGeoJSON(lg.geom)::json As geometry, row_to_json((parameters) As properties FROM radiobases As lg WHERE lg.parameter= ANY($1) )As f) As fc";
var client = new pg.Client(conStringRB);
client.connect();
var query = client.query(new Query(filter_query,[name.provinces]));
query.on("row", function (row, result) {
result.addRow(row);
});
query.on("end", function (result) {
var data = result.rows[0].row_to_json
res.json({
title: "Express API",
jsonData: data
});
});
Имейте в виду, что может использоваться любой тип массива
я попробовал это и работал без приведения к int[]. использование node-postgres с основной версией 8
var q = client.query('SELECT Id FROM MyTable WHERE Id = ANY($1)',[ids]);
A simple solution would be
client.query('SELECT * FROM table WHERE id IN ($1)', [ids.join(",")])
where ids is array of id's
Идея вообще:
var invals = [1,2,3,4], cols = [...fields];
var setvs = vs => vs.map(v=> '$'+ (values.push(v)) ).join();
var values = [];
var text = 'SELECT '+ setvs(cols) +' FROM table WHERE id IN (' + setvs(invals) +')';