How can we add a new row in middle of a cfquery result?

Question

I have a query result set from cfquery. I just want to add a new after a particular row number. But When tried each time it inserts the row at the end.

Is there any way I can insert row at the middle of the query?

 <cfquery datasource="cse" name="abc">
    select * from grade 
 </cfquery>

 <cfset i = 0>
 <cfloop query="abc">
   <cfset i = i+1>
   <cfif i eq 2>
      <cfset queryAddRow(abc)>
   </cfif>  
 </cfloop>

Can you give us an idea of what you have tried? One thing to try might be adding row to end then doing a QoQ to update the sort order. Curious, what is the use case for adding a row in the middle of a query? — Scott Stroz, Feb 05 '14 at 13:26
There's nothing natively. Supposing you have a query with 4 rows, and you want to insert a new 3rd row, moving rows 3,4 to position 4,5. You'd have to grab the first 2 rows, add 1 new row, then add back on the last 2 rows. Bit of a faff — duncan, Feb 05 '14 at 13:30
You can't edit an existing query like that. `queryAddRow()` should perhaps better be called `queryAppendRow()`. You can only add new rows to the *end* of a query. — Adam Cameron, Feb 05 '14 at 13:39
*what is the use case for adding a row in the middle of a query* I am curious as well. I have seen similar questions, but with no mention of the reason for the requirement. — Leigh, Feb 05 '14 at 17:48
Not the answer to your question, but you can use "CURRENTROW" instead of creating an index counter variable. Then you just need and can eliminate the i variable. — Robert Waddell, Feb 12 '14 at 00:07

score 3 · Answer 1 · answered Feb 05 '14 at 13:38

You cannot, easily. You have a coupla options.

<cfquery name="resultSet" dbtype="query">
    SELECT col1, col2, etc
    FROM yourQuery
    WHERE [somecondition matching the "top" rows]

    UNION

    SELECT 'value' AS col1, 'value' AS col2, etc

    UNION

    SELECT col1, col2, etc
    FROM yourQuery
    WHERE [somecondition matching the "bottom" rows]
</cfquery>

Or you could simply loop over the original query, building a new query, using queryNew(), queryAddRow() and querySetCell(). At the appropriate point in the loop... add the row you want to insert, then continue adding the rest of them.

There's no elegant way I can think of.

You would probably need a sorting field with values of 1, 2, and 3 for that union query to work, plus an order by clause. — Dan Bracuk, Feb 05 '14 at 13:42

score 2 · Answer 2 · answered Feb 12 '14 at 00:23

Not knowing the goal you are trying to accomplish, my first advice would be along the lines of the other answers. Add rows that you can subsequently sort using Order By. However, if you really just want to inject a row at a specific position in the existing query, this should do it for you. Note you'll need to define the columns in the QueryNew(), so I've provided a sample case.

 <cfquery datasource="cse" name="abc">
    select student, teacher from grade 
 </cfquery>

 <cfset abc_new = QueryNew("student,teacher","varchar,varchar")>

 <cfloop query="abc">
   <!--- ADD NEW DATA TO QUERY AT ROW 2 --->
   <cfif CURRENTROW eq 2>
     <cfset QueryAddRow(abc_new) />
     <cfset QuerySetCell(abc_new,"STUDENT","Tommy Oliver") />
     <cfset QuerySetCell(abc_new,"TEACHER","Ms. Appleby") />
   </cfif>

   <!--- COPY ORIGINAL DATA TO QUERY, ROW NUMBERS NOT PRESERVED --->
   <cfset QueryAddRow(abc_new) />
   <cfset QuerySetCell(abc_new,"STUDENT",abc.STUDENT) />
   <cfset QuerySetCell(abc_new,"TEACHER",abc.TEACHER) />
 </cfloop>

 <cfdump var="#abc_new#">

score 1 · Answer 3 · answered Feb 05 '14 at 13:46

The best way would be to create a new query, copying each row from the original but adding the new row at the required point.

Use QueryNew( ) to create the new query.

A bit like copying text files line-by-line and inserting a new line in the middle!

Again, not quite sure you'd need to do this - you could simply add a sortorder column, incrementing in 10s for each row. The new row would then have a sortorder in between the row before and the row after. eg sortorder would be 15 to insert between 10 and 20.

Hope this helps!

score 1 · Answer 4 · answered Feb 05 '14 at 22:39

I assume row positions in your recordset are based on what you ORDER BY'd in your initial CFQUERY.

Therefore, I'd add the row to the recordset, then do a query of that recordset (query of query) using the same ORDER BY as the initial query, which should then return all rows including your new one, in the (presumed) proper order, in a new recordset.

How can we add a new row in middle of a cfquery result?

4 Answers4