返回查询行以及在sql查询中调用列的方式 [英] returning the query row with the way the columns are called in sql query
问题描述
我有一个查询，在其中我按自己想要的方式调用列，我无法使用查询的ColumnList，因为它按字母顺序对列进行排序，另一件事是我需要获取查询的特定行及其关联列结构：
I have a query where I am calling the columns like the way i want, I cannot use ColumnList of query because it sorts the column alphabatically, another thing i need to get the specific row of the query and its associated columns in a structure:
这是我要尝试以所需方式显示列的函数：
So here is my function which i am trying to bring the columns in the manner i want:
<cffunction name="rowToStruct" access="public" returntype="struct" output="false">
<cfargument name="queryObj" type="query" required="true" />
<cfargument name="row" type="numeric" required="true" />
<cfset var returnStruct = structNew()>
<cfset var colname = "">
<cfset arguments.queryObj = arrayToList(arguments.queryObj.getMeta().getColumnLabels())>
<cfloop list="#arguments.queryObj#" index="colname">
<cfset "returnStruct.#colname#" = arguments.queryObj[colname][arguments.row]>
</cfloop>
<cfreturn returnStruct/>
</cffunction>
在进行上述更改之前，功能如下：
before the above change the function was like this below:
<cffunction name="rowToStruct" access="public" returntype="struct" output="false">
<cfargument name="queryObj" type="query" required="true" />
<cfargument name="row" type="numeric" required="true" />
<cfset var returnStruct = structNew()>
<cfset var colname = "">
<cfloop list="#arguments.queryObj.columnList#" index="colname">
<cfset "returnStruct.#colname#" = arguments.queryObj[colname][arguments.row]>
</cfloop>
<cfreturn returnStruct/>
</cffunction>
一个以上的矿井给我一个错误：
Mine above one is giving me an error:
您试图取消引用类型为java.lang.String类型的标量变量作为具有成员的结构。
推荐答案
发生此错误的原因是因为您正在覆盖传递给函数的查询对象。新代码将 arguments.queryObj 的值重置为此处的简单字符串：
The reason for the error is because you are overwriting the query object passed into the function. The new code resets the value of arguments.queryObj to a simple string here:
<cfset arguments.queryObj = arrayToList(arguments.queryObj.getMeta().getColumnLabels())>
稍后在代码中，您尝试使用 arguments.queryObj 变量，就好像它仍然是一个查询对象。显然，这会导致错误，因为变量现在表示一个字符串：
Later in the code, you try and use the arguments.queryObj variable as if it were still a query object. Obviously, this causes an error because the variable now represents a string:
<cfset "returnStruct.#colname#" = arguments.queryObj[colname][arguments.row]>
解决方案是使用其他变量来存储列名列表。由于它也将是函数局部变量，因此请确保使用 local 或 var 适当地限定范围。
The solution is to use a different variable to store the list of column names instead. Since it will also be function local variable, just be sure to scope it properly with local or var.
也就是说，正如 Adam在他的回答中提到的，您需要的功能使用内置功能之一已被支持。因此，实际上并不需要使用 coldfusion.sql.QueryTable 查询类IMO的未记录方法。 GetMetaData（queryObject）返回包含两个键的结构数组：名称（列名称）， IsCaseSensitive （布尔值）。要遍历它，请使用数组循环而不是列表循环：
That said, as Adam mentioned in his answer, the feature you need is already supported using one of the built-in functions. So there is no really need to use the undocumented methods of the coldfusion.sql.QueryTable query class IMO. GetMetaData(queryObject) returns an array of structures containing two keys: Name (column name), IsCaseSensitive (boolean). To iterate through it, use an "array" loop instead of a "list" loop:
...
<cfset var returnStruct = {}>
<cfset var col = "">
<cfset var colArray = getMetaData(arguments.queryObj)>
<cfloop array="#colArray#" index="col">
<cfset returnStruct[col.Name] = arguments.queryObj[col.Name][arguments.row]>
</cfloop>
...
更新1：
我无法使用查询的ColumnList，因为它按字母顺序对列
进行排序
I cannot use ColumnList of query because it sorts the column alphabatically
这里更大的问题是CF结构没有顺序。即使您按正确的顺序将值添加到结构中，也不会保持该顺序。您要么需要在调用代码中解决该问题（即使用 GetMetaData（query）），要么让cffunction返回既有序的列名又行数据。后者似乎有点多余，但是这完全取决于您如何在应用程序中使用该功能。
The bigger issue here is that CF structures are not ordered. Even if you add the values to the structure in the proper order, that order will not be maintained. You either need to address that in the calling code (ie use GetMetaData(query)) OR have the cffunction return both the ordered column names AND the row data. The latter seems a bit redundant, but it all depends on how you are using the function in your application.
更新2：
如果确实需要排序的结构..还有一些Java类确实可以维护插入顺序，例如LinkedHashMap。由于它实现了 java.util.Map 它可以是用于 like CF结构（在大多数情况下）。
IF you really do need a sorted structure .. there are also some java classes that do maintain the insertion order, such as a LinkedHashMap. Since it implements java.util.Map it can be used like a CF structure (in most ways).
<cfset var returnStruct = createObject("java", "java.util.LinkedHashMap").init()>
NB：很好，一个常见的错误是忘记了Java是强类型的，而不是CF。如果您不知道，通常会有细微的细微差别可以轻易地咬住您他们。因此请记住这一点...并确保阅读API。
NB: While having access to java objects is great, a common mistake is to forget that java is strongly typed, unlike CF. There are often subtle nuances that can easily bite you if you are not aware of them. So keep that in mind ... and be sure to read the API.
这篇关于返回查询行以及在sql查询中调用列的方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
