Great suggestion, and it worked. However, the QueryAddColumn function requires an array-name as its third parameter. So now I am getting the following error:
"Object of type class java.lang. String cannot be used as an array." On the following code: <CFSET temp = QueryAddColumn(unsortedResults, "Key", VARIABLES.keyList)> Dylan On 8/5/07, Raymond Camden <[EMAIL PROTECTED]> wrote: > Look here: > > <CFQUERY NAME="unsortedResults" DATASOURCE="#APPLICATION.dataSource#"> > SELECT SPEAKER_ID > FROM TOPIC > WHERE SPEAKER_ID IN (#VARIABLES.keyArray#) > </CFQUERY> > > Remember that you made variables.keyArray an array. You can't use an > array like that. Before you had made keyarray, you had keylist. Just > use keylist. > > On 8/5/07, Dylan Carlson <[EMAIL PROTECTED]> wrote: > > Will do Ray. Again, I appreciate any help you can give. > > > > --- start code --- > > > > <CFIF IsDefined("FORM.topic")> > > <CFSEARCH COLLECTION="myCollection" > > NAME="getTopics" > > CRITERIA="<WILDCARD>*#LCase(FORM.topic)#*"> > > <CFSET KeyList = ValueList(getTopics.key) /> > > <CFSET VARIABLES.keyArray = ListToArray(KeyList) /> > > </CFIF> > > > > <!--- if no results are returned, do not do the additional query ---> > > <!--- no files found for specified criteria? ---> > > <CFIF getTopics.RecordCount is 0> > > <P>No files found for <CFOUTPUT>"#FORM.topic#"</CFOUTPUT></P> > > <CFELSE> > > > > <!--- run a query to pull the information about the records that were > > returned as "matches" on the verity search ---> > > <CFQUERY NAME="unsortedResults" DATASOURCE="#APPLICATION.dataSource#"> > > SELECT SPEAKER_ID > > FROM TOPIC > > WHERE SPEAKER_ID IN (#VARIABLES.keyArray#) > > </CFQUERY> > > > > <!--- add score variable to query ---> > > <CFSET temp = QueryAddColumn(unsortedResults, "Key", > > VARIABLES.keyArray)> > > > > <!--- sort the query based on score ---> > > <CFIF Find("0",KeyList)> > > <CF_QUERYSORT QUERY="unsortedResults" > > SORTCOLUMN="Key" SORTORDER="Desc" > > SORTTYPE="Numeric" > > SORTEDQUERY="results"> > > </CFIF> > > </CFIF> > > > > <UL> > > <CFOUTPUT query="results"> > > <LI><A > > HREF="test_search_details.cfm?speakerID=#key#">#results.title#</A></LI> > > </CFOUTPUT> > > </UL> > > > > --- end code --- > > > > Hey, it's Sunday...a day to be lazy :) > > > > Dylan > > > > On 8/5/07, Raymond Camden <[EMAIL PROTECTED]> wrote: > > > I think it would help if you could repost the full code again, with > > > the issues. I could tyr to parse it from your first email, but I'm > > > lazy. ;) > > > > > > On 8/5/07, Dylan Carlson <[EMAIL PROTECTED]> wrote: > > > > Thanks Ray! I appreciate the quick response and suggestions. > > > > > > > > Admittedly, I "found" that code online, so anything that is old or > > > > deprecated should have been caught. For instance, the parameterExists > > > > function, I didn't know that it was deprecated, but regardless should > > > > have known to use an isDefined function instead. Thank you for > > > > bringing that to my attention. > > > > > > > > Ok re: number 2. I have changed my code to assign the valueList of > > > > keys to a local variable instead. However, in the query I now get a > > > > "Complex object types cannot be converted to simple values." error. > > > > > > > > --- start new query --- > > > > > > > > <CFQUERY NAME="unsortedResults" DATASOURCE="#APPLICATION.dataSource#"> > > > > SELECT SPEAKER_ID > > > > FROM TOPIC > > > > WHERE SPEAKER_ID IN (#VARIABLES.keyArray#) > > > > </CFQUERY> > > > > > > > > --- end new query --- > > > > > > > > With re: to your third comment. This is true, I am only getting one > > > > column and this same column is already being stored in my <cfindex> as > > > > the key. As I understand how the custom tag works, I need to "match" > > > > the verity results with the db results using "WHERE SPEAKER_ID IN > > > > (#VARIABLES.keyArray#)" ...(except I am getting an error when doing > > > > this, see number two from above)... Then the custom tag runs > > > > (http://www.adobe.com/cfusion/exchange/index.cfm?event=extensionDetail&extid=1000158) > > > > and sorts the list according to the column specified. Does that make > > > > sense? Would it be better to use a queryAddColumn and querySetCell as > > > > opposed to the custom tag (which does the same thing)? > > > > > > > > Finally, with re: to your last comment. Thank you for pointing that > > > > out. I completely overlooked that, since my code isn't even getting > > > > that far at this point. My new output looks like this: > > > > > > > > --- start new output --- > > > > > > > > <UL> > > > > <CFOUTPUT query="results"> > > > > <LI><A > > > > href="test_search_details.cfm?speakerID=#key#">#results.title#</A></LI> > > > > </CFOUTPUT> > > > > </UL> > > > > > > > > --- end new output --- > > > > > > > > Any thoughts or suggestions is much appreciated. > > > > > > > > Thanks. > > > > > > > > Dylan > > > > > > > > On 8/5/07, Raymond Camden <[EMAIL PROTECTED]> wrote: > > > > > I see a lot here - so forgive me if I jump around a bit. > > > > > > > > > > First off - don't use parameterExists. it was deprecated a _long_ time > > > > > ago. It isn't the cause of any of your problems, but you should be > > > > > using isDefined instead. > > > > > > > > > > Second - you get the valueList of keys from your Verity query. You > > > > > then store this in the application scope. Why? You could have an issue > > > > > right there if 2 people run your code at the same time. Because they > > > > > share the value, one person won't get the results they expect. I'd > > > > > just store the list of keys as a simple variable. > > > > > > > > > > Third - I see you are joining a Verity result with a db result. There > > > > > is nothing wrong with that, but I notice that you only get one column > > > > > from the db. If you are only getting one column, why not just store > > > > > that value in the Verity collection? You can use one of four custom > > > > > columns. > > > > > > > > > > Lastly - I have'nt used that custom tag before, but it seems like you > > > > > told it to return a sortedquery named results. But you don't even use > > > > > that query when you output. > > > > > > > > > > On 8/5/07, Dylan Carlson <[EMAIL PROTECTED]> wrote: > > > > > > Does any know how to sort by key from the results of a <cfsearch>? > > > > > > I'm > > > > > > using Verity. > > > > > > > > > > > > I found a way by creating a list from the returned keys, then > > > > > > assigning them to an array, then query the original data and > > > > > > matching > > > > > > it to the returned keys, then using a custom tag by Nate Weiss to > > > > > > sort > > > > > > the new query. It sounds as if it would work perfectly; however, > > > > > > I've > > > > > > run into a few issues. Here's what I have got thus far: > > > > > > > > > > > > --- start code --- > > > > > > > > > > > > <CFIF ParameterExists(FORM.topic)> > > > > > > <CFSEARCH COLLECTION="myCollection" > > > > > > NAME="getTopics" > > > > > > CRITERIA="#LCase(FORM.topic)#"> > > > > > > <CFSET KeyList = ValueList(getTopics.key)> > > > > > > <CFSET application.keyArray = ListToArray(KeyList)> > > > > > > </CFIF> > > > > > > > > > > > > <!--- if no results are returned, do not do the additional > > > > > > query ---> > > > > > > <!--- no files found for specified criteria? ---> > > > > > > <CFIF getTopics.RecordCount is 0> > > > > > > <CENTER>No files found for > > > > > > <CFOUTPUT>"#FORM.topic#"</CFOUTPUT> > > > > > > </CENTER> > > > > > > <CFELSE> > > > > > > > > > > > > <!--- run a query to pull the information about the records > > > > > > that were returned as "matches" on the verity search ---> > > > > > > <CFQUERY NAME="unsortedResults" > > > > > > DATASOURCE="#APPLICATION.dataSource#"> > > > > > > SELECT speaker_id > > > > > > FROM topic > > > > > > WHERE speaker_id IN (#application.keyArray#) > > > > > > </CFQUERY> > > > > > > > > > > > > <!--- add score variable to query ---> > > > > > > <CFSET temp = QueryAddColumn(unsortedResults, "Key", > > > > > > application.keyArray)> > > > > > > > > > > > > <!--- sort the query based on score ---> > > > > > > <CFIF Find("0",KeyList)> > > > > > > <CF_QUERYSORT QUERY="unsortedResults" > > > > > > SORTCOLUMN="Key" > > > > > > SORTORDER="Desc" > > > > > > SORTTYPE="Numeric" > > > > > > SORTEDQUERY="results"> > > > > > > </CFIF> > > > > > > </CFIF> > > > > > > > > > > > > <ul> > > > > > > <cfoutput query="getTopics"> > > > > > > <li><a > > > > > > href="test_search_details.cfm?speakerID=#key#">#getTopics.title#</a></li> > > > > > > </cfoutput> > > > > > > </ul> > > > > > > > > > > > > --- end code --- > > > > > > > > > > > > Any help or direction would be much appreciated. > > > > > > > > > > > > Thanks. > > > > > > > > > > > > Dylan > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:285440 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4