Pagination without recordsTotal / recordsFiltered

Pagination without recordsTotal / recordsFiltered

diego_reginidiego_regini Posts: 7Questions: 1Answers: 0
edited June 2019 in Free community support

Hi,
Is there a method to ignore the need of recordsTotal and recordsFiltered? (serverSide paging)
I'm asking because i have a complex query and i don't need to know my totals (the data rapresent a system log, so is useless for user to know they have 100000 rows).
count all rows available is so time consuming that user would walk away (2 to 5 minutes).

Any suggestions?

ps: i just need "pagingType": "full", so no page number is required.

Thank you,
Diego

Answers

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    Hi @diego_regini ,

    Not really - if you're using serverSide, the client would expect those values. You could potentially fudge the values with a really high number if you don't expect users to go to the end, and you prevent them by just having 'First', 'Previous' and 'Next' buttons, ignoring "Last".

    Cheers,

    Colin

  • diego_reginidiego_regini Posts: 7Questions: 1Answers: 0

    Hi, I need the Last page. Query with seek method are fast even on enormus table. the problem is counting and OFFSET usage.

    Is there any possibility that in future versions a serverSide mode will be implemented without counting the rows total? or with customizable pagination?

    Diego

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    In future yes, we do plan to support this. But honestly I don't know what that will come. Part of the problem is that DataTables expects an end count so it can do the paging calculations. That would really need to be loosened to just allow "request next" and allowing the server to say "no more data".

    Allan

  • diego_reginidiego_regini Posts: 7Questions: 1Answers: 0

    That would be awseome!!

    I'll keep waiting!

    Diego

  • chaitanya_Alliedchaitanya_Allied Posts: 1Questions: 0Answers: 0

    hi @allan is this feature made available now?

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    No, it's still in the backlog, I'm afraid.

    Colin

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Its not something we are planning on working on in the near future I'm afraid, due to the number of other things we are working on!

    Allan

  • usedlensusedlens Posts: 15Questions: 5Answers: 1

    I started to wonder about this myself, since the queries for counting records are taking too long.

    What would probably work nicely.

    Simple Pagination, Previous & Next.
    The url.length value is 1 over the row count desired, so limit 26 for 25 results.
    If the data record count is higher than the rows, then Next Button is available - else disable.

  • usedlensusedlens Posts: 15Questions: 5Answers: 1
    edited August 2020

    I realised it shouldn't be too hard to apply this logic.

    So this is to alleviate the need for slow count queries with large amounts of data, you can have a single SQL query to return your data with a LIMIT.

    I am using a search feature, so sometimes the records to display are less than 10, blind page numbering will not work, I will just use Prev / Next pagingType: "simple"

    On my serverside
    * I set records and filtered records to 5000000 so that it will always expect more data (hide this on the front end bInfo : false)
    * I added +1 to my url.length for the LIMIT so that query returned one extra row to check for more data.

    The following code will check the length of returned JSON data (e.g. 11 records) versus the page.len() (e.g. 10 records) - in this case pop() will remove the last record.

    It will hide and show the next button.

    dataSrc: function(json) {
            if (json.data) {
                if (resultsTable.page.len() > json.data.length) {
                    hideNext()
                } else {
                    showNext()
                    json.data.pop()
                }
                return json.data;
            } else {
                return [];
            }
        },
    

    I added these as separate functions because it needed a delay, though it works with 0. Maybe I needed to handle the logic in the correct place, but this works for now.

        function hideNext() {
            setTimeout(
                function() {
                    $('#resultsTable_next').hide();
                }, 0);
        }
    
    function showNext() {
        setTimeout(
            function() {
                $('#resultsTable_next').show();
            }, 0);
    }
    

    I had a look at disabling the next button using
    https://datatables.net/extensions/buttons/

    When I added the JS I got a cross site cookie error

    Then I added buttons: true and I got this error pointing at the buttons.min.js file

    Uncaught Unknown button type: print

    @allan any ideas on this last part?

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Uncaught Unknown button type: print

    Sounds like you haven't got the buttons.print.js file included on your page.

    Allan

This discussion has been closed.