server side pagination without triple query
server side pagination without triple query
apex__
Posts: 2Questions: 1Answers: 0
Hi,
Their is something i don't get it with the server side.
First at all, when the user load the initial page, i have to quey my db just to count the lenght of the maximum results that this customer can have. (recordsTotal is now set)
Secondly, i catch my data with the parameters sent by the datatable. (start, length, search and order by)
I use start and length to set LIMIT and OFFSET of the query.
The problem is to set recordsFiltered. The only way (i see) to get this value is to redo my previous query, without LIMIT and OFFSET and get the count(*).
Is this the good way ? Triple query the database to get recordsTotal, recordsFiltered, and data ?
I think's it can't be possible, i do something wrong. My query is a big query, and i cannot have the privilege to play it 3 times. 2 was already too much but 3 : noway !
Thank's guys to provide help.
This question has an accepted answers - jump to answer
Answers
That's the only way I know of doing it as well. Those three pieces of information are required, and they need to be obtained by using different queries. MySQL does have a shortcut option for getting the unfiltered table length, but that is specific to MySQL only.
If you have any other suggestions for how to get that data from a single query then I'd be interested to hear your ideas.
Allan
First at all, very thank you to answer so fast. You give me the right keyword to find this shortcut.
http://stackoverflow.com/questions/12887266/get-total-number-of-rows-when-using-limit
https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
An other idea, is to grab all the filtered result without LIMIT and do the slice directly onto the array given by the database.
And as always, Thank you Allan, you (and your team?) did a great job with Datatable :)
found_rows is the method in MySQL.
Thank you. Just me and the community :-)
Allan