Table auto-sort

[FMCUSystemAdmins]

Apologies if this has been submitted already, but I didn't see anything out there. We use quite a bit of tables in our documentation and have a need for them to be alphabetically sorted based on a particular column (in our case, the first column). We would like to request an auto-sort feature for tables that would sort them alphabetically based on a chosen column.

This would ease the management of tables that have quite a bit of rows as well as any future additions to the table.

[ssddanbrown]

Thanks for the suggestion @FMCUSystemAdmins, I can see how this would be useful. Just to confirm, Do you desire this feature primarily as an editor of a table or as a viewer of a table?

[FMCUSystemAdmins]

We’re finding a need for it from the editor standpoint.

[essdeeay]

I've added +1 for this as a feature.

[Mr-Sloth]

Eventually a different table plugin would make sense. Something like a filter, sort, searchable table would be overpowered. But sorting in edit mode is also a great addition.

[C493]

I'd like to +1 this request. I would also like viewers of the page to be able to sort tables by any column by clicking on the column heading.

[gschirinzi]

+1 for this request Mainly for viewers to sort tables on any desired column

[Montg0mery]

Even just having client-side table sorting when viewing a page would be very useful, as a starting point.

[crpb]

For those who want sorting at least in the view.

As you can just copy the sorted Table and overwrite the current Content you can at least do some basic Formating with it without relying on something like 'LibreOffice-Calc'.

Custom HTML Head Entry

<!-- https://datatables.net -->
<script src="/localjs/jquery-3.6.0.min.js"></script>
<link rel="stylesheet" type="text/css" href="/localjs/datatables.min.css"/>
<script type="text/javascript" src="/localjs/datatables.min.js"></script>
<script>
$(document).ready(function() {
  $("[id^=bkmrk-sorted-table], table.sorted").DataTable({
    "language": {
       "url": "/localjs/de_de.json"
    },
    "order": []         
  })
})
</script>

I use this nginx-snippet, so i don't have to rely on external Sources.

location /localjs {
  autoindex on;
  alias /var/www/html/localjs/;
}

The autoindex can be left out naturally.

[robert-andreas]

Could you share the content of that file :-)?

[crpb]

Could be that someone did a few modifications in the office. I'm not all versed in Javascript :-). https://gist.github.com/crpb/705dbebe326573e6a36c12c415207d59

[ssddanbrown]

Here's my no-library take for in-WYSIWYG-editor table sorting. Allows sorting via double clicking column headers. Supports inverse sort on re-sort of same column. Lightly tested on v22.11, Might have side-affects. Just needs to be added to the "Custom HTML Head Content" customization setting:

<script>

    // Hook into the WYSIWYG editor setup event and add our logic once loaded
    window.addEventListener('editor-tinymce::setup', event => {
        const editor = event.detail.editor;
        setupTableSort(editor);
    });

    // Setup the required event handler, listening for double-click on table cells.
    function setupTableSort(editor) {
        editor.on('dblclick', event => {
             const target = event.target;
             const parentHeader = target.closest('table tr:first-child td, table tr:first-child th');
             if (parentHeader) {
                 // Sort out table within a transaction so this can be undone in the editor if required.
                 editor.undoManager.transact(() => {
                     sortTable(parentHeader, editor);
                 });
             }
        });
    }

    // Sort the parent table of the given header cell that was clicked.
    function sortTable(headerCell) {
        const table = headerCell.closest('table');
        // Exit if the table has a header row but the clicked cell was not part of that header
        if (table.querySelector('thead') && headerCell.closest('thead') === null) {
            return;
        }

        const headerRow = headerCell.parentNode;
        const headerIndex = [...headerRow.children].indexOf(headerCell);
        const tbody = table.querySelector('tbody');
        const rowsToSort = [...table.querySelectorAll('tbody tr')].filter(tr => tr !== headerRow);
        const invert = headerCell.dataset.sorted === 'true';

        // Sort the rows, detecting numeric values if possible.
        rowsToSort.sort((a, b) => {
            const aContent = a.children[headerIndex].textContent.toLowerCase();
            const bContent = b.children[headerIndex].textContent.toLowerCase();
            const numericA = Number(aContent);
            const numericB = Number(bContent);

            if (!Number.isNaN(numericA) && !Number.isNaN(numericB)) {
                return invert ? numericA - numericB : numericB - numericA;
            }

            return aContent === bContent ? 0 : (aContent < bContent ? (invert ? 1 : -1) : (invert ? -1 : 1));
        });

        // Re-append the rows in order
        for (const row of rowsToSort) {
            tbody.appendChild(row);
        }

        // Update the sorted status for later possible inversion of sort.
        headerCell.dataset.sorted = invert ? 'false' : 'true';
    }
</script>

[Coros]

