Question 77510 in Flutter Question posted in
The official Flutter documentation can be found here.

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

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

2

Answers


  1. Chosen as BEST ANSWER
    0

    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'",
);

  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search