Showing posts with label DataTables. Show all posts
Showing posts with label DataTables. Show all posts

Wednesday, 2 August 2017

Some thoughts on pagination

I've written an adaptation of DataTables Pagination for use with MDB before but I've never really had to think about pagination much until, that is, a recent project at work came up. I was laid-up at home with a cold, so I used my time to think about it in some depth. For my use case, I wanted a set of buttons starting with a First and Previous button and ending with Next and Last buttons. I spent some time looking around, and it seemed as though the best arrangement of buttons for individual pages had an odd number.

This, then, was my desired output:

⇤ ← 1 2 3 4 5 → ⇥

With the currently active page disabled for it to be distinguishable (and immune to clicking - I'm not sure why that's as important as it is to me, but it always pleases me if I'm unable to link back to the page I'm on from the page I'm on). I guessed that I'd also need to disable the First and Previous buttons when the first page was active and the Next and Last when the last page was active.

This then meant that the as far as possible the active number should be in the centre of the range of numbers. In the example above then when three was active I'd be happy. However, should four be the active page then the range would start at two and end at six. When one is the active page then it is not in the middle of the range; so we don't need to show numbers which are impossible to display (we really can't get to the page with the number minus one).

If we had a range of seven pages of data to display, this is what we should see while paging through the data:

Active: one

⇤ ← 1 2 3 4 5 → ⇥

Active: two

⇤ ← 1 2 3 4 5 → ⇥

Active: three

⇤ ← 1 2 3 4 5 → ⇥

Active: four

⇤ ← 2 3 4 5 6 → ⇥

Active: five

⇤ ← 3 4 5 6 7 → ⇥

Active: six

⇤ ← 3 4 5 6 7 → ⇥

Active: seven

⇤ ← 3 4 5 6 7 → ⇥

This, then presented me with some considerable confusion, not least because I had trouble thinking clearly - but after far too many hours of pondering I can up with this spreadsheet and started reasoning about the logic behind calculating what I needed to happen.

After playing with the sheet, I clocked the algorithm required needed to check a number of different figures in order to decide where to start and end the range:

=IF(LTE(A3, CEILING(DIVIDE(D2, 2))),
    1,
    IF(GT(ADD(A3, FLOOR(DIVIDE(D2, 2))), A14),
        ADD(MINUS(A14, D2), 1),
        MINUS(A3, FLOOR(DIVIDE(D2,2)))))

Bonkers ehh?

I wrote the first line and clocked what was going on and decided to cheat after that, so I wrote a JSFiddle that generated the correct Google Sheet code for me, which I could then use to create the finished product. The final bit of code is rather simple, and this is it:

const generateCell = (text, active) => {
    const cell = document.createElement("td");
    cell.textContent = text;
    active && cell.classList.add("active");
    return cell;
}
const generateRow = (f, a, b) => {
    const row = document.createElement("tr");
    row.append(generateCell("⇤", a === 1));
    row.append(generateCell("←", a === 1));
    for(let i = f; i < (f + b); i++){
        row.append(generateCell(i, i === a));
    }
    row.append(generateCell("→", a === total));
    row.append(generateCell("⇥", a === total));
    return row;
};
const total = 12;
const table = document.getElementById("body");
const buttons = 5;
for(let active = 1; active <= total; active++){
    let first = (active <= Math.ceil(buttons / 2))
        ? 1
        : (active + Math.floor(buttons / 2) > total)
            ? (total - buttons) + 1
            : active - Math.floor(buttons / 2);
    table.append(generateRow(first, active, buttons));
}

I'm sort of ashamed that it took quite as long as it did, but I’m rather pleased with the result.

Saturday, 22 April 2017

WebSQL bulk insert

I've been playing with WebSQL of late (after all, why wouldn't you play with a deprecated standard?). And I'm loving it for inserting data via Papa Parse, the thing is, it was proving to be really, really slow... right up until I clocked that I was doing it wrong!

The thing is, I was iterating over my data and firing a transaction with an SQL payload for each row of the CSV file I was parsing where I should've been firing a transaction and then iterating over my data and firing off multiple SQL payloads within the single transaction.

This was my first effort:

for (let record of results.data) {
    mydb.transaction(
        t => {
            t.executeSql(`
                INSERT INTO
                  accounts (
                    Date,
                    Company,
                    Account,
                    Amount
                  )
                VALUES (
                  ?,
                  ?,
                  ?,
                  ?
                )`,
                [
                    record.Date,
                    record.Company,
                    record.Account,
                    record.Amount
                ],
                t => {
                    let query = "SELECT COUNT(*) AS total FROM accounts";
                    t.executeSql(
                        query,
                        null,
                        (t, result) => {
                            if (~~result.rows[0].total === tableMetaData.total) {
                                createTable();
                            }
                        },
                        null
                    );
                },
                null
            );
        }
    );
}

Pretty, eh? But it took silly amounts of time to execute and I was planning on inserting 250,000 records. It simply wasn't practical to do it that way as it took something like 20 seconds to insert 1000 rows:

RowsTime
100.218
1001.711
100021.111
500090.703

Changing the code to this:

mydb.transaction(t => {
    for (let record of results.data) {
            t.executeSql(`
                INSERT INTO
                  accounts (
                    Date,
                    Company,
                    Account,
                    Amount
                  )
                VALUES (
                  ?,
                  ?,
                  ?,
                  ?
                )`,
                [
                    record.Date,
                    record.Company,
                    record.Account,
                    record.Amount
                ],
                null,
                null
            );
        }
    },
    error => {
        console.log("error", error);
    },
    () => {
        createTable();
    }
);
Produced these results:
RowsTime
100.081
1000.058
10000.083
50000.223

Not only quicker but I've also halved the number of queries as I don't need to check that all the transactions have completed after each insert, sweet eh? After figuring this out I came across a number of posts where the same thing had been discovered by others. Including RICAL and Gavin Pickin. Just a shame it's deprecated.

Tuesday, 28 February 2017

Roll your own pagination with Bootstrap and MDBootstrap

My previous post about adding MDBootstrap styling to DataTables got me thinking about pagination (that and reading about jQuery Pagination Plugins). As such, I decided to roll my own dynamic pagination. While I got around to it I got to thinking about the bits between the arrows, the filling if you will. I really liked the start/previous mechanism as well as the next/last one. So that within the two ends there's a set of numbers. That's generally fine with a limited set of numbers to page though but what if you've got more than 7, for instance?

I got to thinking about ellipses and how they might do in order to indicate the existence of further numbers past a given set (in my example I'm limiting those to 3). So at the beginning of your paging experience, you can see the first three (with them becoming active as you page through), once you pass a given threshold though you see that there are more behind you than you can see and that you are unable to reach them without going back.

The same mechanism occurs towards the end of your paging experience. I quite like this approach and I've worked up a simple JSFiddle to illustrate the action:

This is the relevant function:

let buildPagination = function(target, start, hits) {
    let pages = Math.ceil(hits / 10);
    let pagesArray = [];
    /*
     * Sort out pages links
     */
    if (pages < 5) {
        for (let i = 0; i < pages; i++) {
            let pageBubble = $("<li></li>", {
                "data-start": i * 10
            });
            (Math.floor((i) * 10) === start) && pageBubble.addClass("active");
            pageBubble.append($("<a></a>", {
                "href": "#",
                "text": i + 1
            }));
            pagesArray.push(pageBubble)
        }
    } else {
        /*
         * Show three pages, and disabled padding if required
         */
        if (start === 0) {
            for (let i = 0; i < 3; i++) {
                let pageBubble = $("<li></li>", {
                    "data-start": i * 10
                });
                (Math.floor((i) * 10) === start) && pageBubble.addClass("active");
                pageBubble.append($("<a></a>", {
                    "href": "#",
                    "text": i + 1
                }));
                pagesArray.push(pageBubble);
            }
            let pageBubble = $("<li></li>", {
                "class": "disabled"
            });
            pageBubble.append($("<a></a>", {
                "href": "#",
                "text": "…"
            }));
            pagesArray.push(pageBubble);
        } else if ((start + 10) >= hits) {
            let pageBubble = $("<li></li>", {
                "class": "disabled"
            });
            pageBubble.append($("<a></a>", {
                "href": "#",
                "text": "…"
            }));
            pagesArray.push(pageBubble);
            for (let i = pages - 3; i < pages; i++) {
                let pageBubble = $("<li></li>", {
                    "data-start": i * 10
                });
                (Math.floor((i) * 10) === start) && pageBubble.addClass("active");
                pageBubble.append($("<a></a>", {
                    "href": "#",
                    "text": i + 1
                }));
                pagesArray.push(pageBubble)
            }
        } else {
            let pageBubble = $("<li></li>", {
                "class": "disabled"
            });
            pageBubble.append($("<a></a>", {
                "href": "#",
                "text": "…"
            }));
            pagesArray.push(pageBubble);
            let current = Math.floor(start / 10);
            for (let i = current - 1; i < current + 2; i++) {
                let pageBubble = $("<li></li>", {
                    "data-start": i * 10
                });
                (i === current) && pageBubble.addClass("active");
                pageBubble.append($("<a></a>", {
                    "href": "#",
                    "text": i + 1
                }));
                pagesArray.push(pageBubble)
            }
            pageBubble = $("<li></li>", {
                "class": "disabled"
            });
            pageBubble.append($("<a></a>", {
                "href": "#",
                "text": "…"
            }));
            pagesArray.push(pageBubble);
        }
    }
    /*
     * Left links (Start and previous)
     */
    let first_page = $("<li></li>", {
        "data-start": 0
    });
    (start === 0) && first_page.addClass("disabled");
    first_page.append($("<a></a>", {
        "href": "#!"
    }).append($("<i></i>", {
        "class": "material-icons",
        "text": "first_page"
    })));
    let chevron_left = $("<li></li>", {
        "data-start": ~~start - 10
    });
    (start === 0) && chevron_left.addClass("disabled");
    chevron_left.append($("<a></a>", {
        "href": "#!"
    }).append($("<i></i>", {
        "class": "material-icons",
        "text": "chevron_left"
    })));
    /*
     * Right link (Next and last)
     */
    let chevron_right = $("<li></li>", {
        "data-start": start + 10
    });
    ((start + 10) >= hits) && chevron_right.addClass("disabled");
    chevron_right.append($("<a></a>", {
        "href": "#!"
    }).append($("<i></i>", {
        "class": "material-icons",
        "text": "chevron_right"
    })));
    let last_page = $("<li></li>", {
        "data-start": Math.floor(hits / 10) * 10
    });
    ((start + 10) >= hits) && last_page.addClass("disabled");
    last_page.append($("<a></a>", {
        "href": "#!"
    }).append($("<i></i>", {
        "class": "material-icons",
        "text": "last_page"
    })));
    /*
     * Add out links
     */
    target.empty();
    target.append(first_page);
    target.append(chevron_left);
    target.append(pagesArray);
    target.append(chevron_right);
    target.append(last_page);
};

This did take me far too long to figure out though as the Maths was complicated (at least it was for me), and I'm still not overly sure that it's correct so if anyone want to take a look please do and tell me where I've gone wrong.

Thursday, 16 February 2017

MDB 3 DataTable Style

It's taken ages and ages but I've finally gotten around to creating a theme for DataTables which uses the lovely inputs from Material Design for Bootstrap (MDB). I've been playing with MDB for a while now and love its look and feel. I've loved DataTables for as long as I can remember so getting them to play nicely with each other was on my radar for a while. I finished that work last night and published it to GitHub this morning and I'm really, quite pleased with the results. This is the Readme:

DataTables comes with few themes, including those for Bootstrap 3 and 4. These themes work really well for Material Design for Bootstrap (MDB) as is, but...

This work enhances the Bootstrap 3 theme and takes advantage of the nice pagination and inputs available in MDB to make it look even better!

Initially only the pagination was implemented but, with some help and advice from Allan I got the filter input as well as the length select to work, using Input Fields and Material Select respectively.

There is presently no support for custom renderers for these elements so the "initComplete" function has been hijacked. Once custom renderers are implemented I'll move the work over to a that mechanism.

Once I have more time I'll also adapt it so that it will work with BootStrap 4 and MDB4.

And this is the code:

/*! DataTables MDBootstrap 3 integration
 * Based upon DataTables Bootstrap 3 integration
 * Dominic Myers <annoyingmouse@gmail.com>
 */

/**
 * DataTables integration for MDBootstrap 3. This requires MDBootstrap 3 and
 * DataTables 1.10 or newer.
 *
 * This file sets the defaults and adds options to DataTables to style its
 * controls using Bootstrap.
 */
(function (factory) {
    if (typeof define === 'function' && define.amd) {
        // AMD
        define(['jquery', 'datatables.net'], function ($) {
            return factory($, window, document);
        });
    } else if (typeof exports === 'object') {
        // CommonJS
        module.exports = function (root, $) {
            if (!root) {
                root = window;
            }
            if (!$ || !$.fn.dataTable) {
                // Require DataTables, which attaches to jQuery, including
                // jQuery if needed and have a $ property so we can access the
                // jQuery object that is used
                $ = require('datatables.net')(root, $).$;
            }
            return factory($, root, root.document);
        };
    } else {
        // Browser
        factory(jQuery, window, document);
    }
}(($, window, document, undefined) => {
    'use strict';
    let DataTable = $.fn.dataTable;
    /* Set the defaults for DataTables initialisation */
    $.extend(true, DataTable.defaults, {
        "dom": `
            <'row'
                <'col-sm-6 input-field'
                    l
                >
                <'col-sm-6 input-field'
                    f
                >
            >
            <'row'
                <'col-sm-12'
                    tr
                >
            >
            <'row'
                <'col-sm-5'
                    i
                >
                <'col-sm-7'
                    p
                >
            >`,
        "renderer": 'mdbootstrap',
        "language": {
            "lengthMenu": "_MENU_",
            "paginate": {
                "first": `
                    <i class="material-icons">
                        first_page
                    </i>`,
                "last": `
                    <i class="material-icons">
                        last_page
                    </i>`,
                "next": `
                    <i class="material-icons">
                        chevron_right
                    </i>`,
                "previous": `
                    <i class="material-icons">
                        chevron_left
                    </i>`
            }
        },
        "initComplete": () => {
            /*
             * This makes the length dropdown into a material select
             */
            let lengthDiv = $(".dataTables_length"),
                lengthSelect = lengthDiv
                    .find("select[name$='_length']"),
                lengthSelectClone = lengthSelect
                    .clone(true);
            lengthDiv
                .replaceWith(lengthSelectClone);
            lengthSelectClone
                .material_select();
            let lengthSelectCloneCaret = lengthSelectClone
                .parent()
                .find(".caret");
            lengthSelectCloneCaret
                .css("display", "block")
                .css("width", "initial")
                .css("border-top", "initial");

            /*
             * This makes the filter input work as a material input
             */
            let filterDiv = $(".dataTables_filter"),
                filterDivParent = filterDiv
                    .parent(".input-field"),
                filterInput = filterDiv
                    .find("input[type='search']"),
                filterInputClone = filterInput
                    .clone(true),
                tableId = filterDiv
                    .closest(".dataTables_wrapper")
                    .attr("id")
                    .split("_")[0],
                filterLabel = filterInput
                    .parent("label")
                    .attr("for", tableId + "_cloned_input")
                    .empty()
                    .text("Search")
                    .clone(true);
            filterInputClone.attr({
                "id": tableId + "_cloned_input",
                "type": "text"
            }).on("blur", () => {
                /*
                 * We need to ensure that the active class is removed from the input if there
                 * isn't a value to make it look pretty.
                 */
                if (!filterInputClone.val().length) {
                    filterLabel.removeClass("active");
                }
            });
            filterDivParent
                .empty()
                .append(`
                    <i class="material-icons prefix">
                        search
                    </i>`)
                .append(filterInputClone)
                .append(filterLabel)
        }
    });
    /* Default class modification */
    $.extend(DataTable.ext.classes, {
        sWrapper: "dataTables_wrapper form-inline dt-bootstrap",
        sProcessing: "dataTables_processing panel panel-default"
    });
    /* MDBootstrap paging button renderer */
    DataTable.ext.renderer.pageButton.mdbootstrap = (settings, host, idx, buttons, page, pages) => {
        let api = new DataTable.Api(settings);
        let classes = settings.oClasses;
        let lang = settings.oLanguage.oPaginate;
        let aria = settings.oLanguage.oAria.paginate || {};
        let counter = 0;
        let getDisplayClass = button => {
            let tempBtns = {
                "tempBtnDisplay": "",
                "tempBtnClass": ""
            };
            let tempBtnDisplays = {
                "ellipsis": () => {
                    tempBtns.btnDisplay = "&#x2026;";
                    tempBtns.btnClass = "disabled";
                },
                "first": () => {
                    tempBtns.btnDisplay = lang.sFirst;
                    tempBtns.btnClass = button + (page > 0 ? '' : ' disabled');
                },
                "previous": () => {
                    tempBtns.btnDisplay = lang.sPrevious;
                    tempBtns.btnClass = button + (page > 0 ? '' : ' disabled');
                },
                "next": () => {
                    tempBtns.btnDisplay = lang.sNext;
                    tempBtns.btnClass = button + (page < pages - 1 ? '' : ' disabled');
                },
                "last": () => {
                    tempBtns.btnDisplay = lang.sLast;
                    tempBtns.btnClass = button + (page < pages - 1 ? '' : ' disabled');
                },
                "default": () => {
                    tempBtns.btnDisplay = button + 1;
                    tempBtns.btnClass = page === button ? 'active' : '';
                }
            };
            (tempBtnDisplays[button] || tempBtnDisplays["default"])();
            return tempBtns;
        };
        let attach = (container, buttons) => {
            let i, ien, node, button;
            let clickHandler = e => {
                e.preventDefault();
                if (!$(e.currentTarget).hasClass('disabled') && api.page() != e.data.action) {
                    api.page(e.data.action).draw('page');
                }
            };
            for (i = 0, ien = buttons.length; i < ien; i++) {
                button = buttons[i];

                if ($.isArray(button)) {
                    attach(container, button);
                }
                else {
                    let btnDisplayClass = getDisplayClass(button);
                    if (btnDisplayClass.btnDisplay) {
                        node = $('<li>', {
                            'class': classes.sPageButton + ' ' + btnDisplayClass.btnClass,
                            'id': idx === 0 && typeof button === 'string' ?
                                settings.sTableId + '_' + button :
                                null
                        }).append($('<a>', {
                                'href': '#',
                                'aria-controls': settings.sTableId,
                                'aria-label': aria[button],
                                'data-dt-idx': counter,
                                'tabindex': settings.iTabIndex
                            }).html(btnDisplayClass.btnDisplay)
                        ).appendTo(container);
                        settings.oApi._fnBindAction(
                            node, {action: button}, clickHandler
                        );
                        counter++;
                    }
                }
            }
        };
        // IE9 throws an 'unknown error' if document.activeElement is used
        // inside an iframe or frame.
        let activeEl;
        try {
            // Because this approach is destroying and recreating the paging
            // elements, focus is lost on the select button which is bad for
            // accessibility. So we want to restore focus once the draw has
            // completed
            activeEl = $(host).find(document.activeElement).data('dt-idx');
        } catch (e) {
        }
        attach(
            $(host).empty().html('<ul class="pagination pag-circle"/>').children('ul'), buttons
        );
        if (activeEl) {
            $(host).find('[data-dt-idx=' + activeEl + ']').focus();
        }
    };
    return DataTable;
}));