Raymond Camden's ColdFusion Blog: Ask a Jedi: Showing Every Nth Record

Ask a Jedi: Showing Every Nth Record

Tony "I Suck at Math" Weeg asked me this over IM and I thought it would be fun to share. (And I know Tony, so don't think I'm giving him too much crap - my math is pretty rusty as well!) Basically, he had a large query (cue that's what she said joke) and wanted to show every nth row. This can be done easily enough using a simple MOD operation. The MOD operation does division on two numbers and returns the remainder. So if you want to show every 5 rows, you want to know when: Current Row divided by 5 has a remainder of 0. Here is a quick example:

view plain print about

<cfset people = queryNew("name","varchar")>
<cfloop index="x" from="1" to="342">
	<cfset queryAddRow(people)>
	<cfset querySetCell(people, "name", "Person #x#")>
</cfloop>

<cfdump var="#people#" top="10">

1  <cfset people = queryNew("name","varchar")>
2  <cfloop index="x" from="1" to="342">
3      <cfset queryAddRow(people)>
4      <cfset querySetCell(people, "name", "Person #x#")>
5  </cfloop>
6
7  <cfdump var="#people#" top="10">

This creates my fake query with 342 rows. I did a dump just to be sure it was that big.

I made my 'every N' logic dynamic using a variable:

view plain print about

<cfset nth = 6>
<cfloop index="x" from="1" to="#people.recordCount#">
	<cfif x mod nth is 0>
		<cfoutput>#people.name[x]#<br/></cfoutput>
	</cfif>
</cfloop>

1  <cfset nth = 6>
2  <cfloop index="x" from="1" to="#people.recordCount#">
3      <cfif x mod nth is 0>
4          <cfoutput>#people.name[x]#<br/></cfoutput>
5      </cfif>
6  </cfloop>

And that's it. I've used code like this before with tables where you want to show N people per row. In general it is pretty easy, but you normally want to have a 'complete' last row. This involves recognizing where you ended and adding some blank cells to finish the table. As an example:

view plain print about

<table border="1">
	<tr>
	<cfloop query="people">
		<cfoutput><td>#name#</td></cfoutput>
		<cfif currentRow mod nth is 0>
			</tr>
			<cfif currentRow neq recordCount>
			<tr>
			</cfif>
		</cfif>
	</cfloop>
	<!--- remainder? --->
	<cfif people.recordCount mod nth neq 0>
		<cfset padding = nth - (people.recordCount mod nth)>
		<cfoutput>#repeatString("<td> </td>", padding)#</cfoutput>
		</tr>
	</cfif>
</table>

1  <table border="1">
2      <tr>
3      <cfloop query="people">
4          <cfoutput><td>#name#</td></cfoutput>
5          <cfif currentRow mod nth is 0>
6              </tr>
7              <cfif currentRow neq recordCount>
8              <tr>
9              </cfif>
10          </cfif>
11      </cfloop>
12      
13      <cfif people.recordCount mod nth neq 0>
14          <cfset padding = nth - (people.recordCount mod nth)>
15          <cfoutput>#repeatString("<td> </td>", padding)#</cfoutput>
16          </tr>
17      </cfif>
18  </table>

After each cell I see if I'm at the end of my row, and if so, add a closing tr. If I'm not totally done, start a new row. After the loop I see if I had a remainder and if so, padd the table with some empty cells and a closing tr.

This entry was posted on Friday, February 20, 2009 at 8:41 AM. It was filed in the following categories: ColdFusion. It has been viewed 2,259 times and has 15 comments.

TweetBacks

Comments

[Add Comment] [Subscribe to Comments]

Comment 1 written by tony weeg on 20 February 2009, at 9:01 AM

:) thanks for the help this morning ray, the truth of the MOD part is that i was thoroughly ok using this:

<cfif i mod 2>

</cfif>

and showing every other row. the problem i had was how to show every 5th row... and once ray clued me into the necessity of adding "eq 0" to the mix, all was well, thanks again ray!!

so the final version ended up as:

<cfif ((i mod 5) = 0)>

</cfif>

because as ray explained, mod basically means, gimme the remainder
Comment 2 written by ulan on 20 February 2009, at 9:06 AM

I never knew it was the remainder
Comment 3 written by Timothy Farrar on 20 February 2009, at 9:09 AM

@Ray,

Just curious... I like the implementation here, but would the step attribute for the loop tag work also for this situation?
Comment 4 written by chris on 20 February 2009, at 9:11 AM

Why not use step?

<cfloop index="x" from="1" to="#people.recordCount#" step="6">
Comment 5 written by Timothy Farrar on 20 February 2009, at 9:12 AM

oh... I thought about it... only in the case of an index loop... so this solution is probably better as it works in more situations... never mind :)
Comment 6 written by Raymond Camden on 20 February 2009, at 9:13 AM

-sigh-

Timothy and Chris schooled us Tony. ;)

Although you have to modify your FROM:

<cfloop index="x" from="#nth#" to="#people.recordCount#" step="#nth#">
<cfoutput>#people.name[x]#<br/></cfoutput>
</cfloop>

If you don't start with Nth, then your values are off. So instead of 5,10,15 for counting by 5s, you get 1,6, 11.

Have I lost the respect of the CF world now? ;)
Comment 7 written by tony weeg on 20 February 2009, at 9:22 AM

sometimes we over think brother, its ok :) jedimaster status for you, and padawon status for me are still there
Comment 8 written by tony weeg on 20 February 2009, at 10:17 AM

one other cool thing ive learned through this was the ability to filter and get the results you want through straight SQL. assuming there are no deleted rows that would throw off the results, you could write your query to do this:

select *
from lcdsmetrics
where dateEntered between '02/19/2009' and '02/20/2009'
and (id % 5 = 0)
order by dateEntered

which would give you essentially the same thing as step="5" would but it would bring less records back from the server therefore decreasing the payload from the outset.
Comment 9 written by Chris Clark on 20 February 2009, at 10:20 AM

What if you were dealing with a ...very... large result set. Would it be possible (and would there be any benefit) to doing this in the actually query code, as opposed to the output code?
Comment 10 written by Chris Clark on 20 February 2009, at 10:21 AM

Haha ... Tony answered my question already.
Comment 11 written by Raymond Camden on 20 February 2009, at 10:22 AM

@tony - that assumes continuous IDs though. If you delete anything that would break it.

@Chris - yep - you would never do this over a billion records. But for a 'reasonable' size I think it is fine.
Comment 12 written by tony weeg on 20 February 2009, at 10:44 AM

yup and thats why i caveat'd that with the assumption of no deleted rows, but i bet you could #temp table that, and assign a new id field to count off of, and use that, right?
Comment 13 written by Raymond Camden on 20 February 2009, at 10:45 AM

Serves me right for looking at your code and not actually _reading_.
Comment 14 written by Adrian J. Moreno on 20 February 2009, at 10:53 AM

In reply to this post, I just posted a SQL approach to this that doesn't rely on the primary ID:

http://www.iknowkungfoo.com/blog/index.cfm/2009/2/...
Comment 15 written by Dave DuPlantis on 20 February 2009, at 11:17 AM

And then you can take Adrian's method and modify the WHERE clause if you need to start at a specific record (as Ray mentions above with respect to the cfloop approach).

To get every N records starting with record X, just replace 5 with N and 0 with X. (Assuming X < N; for X = N, use 0 for X as Adrian did in the example. If X > N, then you'll miss at least one record you wanted.)