I need help with a little custom ordering of query results

Need to display all rows of a query, but start with a specified row

Page 1 of 1

14 Replies - 2307 Views - Last Post: 17 April 2008 - 05:31 PM Rate Topic: -----

#1 jorvis  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 11
  • Joined: 04-April 08

I need help with a little custom ordering of query results

Post icon  Posted 04 April 2008 - 07:15 PM

Hi there.

I've been searching for quite some time, but have yet to find a solution to my problem.

I need to find a way to display all the records in a query, but begin the list with a record that is "out of order"

For example, if my query returns 6 rows, I'd like to present those rows as:

Row 4
Row 5
Row 6
Row 1
Row 2
Row 3

I know the first answer that will jump to mind is "use the Order By clause!" I thought of this too. Unfortunately, the other fields I would order by are not unique, so I can't really use an alphabetical or numerical ordering.

I also tried using
<cfoutput query="myQuery" startrow="4">
but that returns only row 4 through the end (I don't get rows 1-3 outputted)

If anyone knows of a way to output ALL rows from a query, but begin with row X, please let me know.

Thanks very much.

Is This A Good Question/Topic? 0
  • +

Replies To: I need help with a little custom ordering of query results

#2 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: I need help with a little custom ordering of query results

Posted 04 April 2008 - 08:11 PM

View Postjorvis, on 4 Apr, 2008 - 07:15 PM, said:

I know the first answer that will jump to mind is "use the Order By clause!" I thought of this too. Unfortunately, the other fields I would order by are not unique, so I can't really use an alphabetical or numerical ordering.

I also tried using
<cfoutput query="myQuery" startrow="4">
but that returns only row 4 through the end (I don't get rows 1-3 outputted)

If anyone knows of a way to output ALL rows from a query, but begin with row X, please let me know.

Thanks very much.


What is the logic determining the output order? For example, do you divide the number of rows / 2 and then output the second half

row 4
row 5
row 6

followed by the first half

row 1
row 2
row 3

?

Without knowing the logic, the first thing that comes to mind is to use (2) cfoutput's

<cfoutput startrow="4" endrow="6">....</cfoutput>
<cfoutput startrow="1" endrow="3">....</cfoutput>

Though there may be a more elegant method, depending on your logic.

Also, some databases provide a row number that can be used for ordering. So an order by clause might still be an option. Which one are you using?
Was This Post Helpful? 0
  • +
  • -

#3 jorvis  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 11
  • Joined: 04-April 08

Re: I need help with a little custom ordering of query results

Posted 04 April 2008 - 10:10 PM

View Postsansclue, on 4 Apr, 2008 - 08:11 PM, said:

What is the logic determining the output order? For example, do you divide the number of rows / 2 and then output the second half

row 4
row 5
row 6

followed by the first half

row 1
row 2
row 3

?

Without knowing the logic, the first thing that comes to mind is to use (2) cfoutput's

<cfoutput startrow="4" endrow="6">....</cfoutput>
<cfoutput startrow="1" endrow="3">....</cfoutput>

Though there may be a more elegant method, depending on your logic.

Also, some databases provide a row number that can be used for ordering. So an order by clause might still be an option. Which one are you using?


I'm using a mySQL db, and honestly, I don't really care about the output order at this point... It could be 4,1,2,3,5,6 - as long as the specified record (in this case, record #4) is outputted first.

Maybe a little background would help: (I'm about 5 beers in though, so please forgive me if I'm not crystal clear :D )

I'm trying to build a management application for a flash-based slideshow gallery that is powered by an XML file. The XML file is being dynamically generated from a CF page that pulls database content. In my database, I have one table that lists all my albums, and another that lists all the images (images are tied to the albums by an ID#).

Right now, I'm focusing on just the albums. I want to be able to send a variable to the XML-generating CF page that tells it which album should be loaded first. The idea is that the user clicks on a link to open the slideshow to album #4 (therefore, album #4 must be first in the XML file).

When the user is viewing the slideshow, they can click on the "Gallery" button to view all available albums within the gallery. The order of the other albums in the gallery is unimportant to me, as long as I can give them the requested album (#4) first, so that they start off viewing the album they wanted to see.


Here's the CF/XML file as I have it right now:
<?xml version="1.0" encoding="UTF-8"?>
<cfheader name="pragma" value="no-cache">
<cfcontent type="text/xml">
<cfinclude template="includes/query_albums.cfm">
<cfinclude template="includes/query_images.cfm">
<gallery>
	<cfset start="1">
	<cfif isDefined("startAt")>
		<cfset start = startAt>
	</cfif>

<cfoutput>
	<cfloop query="getAlbums">
		<album title="#albumName#" description="#albumDescription#" lgPath="images/#albumPath#/Large/" tnPath="images/#albumPath#/Thumbs/">
		<cfset currentAlbum = "#albumID#">
			<cfquery name="getTheseImages" dbtype="query">
				SELECT
					filename,
					title,
					caption,
					link,
					target,
					delay
				FROM
					getImages
				WHERE
					getImages.albumID = #currentAlbum#
			</cfquery>
			<cfloop query="getTheseImages">
				<img src="#filename#" <cfif title IS NOT "">title="#title#" </cfif>caption="#caption#" <cfif link IS NOT "">link="#link#" <cfif target IS NOT "">target="#target#"</cfif></cfif> <cfif delay IS NOT "0">delay="#delay#"</cfif> />
			</cfloop>
		</album>
	</cfloop>
</gallery>
</cfoutput>




Any thoughts?
Was This Post Helpful? 0
  • +
  • -

#4 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: I need help with a little custom ordering of query results

Posted 04 April 2008 - 11:39 PM

View Postjorvis, on 4 Apr, 2008 - 10:10 PM, said:

I'm using a mySQL db, and honestly, I don't really care about the output order at this point... It could be 4,1,2,3,5,6 - as long as the specified record (in this case, record #4) is outputted first.


In that case you can use an order by. Just add a CASE statement to sort the selected album number first. Then order by whatever other columns you want and output the query as usual.

SELECT	CASE WHEN albumID = #selectedAlbumID# THEN 1
						 ELSE  2
			   END AS SortColumn,
			   ... other columns
FROM	 YourTable
ORDER BY  SortColumn,  ... other sort columns ...



View Postjorvis, on 4 Apr, 2008 - 10:10 PM, said:

Maybe a little background would help: (I'm about 5 beers in though, so please forgive me if I'm not crystal clear :D )


Wow. You are more crystal clear than I am with 0 beers ;)



Also, you could probably eliminate the inner query and replace it with a JOIN instead.
Was This Post Helpful? 0
  • +
  • -

#5 jorvis  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 11
  • Joined: 04-April 08

Re: I need help with a little custom ordering of query results

Posted 05 April 2008 - 08:44 AM

GENIUS!!!

I've never knew you could use a CASE statement in SQL, but it's working like a charm, so I'm reading up on it now.

Thank you so much - I was driving myself nuts all night looking for a solution for this. I owe you my sanity.


Next step is to figure out how to define the order of the images in each album - but that should be a little more permanent, so I think I'm going to create a "position" field in the images table. I'm pretty sure I got that one on lockdown, but if I botch it, I'll be back here, crying for help :D
Was This Post Helpful? 0
  • +
  • -

#6 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: I need help with a little custom ordering of query results

Posted 05 April 2008 - 06:31 PM

View Postjorvis, on 5 Apr, 2008 - 08:44 AM, said:

GENIUS!!!

I've never knew you could use a CASE statement in SQL, but it's working like a charm, so I'm reading up on it now.

Thank you so much - I was driving myself nuts all night looking for a solution for this. I owe you my sanity.


Next step is to figure out how to define the order of the images in each album - but that should be a little more permanent, so I think I'm going to create a "position" field in the images table. I'm pretty sure I got that one on lockdown, but if I botch it, I'll be back here, crying for help :D


You're welcome.

Yes for a more permanent ordering, a "position" column sounds like a good approach.
Was This Post Helpful? 0
  • +
  • -

#7 jorvis  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 11
  • Joined: 04-April 08

Re: I need help with a little custom ordering of query results

Posted 06 April 2008 - 08:55 PM

I've been kicking this around the last 2 days, and I'm having a bit of trouble with the logic I'm using to re-arrange these records. I feel like I'm so close, yet so far...

I've set up a page that displays all the current records - each record has a hidden form input with the current/old value, and a text input where the user can enter the new position. (Kinda like the position function in the "My Queue" feature on the Netflix website)

My form is submitted via an onchange call in each text input, so anytime the user changes any value, two lists are returned: one list of old positions, and one list of new positions. I'm trying to compare the two lists, find the difference between the two, and use that info to make the swap...

Here's what I have now:

(To save space here, I'm setting the lists manually instead of pasting the code for the whole page, form and all)

<cfset newImagePosition = "1,2,3,4,1">
<cfset oldImagePosition = "1,2,3,4,5">

<cfset list1 = "#newImagePosition#">
<cfloop list="#oldImagePosition#" index="thisItem">

<cfset list2 = "#thisItem#">
<cfset occurrences = "#ListValueCount(newImagePosition,thisItem)#">
<cfscript>
function ListInListFind(list1,list2)
{
 for (i=1; i LTE ListLen(List1); i=i+1) {
  if (ListFindNoCase(List2, ListGetAt(List1, i))){
   return i;
  break;
  }
 }
 return 0;
}
</cfscript>

<cfoutput>Was: #thisItem#.  Is now: #ListInListFind(list1,list2)# [#occurrences#]<br></cfoutput>
</cfloop>

</cfif>



Any suggestions?

This post has been edited by jorvis: 06 April 2008 - 08:58 PM

Was This Post Helpful? 0
  • +
  • -

#8 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: I need help with a little custom ordering of query results

Posted 07 April 2008 - 05:44 PM

View Postjorvis, on 6 Apr, 2008 - 08:55 PM, said:

My form is submitted via an onchange call in each text input, so anytime the user changes any value, two lists are returned: one list of old positions, and one list of new positions. I'm trying to compare the two lists, find the difference between the two, and use that info to make the swap...


There are at least two ways you could do it. You could update all of the records whenever anything changes - OR - do what you are attempting now, which is updated only the changed items.

The first method is simpler. But a disadvantage is it can potentially update more records.

The second method usually impacts less records. But, the logic is slightly more difficult. Also, you have to consider "state". ie The position order may have changed since you first displayed the form. Though that issue mainly applies more to multiple users updating the same information at one time.

In cases like this I typically use the first method. I find it simpler and less error prone.
Was This Post Helpful? 0
  • +
  • -

#9 jorvis  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 11
  • Joined: 04-April 08

Re: I need help with a little custom ordering of query results

Posted 07 April 2008 - 07:33 PM

View Postsansclue, on 7 Apr, 2008 - 05:44 PM, said:

There are at least two ways you could do it. You could update all of the records whenever anything changes - OR - do what you are attempting now, which is updated only the changed items.

The first method is simpler. But a disadvantage is it can potentially update more records.

The second method usually impacts less records. But, the logic is slightly more difficult. Also, you have to consider "state". ie The position order may have changed since you first displayed the form. Though that issue mainly applies more to multiple users updating the same information at one time.

In cases like this I typically use the first method. I find it simpler and less error prone.


In either case, I need to figure out how to swap the value that has been changed with the value it is being turned into.

That's where I'm running into trouble. I've been poring over the CF reference on adobe.com, and I just found the ArraySwap function a few minutes ago, but I'm a little unclear on how to identify which positions in the array should be swapped so that I can pass those positions to the function.
Was This Post Helpful? 0
  • +
  • -

#10 jorvis  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 11
  • Joined: 04-April 08

Re: I need help with a little custom ordering of query results

Posted 07 April 2008 - 07:48 PM

Nix that - I think I got it...

<cfset newImagePosition = "1,2,3,4,1">
<cfset oldImagePosition = "1,2,3,4,5">

<cfset oldArray = ListToArray(oldImagePosition)>

<cfset currentRow = 1>
<cfloop list="#oldImagePosition#" index="thisItem">

<cfset list2 = "#thisItem#">
<cfset occurrences = "#ListValueCount(newImagePosition,thisItem)#">

<cfif occurrences IS 2>
	<cfset doubleValue = currentRow>
<cfelseif occurrences IS 0>
	<cfset emptyValue = currentRow>
</cfif>

<cfset currentRow = currentRow+1>
</cfloop>

<cfset updatedValues = ArraySwap(oldArray,doubleValue,EmptyValue)>

<cfdump var="#oldArray#">



*big effin' SIGH*

Now it seems so simple... Sometimes I love being self-taught, and sometimes I just want it to friggin work, y'know?
Was This Post Helpful? 0
  • +
  • -

#11 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: I need help with a little custom ordering of query results

Posted 07 April 2008 - 09:27 PM

View Postjorvis, on 7 Apr, 2008 - 07:48 PM, said:

Now it seems so simple... Sometimes I love being self-taught, and sometimes I just want it to friggin work, y'know?


I totally hear you. Sorry I didn't provide an example. My brain is fried and I can barely form a coherent thought right now, let alone working code ;-)

What I was trying to get at, in my round-about way, was if you just accept a list of the new positions, you don't need to figure out what is changed. Just loop through and update the records. That is generally the simplest, though not always most efficient approach. Personally I find it less problematic all around. Especially with multi-threading. Though I have also used a stored procedure approach as well.

Enough rambling. I am glad you worked it out :)
Was This Post Helpful? 0
  • +
  • -

#12 jorvis  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 11
  • Joined: 04-April 08

Re: I need help with a little custom ordering of query results

Posted 08 April 2008 - 06:18 PM

No worries - you've already been more help than I ever expected to get from a forum :D

Took me a bit to get the update queries working properly... records were getting updated in the most random of orders, and I couldn't figure out what the hell was going on... then I realized that I was using the albumID and image position in my WHERE clause and I needed to use the imageID instead...

Here's what the final result came to be:

Form inputs:
<input type="text" name="newImagePosition" value="#imagePosition#" style="width: 15px;" onchange="document.updateAlbum#albumID#.action='editAlbum.cfm?selectAlbum=#albumID#&update'; document.updateAlbum#albumID#.submit();">
<input type="hidden" name="oldImagePosition" value="#imagePosition#">
<input type="hidden" name="imageID" value="#imageID#">



CF processing & DB update
<cfif isDefined("update")>

	<cfset updateIDs = ListToArray(imageID)>
	<cfset updateArray = ListToArray(oldImagePosition)>
	
	<cfset currentRow = 1>
	<cfloop array="#updateArray#" index="thisItem">
		<cfset occurrences = "#ListValueCount(newImagePosition,thisItem)#">
		
		<cfif occurrences IS 2>
			<cfset doubleValue = currentRow>
		<cfelseif occurrences IS 0>
			<cfset emptyValue = currentRow>
		</cfif>
		
		<cfset currentRow = currentRow+1>
	</cfloop>
	
	<cfset updatedValues = ArraySwap(updateArray,doubleValue,EmptyValue)>
	
	<cfset updateRow = 0>
	<cfloop array="#updateArray#" index="thisUpdateItem">
		<cfset thisID = updateIDs[#updateRow#+1]>
		<cfquery datasource="#application.DSN#" dbtype="query">
			UPDATE gallery_Images
			SET imagePosition = #thisUpdateItem#
			WHERE imageID = #thisID#
			LIMIT 1;
		</cfquery>
	<cfset updateRow = updateRow+1>

	</cfloop>
	
</cfif>



If you have any suggestions that would optimize the code, I'd love to hear 'em.

Right now, I'm working on replacing the text inputs with dropdown menus. Once I got to use this, I realized that the text inputs were lame. You have to alter the value, then click out of the input box before the update happens. I think this would be a lot more user-friendly with a dropdown that submits onchange instead.

This post has been edited by jorvis: 08 April 2008 - 06:24 PM

Was This Post Helpful? 0
  • +
  • -

#13 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: I need help with a little custom ordering of query results

Posted 12 April 2008 - 12:46 PM

View Postjorvis, on 8 Apr, 2008 - 06:18 PM, said:

If you have any suggestions that would optimize the code, I'd love to hear 'em.

Right now, I'm working on replacing the text inputs with dropdown menus. Once I got to use this, I realized that the text inputs were lame. You have to alter the value, then click out of the input box before the update happens. I think this would be a lot more user-friendly with a dropdown that submits onchange instead.


If you're updating onchange then the action seems more like a "move up" or "move down" option. In which case providing a set of arrows might be more intuitive to users than either text boxes or select lists. It would also simplify your code. You would not have to worry about duplicate position numbers. Just use javascript to swap the imagePosition values and on submit loop through the list of values and do your updates. No need to identify what has changed.

Are the imagePositions significant? ie Do they have to be contiguous or just unique? If they only need to be unique you could store just the selected image id in a hidden field and a direction "move up" or "move down". Then use a few sql queries to shift the image position up or down.

But whatever method you choose you should use transactions. Otherwise if another request is submitted at the same time, even from the same user, the results will get totally messed up ;)
Was This Post Helpful? 0
  • +
  • -

#14 jorvis  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 11
  • Joined: 04-April 08

Re: I need help with a little custom ordering of query results

Posted 16 April 2008 - 05:58 AM

View Postsansclue, on 12 Apr, 2008 - 12:46 PM, said:

If you're updating onchange then the action seems more like a "move up" or "move down" option. In which case providing a set of arrows might be more intuitive to users than either text boxes or select lists. It would also simplify your code. You would not have to worry about duplicate position numbers. Just use javascript to swap the imagePosition values and on submit loop through the list of values and do your updates. No need to identify what has changed.

Are the imagePositions significant? ie Do they have to be contiguous or just unique? If they only need to be unique you could store just the selected image id in a hidden field and a direction "move up" or "move down". Then use a few sql queries to shift the image position up or down.

But whatever method you choose you should use transactions. Otherwise if another request is submitted at the same time, even from the same user, the results will get totally messed up ;)


Haven't ever used transactions before... I guess it's time to whip out Google :D
Was This Post Helpful? 0
  • +
  • -

#15 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: I need help with a little custom ordering of query results

Posted 17 April 2008 - 05:31 PM

View Postjorvis, on 16 Apr, 2008 - 05:58 AM, said:

Haven't ever used transactions before... I guess it's time to whip out Google :D


Take a look at the cftransaction tag. It greatly simplifies using transactions :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1