Server Side Pagination sql server and datatables
Server Side Pagination sql server and datatables
maniya
Posts: 78Questions: 13Answers: 0
Hi, I have the datatables Successfully Implemented with my Coldfusion code. But there is a Problem, I am unable to use the sql server fetch offset with it,
right now my query returns around 5000000 records without using offset which is usually taking a lot off time , i am trying to show 10 at a time so query should not time out and it should on click of next page start with the offset and show 10 rows only
This is my code
Jquery Code
$(document).ready(function() {
$('#ButtonSubmit').on('click', function (e) {
e.preventDefault();
$.ajax({
url: "getheaders.cfm",
cache: false,
data : $('#form').serialize(),
method: "post",
success: function(response){
$('.mytable').html(response);
}
}).done(function(data) {
$('.table').show();
$(".table").DataTable({
"bFilter": true,
"serverSide": true,
"columns": [
{ "data": "name", "title": "Name", "autoWidth": true },
{ "data": "fname", "title":"First Name", "autoWidth": true }
],
"ajax": {
"url" : "datatables.cfm",
"type" : 'post'
},
"language": {
"processing": "Loading..."
}
});
})
});
});
now the coldfusion code
<cffunction name="records" access="remote" returnformat="json" returntype="any" output="false">
<cfparam name="draw" default="1" type="integer" />
<cfparam name="start" default="0" type="integer" />
<cfparam name="length" default="10" type="integer" />
<cfparam name="search" default="" type="string" />
<cfset var aData = arrayNew(1) />
<cfset var sGridData = StructNew() />
<cfif len(form["search[value]"]) gt 0>
<cfset search=form["search[value]"]>
</cfif>
<cfset var rsQuery = ''>
<cfset var listColumns = 'name,firstname'>
<cfquery name="rsQueryData" datasource="#Application.dsLocal#">
;WITH cte AS(
SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY DateCreated DESC) AS rn,
name,firstname
FROM table1
WHERE 1=1
<cfif len(trim(search))>
AND
(
<cfloop list="#listColumns#" index="thisColumn">
<cfif thisColumn neq listFirst(listColumns)>
OR
</cfif>
#thisColumn# LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#trim(search)#%" />
</cfloop>
)
</cfif>
order by 2 desc
OFFSET val(start+1) ROWS FETCH NEXT #length# ROWS ONLY;
UNION
SELECT ROW_NUMBER() OVER(PARTITION BY id ORDER BY datecreated desc ) AS rn,
name,firstname
FROM table2
WHERE 1=1
<cfif len(trim(search))>
AND
(
<cfloop list="#listColumns#" index="thisColumn">
<cfif thisColumn neq listFirst(listColumns)>
OR
</cfif>
#thisColumn# LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#trim(search)#%" />
</cfloop>
)
</cfif>
order by 2 desc
OFFSET val(start+1) ROWS FETCH NEXT #length# ROWS ONLY;
)
SELECT *
FROM cte WHERE rn = 1
</cfquery>
<!--- Loop our results, implementing our filtering and pagination and putting into an array to be returned --->
<cfoutput query="rsQueryData" startrow="#val(start+1)#" maxrows="#val(length)#">
<cfset sGridData = structNew() />
<cfset sGridData['name'] = rsQueryData['name'][currentRow] />
<cfset sGridData['firstname'] = rsQueryData['FirstName'][currentRow]/>
<cfset ArrayAppend(aData, sGridData) />
</cfoutput>
<cfset sGridReturn["draw"] = val(draw) />
<cfset sGridReturn["recordsTotal"] = 10000 />
<cfset sGridReturn["recordsFiltered"] = rsQueryData.RecordCount />
<cfset sGridReturn["data"] = aData />
<cfreturn sGridReturn />
</cffunction>
as i am using the order by clause and the offset it gives me just 17 records while i have so many records, what i am missing here
i want the query should give calculate all the records and give me the results paginated way like 10 at a time so my query should not timeout and smoothe it should go
because after this next task is to get full data in excel
This question has an accepted answers - jump to answer
Answers
I can't fix your code, but I may be able to help.
You need to return 4 values when using server side loading and pagination.
drawis the draw value data tables sends with it's request.recordsTotalis an int representing the total amount of records. So in your case this should be around 5000000recordsFilteredis an int representing the number of records you are sending back. In this case 10.datais simply an array of the objects you are sending back.Hopefully this helps.
Yep, as @dalenw suggested, it sounds like your server-side script isn't returning what's expected. The protocol is discussed here. Also see examples here.
Cheers,
Colin
@dalenw @coliin did yow saw my CF Code, i am returning theses 4 variables
That would require debugging your server script. Have you debugged it to see what SQL statement is built?
Kevin
@kthorngren i mnaged to fix this by using a cte and then passing the pagination in cte
like this
in mycfc i will made this change
it seems the pagination started working but when i click the last number, i get this
https://prnt.sc/1rmrjln
I would start by making sure your calculations for
recordsTotalandrecordsFilteredare correct. That is what the client side Datatables uses to calculate the paging buttons. Otherwise you will need to debug your server script to find out why its not returning any rows for the last page.Kevin
is it because i m using a CTE and count in the same query, because for count in a separate query, i do not know if i have to call the CTE again or whatever i should be doing here
Very likely yes - but for SQL specific help, you'd be better asking on StackOverflow. You typically need three queries for server-side processing:
Allan
@allan Qustion here
I have very big dataset so i can change the ways it loads data, i can scrap the offset and fetch, i can use cfoutput startrow an maxrows to do pagination, my only concern is query takes lots of time to return data but in the front end, i want to actually start seeing records if in the backend it keeps working, not concerned about filtering/search
is this doable, i am trying to understand can i achieve same results without using fetch
Does it take a long time to run the query even if you limit it to say 10 rows? That should be how server-side processing operates - it just sends back the rows needed for the current pagination.
Allan
that is what i am trying to understand, so i use pagination by cfoutput, the query has 70000 records, so it loads that many records in memory and then pagination by cfoutput, is that how server side works because i tried implementing pagination on sql query but that produces wrong results
Sounds like the SQL query is wrong then. You most certainly don't want to load 70k rows into memory just to throw away all but 10 of them. That would negate much of the advantages that server-side processing will afford you.
I'd suggest debugging the SQL statement if it is giving the wrong results.
Allan