Hi Richard,
You could try to use the table options (advanced) column to set:
(1) page length option to 5
(2) dom option to remove the pagination area
Like this:
{"pageLength":5,"dom":"t"}
This does not limit the number of rows returned by the query, it just removes the pagination buttons after the showing the first five rows. In effect it gives you same result I think/hope… 🙂
You might want to check out the dom option for more info:
https://datatables.net/reference/option/dom
In the example above I remove all dom elements, maybe you want to keep the search box, or some other elements?
Hope this helps Richard! Let me know… 😉
Best regards,
Peter
Thanks Peter for your sharing an alternative approach. Call me a traditionalist but I don’t like the idea of large volumes of data being moved across the network unnecessarily only to be filtered locally by the client. I take it there is no way to append the LIMIT command to your GUI in which case I shall create views that restrict the number of rows returned from the database.
Thanks again for your plug-in
Richard.
Hi Richard,
The approach I mentioned does not move large volumes of data across the network. In fact it moves exactly 5 rows. If you leave pagination on (which is the default), the plugin adds a limit to your query based on the value pageLength (5 in this example).
I’m a traditionalist as well! 🙂
Best regards,
Peter
PS I see now that I wrote “this does not limit the number of rows returned by the query”… Sorry, that was not correct! 🙈
Thanks Peter. As per your suggestion I have added the following jquery conditions via the advanced table options.
{
“paging”: true,
“pageLength”: 5,
“dom”: “t”
}
If I change the “pageLength” value to 3 then 3 lines are returned – which is as required. However, if I don’t clear the Browser cache (Chrome) then it takes a few hours for the new value to take effect. When I clear the Browser cache manually then the new value is immediately active. Not sure what’s going on here and would like to understand better!
Also I still don’t understand how JQUERY via APEX work together to limit the number of rows obtained from a MYSQL query because APEX, which populates an HTML table, gets its data from the JSON array which in turn is populated by running a PHP / MySQL SQL script where the LIMIT condition would be applied. I dare say my understanding of the architecture is a bit suspect!
Thanks.
Richard.
Hi Richard,
Let’s first look at the caching issue. The plugin caches settings by default. This helps to build the table faster. You can turn this off by adding an option to your advanced table settings:
"stateSave":false
I hope this answers your question. I would not recommend to set the stateSave to false on your website, unless you want to change the pageLength frequently or you have other good reasons.
The answer to your LIMIT question might be simpler than you expect… By default pagination is turned on. The browser sends the page number and the page length to the server to request the records for a specific page. The server answers with the records for that page by adding a limit + offset to the SQL query. In SQL something like this is added to the query:
limit pageLength offset ( pageNumber – 1) * pageLength
To turn of this behaviour you can set the “Allow Paging?” column in the Data Publisher to “Yes”. This will load all records in the browser at once. Not a good idea for large tables! For small tables this might be faster if the user wants to perform local searches, sorting, and so on.
Hope this helps…
From traditionalist to traditionalist! 🙂
Best regards,
Peter
What a Pro! Thanks for your patience and knowledge.
All solved.
Richard.
Thanks for your compliment! 🙂
