how to query with List in IN OPERATOR - flutter/dart with PostgreSQL

Question

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

score 1 · Accepted Answer · answered Jan 19 '23 at 15:58

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

score 0 · Answer 2 · answered Feb 09 '23 at 14:16

This helped me

var valuesString1 = custID.map((word) => word.trim()).toList();
String valuesString = valuesString1.map((e) => "'$e'").join(',');

_fetchMasterPositionData = await fetchDataConn.query(
  "SELECT cust_id, cust_name, net_quantity, net_stock_symbol, net_average_price, net_product_type, net_m2m, net_last_price, false AS isSelected FROM positions WHERE cust_id IN ($valuesString) AND net_status = 'OPEN'",
);

how to query with List in IN OPERATOR - flutter/dart with PostgreSQL

2 Answers2