qq说说你想做我的什么_“你想做什么？”-CSDN博客

qq说说你想做我的什么

Ok I know this isn’t part 2 of my CFC’s for the common developer but I wanted to share with you something cool I did today. I had a query of locations which needed an extra row inserted into it. Now this sounds like an easy thing to do but there’s a catch, isn’t there always a catch. I had to have the query sort the locations alphabetically but this new row which I needed to insert had to be first in line. So my task was to:

好的，我知道对于普通开发人员来说，这不是我的CFC的第二部分，但是我想与大家分享我今天所做的一些很棒的事情。我有一个位置查询，需要在其中插入额外的一行。现在这听起来很容易，但是有一个陷阱，并不总是有一个陷阱。我必须让查询按字母顺序对位置进行排序，但是我需要插入的这一行必须排在第一行。所以我的任务是：

Query the database and get a listing of all locations and their respective ID’s and order them alphabetically
查询数据库并获得所有位置及其各自ID的列表，并按字母顺序排序
Insert into the first row of this same query a listing for Corporate Office with an ID of -99
在同一查询的第一行中插入ID为-99的Corporate Office列表。

So here’s my query:

所以这是我的查询：

<cfquery name="getLocations" datasource="MyDSN">
     SELECT franchiseeName + ' (' + city + ', ' + stateProvince + ')' AS lookupdisplay, franchiseeid AS lookupkey
     FROM franchisee
     ORDER BY lookupdisplay
</cfquery>

This returns a nice query result set with two columns lookupDisplay which has my location name / state and lookupKey which has the ID for this particular location. So how do I get another row into this query? Well adding a row is easy we can simple do:

这将返回一个不错的查询结果集，其中包含两列lookupDisplay (具有我的位置名称/状态)和lookupKey (具有此特定位置的ID)。那么，如何在查询中获得另一行呢？添加一行很容易，我们可以简单地做到这一点：

<cfset QueryAddRow(getLocations,1)>

The QueryAddRow function will insert a single blank row (changing the 1 to some other number would insert that number of rows) into my result set getLocations. No in order for me to populate this row I just need to do the following:

QueryAddRow函数将在我的结果集getLocations中插入一个空白行(将1更改为其他数字将插入该行数)。否为了让我填充此行，我只需要执行以下操作：

