Implementation of pagination in coldfusion

Question

We are trying to implement pagination in coldfusion.So that I need to make changes in sql query to fetch only a perticular number of data and when clicking on next button, next set of data should be queried.

  SELECT    History.*,User.FirstName,User.LastName
  FROM History 
  LEFT OUTER JOIN User ON History.UserID = User.UserID
  WHERE History.UserID = <CFQUERYPARAM VALUE="#UserID#" CFSQLTYPE="CF_SQL_INTEGER">   
  AND Type IS NOT NULL
  AND SubType IS NOT NULL 
  ORDER BY #OrderBy# #sort#

Can anyone help me to do the same with above given query.

example query :

   SELECT * FROM (
         SELECT ROW_NUMBER() OVER(ORDER BY SearchID) AS NUMBER, 
                * FROM search_history 
                WHERE UserID=111
                AND Date >= #DateVal#     
           ) AS TBL
   WHERE NUMBER BETWEEN ((#pageNum# - 1) * #MaxRows# + 1) AND (#pageNum# * #MaxRows#)
   ORDER BY #OrderBy# #sort#

Watch out for the SQL Injection in your ORDER BY. What versions of ColdFusion and SQL? — Shawn, Aug 24 '17 at 19:22

score 2 · Accepted Answer · answered Aug 24 '17 at 16:15

First, this is an issue with SQL Server and not ColdFusion. You're just generating the query inline with ColdFusion. It would be better if you create this query as a stored procedure, which by nature would perform better than an inline query.

Second, this answer is the basis for the example I'm posting. SQL Server famously has not supported OFFSET and LIMIT, which makes paging dead simple. These have been in MySQL and postgreSql forever.

Third, as of SQL Server 2012, there is now support for OFFSET and FETCH (essentially LIMIT). Here is a good example of how that works.

This example uses SQL @parameters. Ideally you would declare these in a stored procedure, but you can declare and set them in your inline query. Just make sure to use cfqueryparam to protect against SQL Injection attacks.

SELECT *
FROM (
    SELECT 
        h.column1
        , h.column2
        , u.FirstName
        , u.LastName
        , SELECT ROW_NUMBER() OVER ( ORDER BY @ORDER_BY @SORT_ORDER ) AS RowNum
    FROM 
        dbo.History h
    INNER JOIN 
        dbo.User u ON h.UserID = u.UserID
    WHERE 
        h.UserID = @USER_ID
        AND 
        h.Type IS NOT NULL
        AND 
        h.SubType IS NOT NULL 
) AS HistorySearch
WHERE 
    RowNum >= @ROW_START
    AND
    RowNum < @ROW_END
ORDER BY 
    RowNum

You'll want to calculate the values of ROW_START and ROW_END based on the page number and a page size variable.

Finally, don't use SELECT * in your main select query. It's a bad practice.

score 0 · Answer 2 · answered Aug 24 '17 at 18:55

Depending on how much data you are returning you can do your query normally and do pagination on the output with cold fusion. And if you can avoid using * in the query that is a good idea.

<cfparam name="StartAt" default="1">
<cfparam name="MaxRows" default="100">

<cfoutput name="QueryName" startrow="#StartAt#" maxrows="#maxRows#" >

The variables MaxRows and StartAt would be set in your form, and passed when the user clicks Next or previous.

If you have a very large result set then limiting the data returned would be a better idea as you have put forward.

query result is very large and crashing the application in between. so that only we started thinking about alternative. — Süresh AK, Aug 25 '17 at 06:10

Implementation of pagination in coldfusion

2 Answers2