Does anyone have the solution from @crpb working? I've obtained the jquery and datatable files from datatables.net. I've got an alias in Apache for a /localjs path. I've added the header script provided to BookStack. The browser console reports: uncaught sortable: el must be an htmlelement

I assume based on the header script I needed to add a table ID of bkmrk-sorted-table but that didn't help either.

[crpb]

@Coros that datatables-hack broke here with the release in may and nobody "cried" enough to make it working again since then :P. I think it was v23.05 - maybe this helps to determine where it got mangled.

I currently implemented https://github.com/BookStackApp/BookStack/issues/1518#issuecomment-1349568398 which worked since then without any issues but of course it is "something different".

[Coros]

@crpb Thanks for the update. I would really like the tables to be sorted by the user/viewer. We're migrating away from Confluence and have some rather large tables that help to have on demand sorting. I implemented the WYSIWIG editor change but it didn't seem to sort properly. It only reversed the order rather than sorting by alpha/num. I'll give it another try.

edit: I had a couple blank rows in the table and I think that caused problems. After removing those, it does sort properly in the editor.

[TineUser]

Here's my no-library take for in-WYSIWYG-editor table sorting. Allows sorting via double clicking column headers. Supports inverse sort on re-sort of same column. Lightly tested on v22.11, Might have side-affects. Just needs to be added to the "Custom HTML Head Content" customization setting...

@ssddanbrown: Not bad, but this allows sorting only for editors. It would be great if viewers can sort the tables, too.

[mlbarrow]

@ssddanbrown I'm so amped by your table sort hack above that I had to make this post. Thanks for this. I'll also take this opportunity to 👍 a feature to do this out of the box. I'd prioritize the feature for those with editor permissions (I'm biased), but also see the value for viewers.

A big 💯 for your work on Bookstack, too!!!

[rnldnkp]

Here's my no-library take for in-WYSIWYG-editor table sorting. Allows sorting via double clicking column headers. Supports inverse sort on re-sort of same column. Lightly tested on v22.11, Might have side-affects. Just needs to be added to the "Custom HTML Head Content" customization setting:

@ssddanbrown Very nice! Still works as of today. Should be available by default. Any change you know how to fix this for the 'viewer' mode, which allows live (stateless) sorting? Currently only works in Editing mode.

[alexschomb]

I had the same request by a customer for manual sorting of tables in the frontend. Thanks to the help of modern AI technology and my humble webdev experience, I was able to develop a working solution that even implements up/down arrows next to the column headers. It will work in both light & dark mode and will make use of the CSS variables that can be defined in the customization settings. You are welcome to use it or even implement it into BookStack.

Just add the following to the "Custom HTML Head Content" in the customization settings:

<style>
    .sort-icon {
        margin-left: 5px;
        font-size: 0.8em;
        opacity: 0.6;
        float: right;
    }
    .sort-icon.active {
        color: var(--color-primary);
        opacity: 1;
    }
</style>
<script>
document.addEventListener("DOMContentLoaded", function() {
    const pageContent = document.querySelector(".page-content");
    if (!pageContent) return;

    const tables = pageContent.querySelectorAll("table");

    // Function to clean up unnecessary <br> tags inside or after <strong> tags
    function removeTrailingBreaks() {
        const strongElements = pageContent.querySelectorAll("strong");
        strongElements.forEach(strong => {
            if (strong.nextSibling && strong.nextSibling.nodeName === "BR") {
                strong.parentNode.removeChild(strong.nextSibling);
            }
            if (strong.lastChild && strong.lastChild.nodeName === "BR") {
                strong.removeChild(strong.lastChild);
            }
        });
    }

    // Remove trailing <br> elements before adding sort icons
    removeTrailingBreaks();

    tables.forEach(table => {
        const headers = table.querySelectorAll("thead tr td");
        headers.forEach((header, index) => {
            // Add initial sorting icon (inactive up arrow)
            const sortIcon = document.createElement("span");
            sortIcon.classList.add("sort-icon");
            sortIcon.innerHTML = "\u25B2"; // Up arrow (inactive by default)
            header.appendChild(sortIcon);

            header.style.cursor = "pointer";
            header.dataset.sortOrder = ""; // No sorting initially

            header.addEventListener("click", () => {
                const currentSortOrder = header.dataset.sortOrder === "asc" ? "desc" : "asc";
                header.dataset.sortOrder = currentSortOrder;

                // Reset the sort icons for all headers
                headers.forEach(h => {
                    const icon = h.querySelector(".sort-icon");
                    if (icon) {
                        icon.classList.remove("active");
                        icon.style.opacity = "0.6";
                        icon.innerHTML = "\u25B2"; // Reset to inactive up arrow
                    }
                    h.dataset.sortOrder = "";
                });

                // Set the active sort icon for the clicked header
                if (currentSortOrder === "asc") {
                    sortIcon.classList.add("active");
                    sortIcon.innerHTML = "\u25B2"; // Up arrow for ascending
                    sortIcon.style.opacity = "1";
                } else {
                    sortIcon.classList.add("active");
                    sortIcon.innerHTML = "\u25BC"; // Down arrow for descending
                    sortIcon.style.opacity = "1";
                }

                header.dataset.sortOrder = currentSortOrder;

                // Sort the table
                sortTable(table, index, currentSortOrder);
            });
        });
    });

    function sortTable(table, columnIndex, sortOrder) {
        const rows = Array.from(table.querySelectorAll("tbody tr"));
        const sortedRows = rows.sort((a, b) => {
            const cellA = a.children[columnIndex]?.innerText.toLowerCase() || "";
            const cellB = b.children[columnIndex]?.innerText.toLowerCase() || "";
            let comparison = 0;

            if (!isNaN(parseFloat(cellA)) && !isNaN(parseFloat(cellB))) {
                comparison = parseFloat(cellA) - parseFloat(cellB);
            } else {
                comparison = cellA.localeCompare(cellB);
            }

            return sortOrder === "asc" ? comparison : -comparison;
        });

        const tbody = table.querySelector("tbody");
        sortedRows.forEach(row => tbody.appendChild(row));
    }
});
</script>