<cfset QuerySetCell(getLocations, 'lookupDisplay',Corporate Posting (Waco, TX)')> <cfset QuerySetCell(getLocations, 'lookupkey','-99')>

Using the QuerySetCell function I can populate my empty rows BUT there is a problem with this. The row is at the bottom of my query and I need it to be at the top. My first thought was to simply do a Query of a Queries and just reorder everything BUT my added value begins with a C and I know I have locations which start with A and B (about 8 total to be exact). So what is a developer to do? Well I start to think about things (specifically data structures) which are easy to shuffle things around in. To me arrays are the easiest data structure to sort and manipulate, but query results aren’t simple arrays. They are more like arrays with structures in each position; each row is an array positions and the columns make up the structure.

使用QuerySetCell函数，我可以填充空行，但是存在问题。该行位于查询的底部，我需要将其置于顶部。我的第一个想法是简单地执行查询查询，然后对所有内容重新排序，但是我增加的价值以C开头，我知道我的位置以A和B开头(确切地说总共有8个)。那么开发人员该怎么办？好吧，我开始考虑易于将事物混入其中的事物(特别是数据结构)。对我来说，数组是最易于排序和操作的数据结构，但查询结果并不是简单的数组。它们更像是在每个位置都有结构的数组。每行是一个数组位置，而列组成了结构。

Ok so I need to write some code which will take my query and turn it into an array of structures. Easy right? Sure is thanks to www.cflib.org! If you’ve never been to CFlib you need to go there right now, it so rocks! It’s a site full of great functions for some simple, and some complex things. A quick search in the data manipulation library got me the two functions I needed ArrayOfStructuresToQuery (by David Crawford) and QueryToArrayOfStructures (by Nathan Dintenfass).

好的，所以我需要编写一些代码来接受查询并将其转换为结构数组。容易吧？当然要感谢www.cflib.org ！如果您从未去过CFlib，那么现在就需要去那儿，这真是太糟糕了！这是一个功能强大的网站，可以处理一些简单和复杂的事情。在数据操作库中进行的快速搜索为我提供了我需要的两个函数： ArrayOfStructuresToQuery (由David Crawford 编写 )和QueryToArrayOfStructures (由Nathan Dintenfass编写)。

With these two functions I could turn my query into one wicked array (you should try dumping it out and take a peak for yourself!) which I could then use some code to bubble sort my last record to the top. So my next step after my query has been created and my record has been added to it is to turn my query into an array of structures like so:

使用这两个函数，我可以将查询转换为一个邪恶的数组(您应该尝试将其转储出去，为自己付出一个顶峰！)，然后可以使用一些代码将我的最后一个记录冒泡排序到顶部。因此，在创建查询并添加记录后，我的下一步就是将查询转换为如下所示的结构数组：

<cfset getLocationsArray = QueryToArrayOfStructures(getLocations)>

Then I could do a CFLoop against this array and use the ArraySwap function to create my bubble sort like so:

然后，我可以对该数组执行CFLoop并使用ArraySwap函数创建气泡排序，如下所示：

<cfloop index="j" from="1" to="#ArrayLen(getLocationsArray)#">
     <cfset ArraySwap(getLocationsArray,j,ArrayLen(getLocationsArray))>
</cfloop>

Let me break down those last 3 lines for you. Line one is my CFLoop tag and in particular it’s an index loop, you programmer types will know this as a FOR loop but in ColdFusion it’s just an index loop. I set my index to j which is going to be a variable I can use inside my CFLoop tags. The from attribute is set to 1, remember in ColdFusion arrays always start with 1 while most other languages start at 0. The to attribute is actually the result of my ArrayLen() function, which is passed in the result of the QueryToArrayOfStructures functions, this ArrayLen function will return a numeric value equal to the number of array positions to ensure I don’t loop outside of my array. If I just set the to attribute to say 50 but only had 45 array positions ColdFusion would generate an ArrayIndexOutOfBounds error, basically telling me that I tired to reference something which didn’t exist.

让我为您分解最后三行。第一行是我的CFLoop标记，特别是它是一个索引循环，您的程序员类型将其称为FOR循环，但在ColdFusion中，它只是一个索引循环。我将索引设置为j，这将是我可以在CFLoop标签内使用的变量。该从属性设置为1，记得的ColdFusion数组总是从1开始，而大多数其他语言从0开始的，以属性实际上是我ArrayLen()函数，这是在的QueryToArrayOfStructures函数的结果传递的结果，这ArrayLen函数将返回一个等于数组位置数的数值，以确保我不会在数组外循环。如果我只是将to属性设置为50，但是只有45个数组位置，那么ColdFusion将生成ArrayIndexOutOfBounds错误，这基本上告诉我，我已经厌倦了引用不存在的东西。

Ok so line 2 uses the ArraySwap function to swap whatever position I’m currently at, that’s the j variable, with what ever is at the bottom of my array thereby causing whatever is at the bottom to rise up to the top. Confused? yea me too, so lets talk this through.

好的，所以第2行使用ArraySwap函数将我当前所处的任何位置(即j变量)与数组底部的内容交换，从而使底部的内容升至顶部。困惑？是的，我也是，所以让我们谈谈。

Ok so lets assume my array only has 5 positions in it and looks something like so:

好的，假设我的数组中只有5个位置，看起来像这样：

getLocationsArray[1] = Struct AgetLocationsArray[2] = Struct BgetLocationsArray[3] = Struct CgetLocationsArray[4] = Struct DgetLocationsArray[5] = Struct E

getLocationsArray [1] =结构AgetLocationsArray [2] =结构BgetLocationsArray [3] =结构CgetLocationsArray [4] =结构DgetLocationsArray [5] =结构E

Now the goal here is to get Struct E to be at the top and then for everything else to go A – D. So at the end of my first pass in my CFLoop my array will look like so:

现在，这里的目标是使Struct E处于顶部，然后让其他所有东西进入A-D。因此，在我的CFLoop第一次遍历结束时，我的数组将如下所示：

getLocationsArray[1] = Struct EgetLocationsArray[2] = Struct BgetLocationsArray[3] = Struct CgetLocationsArray[4] = Struct DgetLocationsArray[5] = Struct A

getLocationsArray [1] =结构EgetLocationsArray [2] =结构BgetLocationsArray [3] =结构CgetLocationsArray [4] =结构DgetLocationsArray [5] =结构A

Pass #2 will swap out what ever is in position 2 with whatever is in position 5

2号通行证将把位置2的东西换成位置5的东西

getLocationsArray[1] = Struct EgetLocationsArray[2] = Struct AgetLocationsArray[3] = Struct CgetLocationsArray[4] = Struct DgetLocationsArray[5] = Struct B

getLocationsArray [1] =结构EgetLocationsArray [2] =结构AgetLocationsArray [3] =结构CgetLocationsArray [4] =结构DgetLocationsArray [5] =结构B

Pass #3 will swap out position 3 with positions 5

3号通行证会将位置3换成位置5

getLocationsArray[1] = Struct EgetLocationsArray[2] = Struct AgetLocationsArray[3] = Struct BgetLocationsArray[4] = Struct DgetLocationsArray[5] = Struct C

getLocationsArray [1] =结构EgetLocationsArray [2] =结构AgetLocationsArray [3] =结构BgetLocationsArray [4] =结构DgetLocationsArray [5] =结构C

Pass #4 will swap out position 4 with position 5

通道＃4将把位置4换成位置5

getLocationsArray[1] = Struct EgetLocationsArray[2] = Struct AgetLocationsArray[3] = Struct BgetLocationsArray[4] = Struct CgetLocationsArray[5] = Struct D

getLocationsArray [1] =结构EgetLocationsArray [2] =结构AgetLocationsArray [3] =结构BgetLocationsArray [4] =结构CgetLocationsArray [5] =结构D

Now at this point it’s good but our loop as it’s coded now will do another swap between position 5 and position 5 which results in nothing really happening.

现在，这很好，但是我们现在编码的循环将在位置5和位置5之间进行另一次交换，这实际上什么也没有发生。

So now you can see that I have exactly what I needed. The great thing about this is if I needed it to appear at position 3 all I have to do is change the from value in my CFLoop to 3 and the same thing will happen but it will work from 3 to 5.

因此，现在您可以看到我完全有需要。很棒的事情是，如果我需要将它显示在位置3上，我所要做的就是将CFLoop中的value更改为3，那么同样的事情也会发生，但是它会从3变为5。

Ok now that everything is the way I want it I just need to turn it back into a query result set using:

好的，现在一切都是我想要的方式，我只需要使用以下方法将其返回为查询结果集：

<cfset getLocations = ArrayOfStructuresToQuery(getLocationsArray)>

Using the ArrayOfStructuresToQuery function takes me back to my query which now has the data in the exact order I wanted it.

使用ArrayOfStructuresToQuery函数使我回到查询中，该查询现在具有与所需顺序完全相同的数据。

Not to shabby huh? How would you have approached the problem?

不要破旧吧？您将如何解决这个问题？