how to query with List<String> in IN OPERATOR - flutter/dart with PostgreSQL-CodePudding

I'm trying to fetch records from PostgreSQL DB by applying filters (where condition with IN operator). However Something wrong while converting string to iterable element. suggestions will be appreciated.

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 = [];
    }

CodePudding user response：

dbfiddle.uk demo
The following code is wrong:

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.

You cannot use

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

correct IN construct:

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

You can also use ANY:

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

IN vs ANY: IN vs ANY operator in PostgreSQL

So your query will be like

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;

However, this query return data types are something like:

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

record[] usage most of time will have some problem, better use json. So the following will do.

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 usage: https://dba.stackexchange.com/questions/69655/select-columns-inside-json-agg