[rnldnkp]

Oh wow, @alexschomb that worked instantly! Many many thanks! Such a feature (both front and backend) should be available by default. Maybe @ssddanbrown could adopt both?

[alexschomb]

@rnldnkp Using both scripts together won't work because they will interfere due to similar function names. I took the liberty to advance my script so that it should be working now in the backend editor as well. There is one little bug that I couldn't solve that causes the sorting state of a column header to be saved as well. Actually, this might even be called a feature, but I think it might be bad practice and should be solved. I did some tests with the TinyMCE event editor.on('SaveContent', function(e) {...}), but somehow the state of the sorting icon was still saved to the database. Maybe there is a different event to listen to. Perhaps @ssddanbrown has a better idea.

By the way, the script also has a new feature to reset sorting when clicking on the same column header for the third time.

<style>
    .sort-icon {
        margin-left: 5px;
        font-size: 0.8em;
        opacity: 0.6;
        float: right;
    }
    .sort-icon.active {
        color: var(--color-primary);
        opacity: 1;
    }
</style>
<script>
document.addEventListener("DOMContentLoaded", function() {
    setupSorting();

    // Hook into the WYSIWYG editor setup event and add our logic once loaded
    window.addEventListener('editor-tinymce::setup', event => {
        const editor = event.detail.editor;

        editor.on('init', () => {
            const iframe = document.getElementById('html-editor_ifr');
            if (iframe) {
                const iframeDoc = iframe.contentDocument || iframe.contentWindow.document;
                const iframeBody = iframeDoc.getElementById('tinymce');
                if (iframeBody) {
                    setupSorting(iframeBody, true, editor);
                }
            }
        });
    });

    function setupSorting(context = document, isBackend = false, editor = null) {
        const tables = context.querySelectorAll("table");

        // Remove trailing <br> elements before adding sort icons
        removeTrailingBreaks(context);

        tables.forEach(table => {
            const headers = table.querySelectorAll("thead tr td");
            headers.forEach((header, index) => {
                if (!header.querySelector(".sort-icon")) {
                    // Add initial sorting icon (inactive up arrow)
                    const sortIcon = document.createElement("span");
                    sortIcon.classList.add("sort-icon");
                    sortIcon.innerHTML = "\u25B2"; // Up arrow (inactive by default)
                    header.appendChild(sortIcon);
                }

                header.style.cursor = "pointer";
                header.dataset.sortOrder = "none"; // Start with no sorting

                header.addEventListener("click", () => {
                    let currentSortOrder = header.dataset.sortOrder;

                    // Transition sort order: none -> asc -> desc -> none
                    switch (currentSortOrder) {
                        case "none":
                            currentSortOrder = "asc";
                            break;
                        case "asc":
                            currentSortOrder = "desc";
                            break;
                        case "desc":
                            currentSortOrder = "none";
                            break;
                    }

                    // Reset sorting for all headers in the table
                    headers.forEach(h => {
                        const icon = h.querySelector(".sort-icon");
                        if (icon) {
                            icon.classList.remove("active");
                            icon.style.opacity = "0.6";
                            icon.innerHTML = "\u25B2"; // Default to inactive up arrow
                        }
                        h.dataset.sortOrder = "none";
                    });

                    // Apply sorting state to the clicked header
                    header.dataset.sortOrder = currentSortOrder;
                    const sortIcon = header.querySelector(".sort-icon");

                    if (currentSortOrder === "asc") {
                        sortIcon.classList.add("active");
                        sortIcon.innerHTML = "\u25B2"; // Active up arrow for ascending
                        sortIcon.style.opacity = "1";
                        sortTable(table, index, "asc");
                    } else if (currentSortOrder === "desc") {
                        sortIcon.classList.add("active");
                        sortIcon.innerHTML = "\u25BC"; // Active down arrow for descending
                        sortIcon.style.opacity = "1";
                        sortTable(table, index, "desc");
                    } else if (currentSortOrder === "none") {
                        resetTableOrder(table);
                    }

                    // If in backend, reflect changes to editor
                    if (isBackend && editor) {
                        editor.undoManager.add();
                    }
                });
            });

            // Store original order of rows for unsorting functionality
            storeOriginalOrder(table);
        });
    }

    // Function to clean up unnecessary <br> tags inside or after <strong> tags
    function removeTrailingBreaks(context) {
        const strongElements = context.querySelectorAll("strong");
        strongElements.forEach(strong => {
            if (strong.nextSibling && strong.nextSibling.nodeName === "BR") {
                strong.parentNode.removeChild(strong.nextSibling);
            }
            if (strong.lastChild && strong.lastChild.nodeName === "BR") {
                strong.removeChild(strong.lastChild);
            }
        });
    }

    // Sort table rows
    function sortTable(table, columnIndex, sortOrder) {
        const rows = Array.from(table.querySelectorAll("tbody tr"));
        const sortedRows = rows.sort((a, b) => {
            const cellA = a.children[columnIndex]?.innerText.toLowerCase() || "";
            const cellB = b.children[columnIndex]?.innerText.toLowerCase() || "";
            let comparison = 0;

            if (!isNaN(parseFloat(cellA)) && !isNaN(parseFloat(cellB))) {
                comparison = parseFloat(cellA) - parseFloat(cellB);
            } else {
                comparison = cellA.localeCompare(cellB);
            }

            return sortOrder === "asc" ? comparison : -comparison;
        });

        const tbody = table.querySelector("tbody");
        sortedRows.forEach(row => tbody.appendChild(row));
    }

    // Reset the table to its original order
    function resetTableOrder(table) {
        const rows = Array.from(table.querySelectorAll("tbody tr"));
        rows.sort((a, b) => parseInt(a.dataset.originalIndex) - parseInt(b.dataset.originalIndex));
        const tbody = table.querySelector("tbody");
        rows.forEach(row => tbody.appendChild(row));
    }

    // Store original order of rows for unsorting functionality
    function storeOriginalOrder(table) {
        const rows = table.querySelectorAll("tbody tr");
        rows.forEach((row, index) => {
            row.dataset.originalIndex = index;
        });
    }
});
</script>

