Pagination is something that has always been somewhat difficult for
me. Especially optimizing it for large sites. To make it even harder,
the customer wants the first page to show three items, and every
subsequent page to show ten. Not hard enough? OK, they want page number
links instead of simple left/right arrow links, such as:
1 2 3 [4] Next »
Still Easy?
- Only show 4 page links at a time
- When you click ‘next’, show the next block of 4 pages
- When you click ‘prev’, show the previous block of 4 pages
- If you are on the first block of links, do not show the ‘prev’ link
- If you run out of records, only show the correct number of page links in that block
- If you run out of records, do not show the ‘next’ link
Remember, page 1 has 3 items. Page 2 has 10. So the math gets kinda
tricky when you don’t want to do ‘what page are we on’ statements all
over the place.
Here is my solution. All you have to do is enter your records per
page (“allperpage”), number of page links at a time (“blocksof”), and
the number of items on the first page (“firstperpage”).
The SQL query has been optimized to get as close to MySQL’s ‘LIMIT’
function as possible. We’re doing an INNER JOIN here, and sorting on
columns from both tables. I haven’t seen any examples of this
optimization technique with an inner join, or even sorting on more than
one column. We pull back the max rows we’d need, then trim the top with
a reverse sort, finally un-reverse the sort for the web page. WHEW.
Onto the code…
In this example, we’re going to show page blocks of 4. The first page has 3 items. Every other page has 6. The example uses articles which are in publications.
It looks like a monster. But half of it is comments.
<!--- CURRENT PAGE --->
<cfparam name="url.page" default="1">
<!--- RECORDS PER PAGE (ON ALL PAGES) --->
<cfparam name="variables.allperpage" default="6">
<!--- RECORDS PER PAGE (ON FIRST PAGE) --->
<cfparam name="variables.firstperpage" default="3">
<!--- NUMBER OF PAGE LINKS AT A TIME --->
<cfparam name="variables.blocksof" default="4">
<!--- COUNT NUMBER OF RECORDS TOTAL --->
<cfquery name="articlecount" datasource="MagLibrary">
SELECT count(articles.articleid) as articlecount
FROM articles INNER JOIN publications ON articles.publicationID = publications.publicationID
WHERE articles.active = 1
</cfquery>
<!--- RETURN ONLY THE NUMBER OF RECORDS WE NEED --->
<!--- THIS IS AS CLOSE TO MYSQL'S LIMIT X,Y AS WE'RE GONNA GET --->
<cfquery name="latest" datasource="#session.datasource#">
<!--- PAGE 1 IS SIMPLE. SUBSEQUENT PAGES GET COMPLICTED SINCE THE FIRST PAGE HAS A DIFFERENT NUMBER OF RECORDS --->
<!--- THIRD, ORDER OUR RESULTS --->
SELECT *
FROM (
<!--- SECOND, ONLY RETURN THE AMOUNT OF RECORDS WE NEED FOR THIS PAGE --->
SELECT TOP
<!--- THIS STEP ENSURES THE LAST PAGE HAS THE CORRECT AMOUNT OF RECORDS.
IGNOREING THIS CHECK ALWAYS GIVES THE LAST PAGE THE #PERPAGE# RECORDS
SO YOU'D END UP WITH SOME OF THE PREVIOS PAGE'S RECORDS --->
<cfif articlecount.articlecount lt (firstperpage + (allperpage * (url.page-1)))>
#articlecount.articlecount - (firstperpage + (allperpage * (url.page-2)))#
<Cfelse>
<cfif url.page is 1>
#firstperpage#
<cfelse>
#allperpage#
</cfif>
</cfif> *
FROM (
<!--- FIRST, GET UP TO THE AMOUNT WE NEED WITH CORRECT SORTING --->
SELECT TOP #firstperpage + (allperpage * (url.page-1))#
articles.title, articles.articleid, publications.title as pubtitle, publications.rank
FROM articles INNER JOIN publications ON articles.publicationID = publications.publicationID
WHERE articles.active = 1
<!--- OUR PREFERED ORDERING --->
ORDER BY publications.rank, pubtitle, articles.title, articles.articleid
) as t1
<!--- REVERSE THE ORDERING SINCE WE'RE SELECTING THE TOP --->
ORDER BY rank DESC, pubtitle DESC, title DESC, articleid DESC
) as t2
<!--- AND REORDER FOR DISPLAYING --->
ORDER BY rank, pubtitle, title, articleid
</cfquery>
<!--- 'TOP' IS THE LAST PAGE NUMBER LINK --->
<Cfset top = blocksof * ceiling((url.page+1)/blocksof)>
<cfoutput>
<!--- IF WE'RE PAST THE FIRST BLOCK OF PAGES, SHOW THE PREV LINK --->
<cfif url.page gt blocksof>
<a href="?page=#top-blocksof#" class="fullstorylink" style="text-decoration:underline;">« Prev</a>
</cfif>
<!--- LOOP THROUGH THE PAGES IN THIS BLOCK --->
<cfloop from="#top-blocksof+1#" to="#top#" index="a">
<cfif url.page is not a>
<a href="?page=#a#" class="fullstorylink" style="text-decoration:underline;">#a#</a>
<Cfelse>
<b>[#a#]</b>
</cfif>
<!--- IF WE'RE ON THE LAST BLOCK AND DON'T HAVE ENOUGH RECORDS TO COMPLETE THE BLOCK,
STOP CREATING THE LINKS FOR MORE PAGES, AND SET A FLAG TO NOT SHOW THE LINK FOR THE NEXT BLOCK --->
<cfif ((a-1) * allperpage) + firstperpage gte articlecount.articlecount>
<Cfset noNext=1><cfbreak>
</cfif>
</cfloop>
<!--- IF WE RAN OUT OF RECORDS, DO NOT SHOW THE LINK FOR THE NEXT BLOCK OF PAGES --->
<cfif not isdefined("noNext")>
<a href="?page=#top+1#" class="fullstorylink" style="text-decoration:underline;">Next »</a>
</cfif>
</cfoutput>
<!--- NOW IT'S A SIMPLE MATTER OF SHOWING THE RECORDS --->
<br><br>
<!--- WE'LL GROUP THE ARTICLES UNDER THEIR PUBLICATION--->
<cfoutput query="latest" group="pubtitle">
<h2>#pubtitle#</h2>
<cfoutput>
<a href="details.cfm?articleid=#articleid#">#title#</a>
</cfoutput>
</cfoutput>
Comments, suggestions, improvements are gladly welcomed.
ColdFusion, MS SQL SERVER, MySQL, Web Coding
Recent Comments