Get a copy of a query's row as a structure
Sometimes you have a construction where you have to have a query output inside a query output inside a.... a well you get the picture. When the inner query output needs a variable from the outer query output and you reference the query with it's scope "queryname.variable" then most of the time you end up with getting the first row of your outer query... not what you want!!
So I normally would <cfset> the outer query variable to a local and use that in the inner query. Works fine!
I was just sitting in the train and thought of a rowcopy function so you can copy an entire row of data in a single structure. Don't know exactly the good use for it but here it is anyways...
<cffunction name="rowCopy" returntype="struct" output="No">
<cfargument name="q" required="Yes" type="query">
<cfargument name="rowindex" required="Yes" type="numeric">
<cfset var ret = structNew()>
<!--- Loop over the columnlist and get the rowindex needed. --->
<cfloop list="#q.columnlist#" index="column">
<cfset ret[column] = q[column][rowindex]>
</cfloop>
<cfreturn ret>
</cffunction>
<cfoutput query="SomeQuery">
<cfset row = rowCopy(qTest,currentRow)>
<cfloop query="OtherQuery">
<cfif row.id eq otherquery.id>
#row.name#
</cfif>
</cfloop>
</cfoutput>
Too bad you can't do
<cfset row = qTest[currentRow]>
If someone knows an easier way or better.. a better way, please feel free.
15156 viewed | 15 opinion(s) | del.icio.us | Digg it | Tjarko @ 30/11/05 18:20 cet
why not just do
<cfoutput query="SomeQuery">
<cfloop query="OtherQuery">
<p>#otherquery.somefield#</p>
<p>#somequery.name[somequery.currentrow]#</p>
</cfif>
</cfloop>
</cfoutput>
30 November 2005 18:26 cet
*edit*
that </cfif> should not be there
30 November 2005 18:28 cet
I actually never thought of that one. That should work just fine!!
30 November 2005 18:51 cet
Ray Horn wrote.... (
site)
Code runs faster then you recode it using CFSCRIPT... Just a hint.
Also I guess I have to wonder why the author of this post wanted to get a struct for a row when Query objects already provide this to you in the form of a struct of arrays with each array being a column name which means you can just as easily reference QueryObject.colName[row] which is also the same as QueryObject[colName[row]] which is the same as StructObj.colName once the row in in the StructObj which is also the same as StructObj[colName].
Or maybe I am just missing the whole point of this post completely ?!?
30 November 2005 23:20 cet
Brian Kotek wrote....
Actually cfscript is no faster than standard tags because they are both compiled down to the same Java bytecode.
30 November 2005 23:30 cet
@Ray: The author (that's me) clearly understands the fact that a query is a struct of arrays because if you look at the code that is in the function the exact same method is used. The reason for the post is that a student was having the problem that I sketched and I was just thinking of a more robust way of solving it.
Also in the past i have run into situations where it was handy to have the row of data extracted from the query... can't think of a
I am going to try the solution of Sean however but i'm not sure if the currentRow of the outer query doesn't return 1 as array index.
And the whole cfscript runs faster then a <cffunction> debate is something that I tested before and with loops of more then a 100.000 times the difference was a few milliseconds.. Besides the fact that all my functions reside in a <cfcomponent>.
But thanks for thinking along the same way!! I really appreciate the feedback.
01 December 2005 9:02 cet
The solution of Sean works just fine!! Learned something again :-)
<cfoutput query="qTest">
<cfloop query="qTest2">
#qTest2.tst_name# = #qTest.tst_name[qTest.currentRow]#<br />
</cfloop>
</cfoutput>
01 December 2005 9:08 cet
PJ wrote....
Thank you!I've been wanting to convert a query row to a structure for a long time.I wasn't able to find a reference on how a query is put together until now.
29 June 2006 20:49 cet
<cfquery name="qOrderType" datasource="#application.dsn#">
SELECT c.LOOKUP_ID, c.LOOKUP_CODE, c.DESCRIPTION
FROM POS_CUSTOMER_ORDER_TYPES a, POS_CUSTOMERS_V b, POS_LOOKUPS c
WHERE a.CUSTOMER_ID = b.CUSTOMER_ID
AND a.ORDER_TYPE_ID = c.LOOKUP_ID
ANDa.RECORD_STATUS = 'E'
AND TRUNC(SYSDATE)BETWEEN TRUNC(a.START_DATE) AND NVL(a.END_DATE,TRUNC(SYSDATE))
AND b.CUSTOMER_NUMBER = <cfqueryparam value="#session.stCustomer.CUSTOMER_CODE#" cfsqltype="CF_SQL_VARCHAR" >
</cfquery>
<cfquery name="GET_REQ_DATE" datasource="#application.dsn#">
select GET_REQ_DATE(<cfqueryparam value="#session.stCustomer.CUSTOMER_ID#" cfsqltype="CF_SQL_NUMERIC">,
<cfqueryparam value="#qOrderType.LOOKUP_CODE#" cfsqltype="CF_SQL_VARCHAR">)
AS req_date
from dual
</cfquery>
how am i going to implement the solution? I am new, sorry
25 August 2006 11:43 cet
Chris Peters wrote.... (
site)
Actually, I did have a need to extract a row out of a query without having the confusing syntax with all the brackets. Thanks Tjarko.
25 November 2007 5:45 cet
CF is very stupid! Why they dont put a index or a row field in the cfquery? Imagine if you do:
<cfquery query="jaja" row="r">
<cfscript>show(r);</script>
</cfquery>
Its more logical isnit? I cant believed that we need a hack to get the row, so ridiculous
25 March 2009 14:00 cet
jax wrote....
Thomas, why don't you go ahead and read through your own post again...
Maybe you would like to rephrase things a bit ??
25 March 2009 23:08 cet
Aaron Greenlee wrote.... (
site)
The author is right on with his original post. Sometimes, you don't know what columns are going to be returned. For example, his idea allows one to extract the current row during a loop to populate a bean. One could then populate any bean with any query without duplicating the code above.
@Thomas, ColdFusion does allow you direct access to the current row if you know the column names. Here is an example:
<cfscript>
myQuery = myDAO.helpThomasWithThings();
helpfulTopicsForThomas = myQuery.topics[1];
anotherTopicForThomas = myQuery.topics[2];
theLastTopicForThomas = myQuery.topics[myQuery.recordCount];
</cfscript>
22 May 2009 23:05 cet
thx a lot!
24 June 2010 16:07 cet
Garunga wrote....
This post was very helpful.Quick and simple solution to a problem that I had that was different from the author's case: I had query results and needed to extract a record to pass as an argument to an API method that takes the data as a struct.Different problem, same simple solution... helped me tremendously in my case.
As for disrespectful and ignorant comments, I'd suggest to Thomas (late I know but might as well say something):Instead of saying "This platform is stupid because it doesn't do x", it would benefit you more to ask, "Does this platform do x? I don't see support for it."You'd most likely get more than one person offering several different ways to do that thing you thought was impossible.It's not CF that is "very stupid".
Case in point: In addition to Aaron's suggestion of addressing specific rows in query results via array notation, you can also request specific rows in the cfquery tag itself using the "startrow" and "maxrows" attributes.
25 March 2011 15:43 cet
