Coldfusion How can i detect duplicate variable in array

Question

There is a loop I pulled from the database with cfloop. After transferring this loop to arrays, I want to increase the number of stocks if the same array exists.

<cfoutput query="getStocks">
    <cfif #CurrentRow# lt 5> 
        <cfscript>
            c=#CurrentRow#;
            if (c==1){
                arr[c]['stok_kod']='#STOCK_CODE#';
                arr[c]['lot']='#LOT_NO#';
                arr[c]['stok']=#STOK_MIKTARI#;
                arr[c]['depo']='#DEPO#';
                arr[c]['isim']='#PRODUCT_NAME#';
                arr[c]['skt']='#GET_SKT.DELIVER_DATE#';
            }
            for (i=1; i <= arrayLen(#arr#);i++) {
                if (arr[i]['stok_kod'] eq '#STOCK_CODE#' ){
                    arr[i]['stok']+=#STOK_MIKTARI#;
                }else{
                    arr[c]['stok_kod']='#STOCK_CODE#';
                    arr[c]['lot']='#LOT_NO#';
                    arr[c]['stok']=#STOK_MIKTARI#;
                    arr[c]['depo']='#DEPO#';
                    arr[c]['isim']='#PRODUCT_NAME#';
                    arr[c]['skt']='#GET_SKT.DELIVER_DATE#';
                }
            }
        </cfscript>
    </cfif>
</cfoutput>

How can I detect those with the same stock code?

You could forget about the arrays and use Query of Queries to determine which stock codes appear more than once. — Dan Bracuk
You could use ArrayFind/FindNoCase with a closure function, but seems a little heavyweight. What about doing the aggregation in SQL? What's your dbms and sql query? — SOS

Khoa Nguyen Khoa Nguyen · Accepted Answer · 2021-04-21T02:30:38

You can try to use query of queries method with GROUP BY function

Here is the example how it works.

<cfscript>
    getStocks = queryNew("stok_kod,stok", "varchar,integer");
    queryAddRow(getStocks);
    querySetCell(getStocks, "stok_kod", "1234");
    querySetCell(getStocks, "stok", "-10");
    queryAddRow(getStocks);
    querySetCell(getStocks, "stok_kod", "5678");
    querySetCell(getStocks, "stok", "10");
    queryAddRow(getStocks);
    querySetCell(getStocks, "stok_kod", "1234");
    querySetCell(getStocks, "stok", "30");
</cfscript>

<cfquery name="newQuery" dbtype="query">
  SELECT stok_kod, SUM(stok) as stok
  FROM getStocks
  GROUP BY stok_kod
</cfquery>

<cfdump var="#newQuery#">

OUTPUT

Coldfusion How can i detect duplicate variable in array

2 Answers