[rnldnkp]

Nice :)

Somehow I can use both by the way. Currently the backend is de first script (click header), frontend is the second (with arrows). One uniform solution for both is better of course!

[TineUser]

The new head content sounds great but doesn't work for me. Does this only work with newer BookStack releases? I'm using BookStack v24.02.3 because I can't upgrade PHP at the moment.

So I reverted back to this head content https://github.com/BookStackApp/BookStack/issues/1518#issuecomment-1349568398 which is working fine but only in the backend editor.

[alexschomb]

The new head content sounds great but doesn't work for me. Does this only work with newer BookStack releases? I'm using BookStack v24.02.3 because I can't upgrade PHP at the moment.

Sorry, I only tested it with the latest Docker version of BookStack. Can't test older versions right now, but possibly the CSS selector is the reason. @ssddanbrown used table tr:first-child td, table tr:first-child th which is less specific than thead tr td (table is implied by earlier lines) of my code. My code will only work with table headers that were defined as such in the TinyMCE Editor. It's likely that older versions of BookStack didn't support table headers and a more general approach (the first row of all tables) is required. Feel free to replace my selector with tr:first-child td, tr:first-child th, it might be working.

[TineUser]

Sorry, I don't understand what to replace exactly. I'm not a developer so I don't have experience in such coding.

[alexschomb]

@TineUser in my code above replace this line of code from

            const headers = table.querySelectorAll("thead tr td");

to this

            const headers = table.querySelectorAll("tr:first-child td, tr:first-child th");

[TineUser]

@alexschomb: Thank you. This works now in editor and viewer mode but it also sorts the column headers.

[TineUser]

@alexschomb: OK, now I understand how your script is working. You have to define a header row in the table for the sorting feature. Then it works as you scripted it.

But there's one strange thing: When configuring the first row as header there will be inserted one more column which is empty but has the sorting icon.

This is in editor and viewer mode.

[alexschomb]

@TineUser thanks for the feedback. Unfortunately, I can't test with other versions at the moment. I can't reproduce the issue with the newest version.