如何< String>在PostgreSQL中实现IN OPERATOR - flutter/dart中列表查询

dtcbnfnu 于 2023-01-21 发布在 Flutter

关注(0)|答案(1)|浏览(121)

我试图从PostgreSQL数据库中提取记录通过应用过滤器（在条件与IN操作符）。然而一些错误，而转换字符串到可迭代元素。建议将不胜感激。

List<dynamic> detailedPositions = [];
  Future<List<dynamic>> fetchDetailedPositions(String custID) async {
    try {
      print(custID);               //output: ABC123, LP8338
      print(custID.runtimeType);   //output: string 
      await connection!.open();
      await connection!.transaction((fetchDataConn) async {
        _fetchMasterPositionData = await fetchDataConn.query(
          "select cust_id, array_agg((cust_id, cust_name, quantity, item_nme, average_price)) from orders
          where cust_id in (select cust_id from string_to_array(@cust_id,',')) and status='OPEN'"
          substitutionValues: {'cust_id': custID, 'status': status},
          timeoutInSeconds: 30,);
      });
    } catch (exc) {
      print('Exception in fetchDetailedPositions');
      print(exc.toString());
      detailedPositions = [];
    }

flutter

来源：https://stackoverflow.com/questions/75174173/how-to-query-with-liststring-in-in-operator-flutter-dart-with-postgresql

1条答案

按热度按时间

rqmkfv5c1#

dbfiddle.uk demo
下面的代码是错误的：

where cust_id in (select cust_id from string_to_array(@cust_id,','))

https://www.postgresql.org/docs/current/functions-string.html#FUNCTION-STRING-TO-ARRAY string_to_array return text array.
您不能使用

select text 'hello'  in '{"hello","world"}'::text[];

正确的 * IN * 结构：

select text 'hello' in ( text 'hello','world');

您也可以使用ANY：

select text 'hello'  = any( '{"hello","world"}'::text[]);

IN与ANY *：IN vs ANY operator in PostgreSQL

因此，您的查询将类似于

SELECT
    cust_id,
    ARRAY_AGG((cust_id, cust_name, quantity, item_nme, average_price))
FROM orders
WHERE cust_id IN (SELECT cust_id FROM string_to_array('ABC123, LP8338', ',')) AND status = 'OPEN'
GROUP BY
    1;

但是，此查询返回的数据类型如下所示：

Column   |   Type
-----------+----------
 cust_id   | text
 array_agg | record[]
(2 rows)

record []的使用大多数时候会有一些问题，最好使用json。所以下面就可以了。

SELECT
    cust_id,
    json_agg((
        SELECT
            x
        FROM (SELECT cust_id, cust_name, quantity, item_nme, average_price) AS x)) AS item
FROM orders
WHERE cust_id IN (SELECT cust_id FROM string_to_array('ABC123, LP8338', ',')) AND status = 'OPEN'
GROUP BY
    1;

json_agg用法：https://dba.stackexchange.com/questions/69655/select-columns-inside-json-agg

赞(0）回复(0）举报 2023-01-21

我来回答

如何< String>在PostgreSQL中实现IN OPERATOR - flutter/dart中列表查询

1条答案

相关问题

热门标签

最新问答