loop over a query to create an Manual ArryOfStructs Data

Question

I am have a query data and want to create a manual array of structs with it, i am using this code but it seems wrong. what i am missing here what should do it here

for(i in myquery) {
                st['Contact'][#i#]['ID'] = i.id;
                st['Contact'][#i#]['Name'] = i.name;
                st['Contact'][#i#]['Email'] = i.email;
            }

Answer 1

You have some issues in your code. I'm going to break this down:

Let's assume you have a query result like the follwing:

myquery= queryNew(
            "id, name, email",
            "integer,varchar,varchar",
            [ 
                {"id":43,"name":"Antonio", "email": "[email protected]"}, 
                {"id":65,"name":"Manuel", "email": "[email protected]"}, 
                {"id":77,"name":"Paula", "email": "[email protected]"},
                {"id":87,"name":"Jennifer", "email": "[email protected]"}
                
            ]);

Then iterating (looping) through your query with a for( row in query ) like you did won't work:

    for( i in myquery ){
       st['Contact'][#i#]['ID'] = i.id;
       st['Contact'][#i#]['Name'] = i.name;
       st['Contact'][#i#]['Email'] = i.email;
    }

The cause:

• When using a for( row in myquery ) you are iterating the query by row. But in your loop you want to make use of an index i to set your arrays. Then, i is not going to be a numeric integer that you are expecting, but a complete rowset. It will try using the complete row as index, and that shouldn't work. If you need an index, you MUST also iterate your query by index, e.g. with for (i = 1; i <= myquery.recordCount; i++). As a reference please see how to iterate a query by index see cfdocs here, or alternatively use the ArrayAppend() or append() member without using any index.
• You need to pre define st['Contact'] as an array with st['Contact']=[]; first, otherwise CF will assume it's a struct
• Also, you are setting the array with i, but outputting it with [#i#] without any quotes. Use # in outputs embraced with quotes like this: st['Contact']['#i#']['ID'] or without embracing quotes like this: st['Contact'][i]['ID']. But not the like this st['Contact'][#i#]....

Correcting your code to make st.Contact be populated as an array, it could be similar to:

Alternative 1: Use ArrayAppend() or append() member function:

st['Contact']=[]; 
for ( i in myquery ) {
      st['Contact'].append( 
         {"id": myquery.id,
         "name": myquery.name,
         "email": myquery.email
         });
    }

same but simplier:

st['Contact']=[]; 
for ( i in myquery ) {
      st['Contact'].append( i );
    }

Alternative 2: Iterate the query by index with for (i = 1; i <= myquery.recordCount; i++)

st['Contact']=[]; 
for (i = 1; i <= myquery.recordCount; i++) {
    st['Contact'][i]['ID'] = myquery.id[i];
    st['Contact'][i]['Name'] = myquery.name[i];
    st['Contact'][i]['Email'] = myquery.email[i];
            }

If you dump the variable st with the help of the query I've submitted, you'll get the following data structure (here it's simply stringified):

{"Contact":[
    {"Name":"Antonio","ID":43,"Email":"[email protected]"},
    {"Name":"Manuel","ID":65,"Email":"[email protected]"},
    {"Name":"Paula","ID":77,"Email":"[email protected]"},
    {"Name":"Jennifer","ID":87,"Email":"[email protected]"}
    ]}

Here is the complete cfscript code of both alternatives:

myquery= queryNew(
            "id, name, email",
            "integer,varchar,varchar",
            [ 
                {"id":43,"name":"Antonio", "email": "[email protected]"}, 
                {"id":65,"name":"Manuel", "email": "[email protected]"}, 
                {"id":77,"name":"Paula", "email": "[email protected]"},
                {"id":87,"name":"Jennifer", "email": "[email protected]"}
                
            ]);
            
st['Contact']=[]; 
for ( i in myquery ) {
      st['Contact'].append( 
         {"id": myquery.id,
         "name": myquery.name,
         "email": myquery.email
         });
    }
dump(st);

// simplier version of above
st['Contact']=[]; 
for ( i in myquery ) {
      st['Contact'].append( i );
    }
dump(st);


st['Contact']=[]; 
for (i = 1; i <= myquery.recordCount; i++) {
    st['Contact'][i]['ID'] = myquery.id[i];
    st['Contact'][i]['Name'] = myquery.name[i];
    st['Contact'][i]['Email'] = myquery.email[i];
}
dump(st);