Support for server side processing (search, pagination, ...)

[DMW007]

I'm querying a table with ~2k rows, and I assume that some of my further users (OS projects) will have even more rows. So I'd like to fetch only the displayed data with ajax. jQuery DataTables has server-side processing for this, where the initial data is loaded with ajax and all user changes on them like searching, pagination, or the sort order. This keep the table performant even on large datasets, cause they're not loaded and sorted in the DOM.

Sadly this seems not possible with Simple-DataTables out of the box. There is an example using Javascripts fetch api but it only loads the initial data from the server using ajax. No further requests were made if the user interacts with the table (searching, pagination).

I tried to realize this myself using the API without applying core modifiations: There is a datatable.search event, but according to the code it's only possible to read the matched search result. With other methods I tried to modificate the data source itself with the filtered data I'm fetching in the search event:

    async function fetchCommunities(searchTerm = '') {
        let resp = await fetch(`@(Url.Action("Index", "Community"))?searchTerm=${searchTerm}`)
        return resp.json()
    }

    document.addEventListener("DOMContentLoaded", async (event) => {
        let data = await fetchCommunities()
        table = new simpleDatatables.DataTable("#communitys-table", {
            data: {
                headings: data.headings,
                data: data.rows
            },
        })
        table.on('datatable.search', async function (query, matched) {
            // Our workaround doesnt work if there are search results on the client-side:
            // Uncaught TypeError: Node.appendChild: Argument 1 is not an object.
            if (matched.length == 0) {
                // table.clear() doesnt seem to work
                let rows = table.rows()
                while (table.data.length > 0)
                    rows.remove()

                let newData = await fetchCommunities(query)
                if (newData.rows.length == 0) {
                    table.wrapper.classList.remove("search-results")
                    table.setMessage(table.options.labels.noRows)
                } else {
                    rows.add(newData)
                }

                // If searching is set to true, we get "no results"
                rows.searchData = []
                table.searching = false
                table.update()
            }
        });
    })

It basically works, but not really stable. For example when the search term isn't found and the term got deleted out of the search box, it still shows no results instead of loading the default unfiltered ones. So for me it seems more work is needed, I'll re-try to go back to jQuery.DataTable, but maybe there is an interest to official extend this?

I have used the latest stable version from npm

"simple-datatables": "^3.0.2"

which got added in my gulpfile like this:

`${node}/simple-datatables/dist/umd/simple-datatables.js`,

[tacman]

What I'd really love to see is support for json-ld (I use API Platform for that), that gives the server-side pagination, searching, sorting, etc. I have some datatables code that uses that, I'll find it and try to apply it to this.

[tacman]

Try this, it's working okay for my app, hope to post a demo soon, but I think this is the right approach, overwriting the search method. It still needs to do other things, like fire the event. I thin

let url = '/api/works.json?page=1&itemsPerPage=10&&title=';

class ApiDataTable extends simpleDatatables.DataTable {
    search(query) {
            let rows = this.rows()
            while (this.data.length > 0)
                rows.remove();

            let data = fetchCommunities(query).then((data) => {
                    this.insert({
                        data: data.map(item => Object.values(item))
                    });
                }
            );
    }
}

async function fetchCommunities(searchTerm = '') {
    let resp = await fetch(url+searchTerm);
    return resp.json()
}

function test(query)
{
    fetchCommunities()
        .then(data => {
            if (!data.length) {
                return
            }
            let tableData =  data.map(item => Object.values(item));
            let table = new ApiDataTable(".table", {
                data: {
                    headings: Object.keys(data[0]),
                    data: tableData
                },
            });
        });
}

[johanneswilm]

Looks good! Do you want some of this included into the main package or maybe add it as a demo? If so, feel free to make a PR.

On Thu, Feb 25, 2021, 19:28 Tac Tacelosky [email protected] wrote:

Try this, it's working okay for my app, hope to post a demo soon, but I think this is the right approach, overwriting the search method. It still needs to do other things, like fire the event. I thin

let url = '/api/works.json?page=1&itemsPerPage=10&&title='; class ApiDataTable extends simpleDatatables.DataTable { search(query) { let rows = this.rows() while (this.data.length > 0) rows.remove();

        let data = fetchCommunities(query).then((data) => {
                this.insert({
                    data: data.map(item => Object.values(item))
                });
            }
        );
}}

async function fetchCommunities(searchTerm = '') { let resp = await fetch(url+searchTerm); return resp.json()} function test(query){ fetchCommunities() .then(data => { if (!data.length) { return } let tableData = data.map(item => Object.values(item)); let table = new ApiDataTable(".table", { data: { headings: Object.keys(data[0]), data: tableData }, }); });}

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/fiduswriter/Simple-DataTables/issues/97#issuecomment-786109787, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAERMOD5KHY7GLCW7QFIKETTA2JFZANCNFSM4X7C55LA .

[tacman]

It'd be great to be in the main package. I'm working on a plugin (extension?) that integrates with API Platform. I'll put that together, then maybe you can integrate it as part of the main package.

Of course, it quickly becomes a bit complicated. The jQuery datatables plugin is very powerful, but I don't believe it takes advantage of the newer Javascript capabilities (ES2018, etc.) and of course has a jQuery dependency. I'd love for the library to be more powerful, especially since Bootstrap 5 is almost out, which for me was the inflection point for trying to drop jQuery altogether.

Anyway, I'll add pagination to this and publish a demo.

On Thu, Feb 25, 2021 at 1:41 PM Johannes Wilm [email protected] wrote:

Looks good! Do you want some of this included into the main package or maybe add it as a demo? If so, feel free to make a PR.

On Thu, Feb 25, 2021, 19:28 Tac Tacelosky [email protected] wrote:

Try this, it's working okay for my app, hope to post a demo soon, but I think this is the right approach, overwriting the search method. It still needs to do other things, like fire the event. I thin

let url = '/api/works.json?page=1&itemsPerPage=10&&title='; class ApiDataTable extends simpleDatatables.DataTable { search(query) { let rows = this.rows() while (this.data.length > 0) rows.remove();

let data = fetchCommunities(query).then((data) => { this.insert({ data: data.map(item => Object.values(item)) }); } ); }} async function fetchCommunities(searchTerm = '') { let resp = await fetch(url+searchTerm); return resp.json()} function test(query){ fetchCommunities() .then(data => { if (!data.length) { return } let tableData = data.map(item => Object.values(item)); let table = new ApiDataTable(".table", { data: { headings: Object.keys(data[0]), data: tableData }, }); });}

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub < https://github.com/fiduswriter/Simple-DataTables/issues/97#issuecomment-786109787 , or unsubscribe < https://github.com/notifications/unsubscribe-auth/AAERMOD5KHY7GLCW7QFIKETTA2JFZANCNFSM4X7C55LA

.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/fiduswriter/Simple-DataTables/issues/97#issuecomment-786117566, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEXIQIXZSXTNZGPOMY33K3TA2KUHANCNFSM4X7C55LA .

[davidsellen]

It'd be great to be in the main package. I'm working on a plugin (extension?) that integrates with API Platform. I'll put that together, then maybe you can integrate it as part of the main package. Of course, it quickly becomes a bit complicated. The jQuery datatables plugin is very powerful, but I don't believe it takes advantage of the newer Javascript capabilities (ES2018, etc.) and of course has a jQuery dependency. I'd love for the library to be more powerful, especially since Bootstrap 5 is almost out, which for me was the inflection point for trying to drop jQuery altogether. Anyway, I'll add pagination to this and publish a demo. On Thu, Feb 25, 2021 at 1:41 PM Johannes Wilm [email protected] wrote: … Looks good! Do you want some of this included into the main package or maybe add it as a demo? If so, feel free to make a PR. On Thu, Feb 25, 2021, 19:28 Tac Tacelosky @.***> wrote: > Try this, it's working okay for my app, hope to post a demo soon, but I > think this is the right approach, overwriting the search method. It still > needs to do other things, like fire the event. I thin > > let url = '/api/works.json?page=1&itemsPerPage=10&&title='; > class ApiDataTable extends simpleDatatables.DataTable { > search(query) { > let rows = this.rows() > while (this.data.length > 0) > rows.remove(); > > let data = fetchCommunities(query).then((data) => { > this.insert({ > data: data.map(item => Object.values(item)) > }); > } > ); > }} > async function fetchCommunities(searchTerm = '') { > let resp = await fetch(url+searchTerm); > return resp.json()} > function test(query){ > fetchCommunities() > .then(data => { > if (!data.length) { > return > } > let tableData = data.map(item => Object.values(item)); > let table = new ApiDataTable(".table", { > data: { > headings: Object.keys(data[0]), > data: tableData > }, > }); > });} > > — > You are receiving this because you are subscribed to this thread. > Reply to this email directly, view it on GitHub > < #97 (comment) >, > or unsubscribe > < https://github.com/notifications/unsubscribe-auth/AAERMOD5KHY7GLCW7QFIKETTA2JFZANCNFSM4X7C55LA > > . > — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#97 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEXIQIXZSXTNZGPOMY33K3TA2KUHANCNFSM4X7C55LA .

Hello @tacman did you get pagination to work?

[shaunroselt]

Would be great if you can do something like dataTable.search("MyText") and then it searches for that string.

[Kapsonfire-DE]

Any progress here? Really need server side processing like query datatables.

[johanneswilm]

@Kapsonfire-DE What exactly do you want to do? And why is the example in https://github.com/fiduswriter/Simple-DataTables/issues/97#issuecomment-786109787 not enough?

Doing requests to the server is really better handled in your own code as far as I can tell.

[Kapsonfire-DE]

I have huge database which needs server processing. Extending the class is working and thats the way I'm gonna do now, but this should be a native function. I started first writing a plugin, but on the events im not able to stop the client side sorting/searching/pagination But there's no return false/event.preventDefault

[johanneswilm]

@Kapsonfire-DE I think that's what one did back in the old days before fetch, bundling, JS classes, jQuery, etc. when it was very difficult to write your own querying mechanism. Then jQuery & co would add such functionality to libraries. Every web app has a different mechanism to authenticate with the server and how to form the query, so now that it is so easy, this is much better done in your own code.

I do think it would be helpful to add a demo though on how to do that - for example with the above code.

[Kapsonfire-DE]

At current state there seems to be no way to prevent client sort/client paginate...

overwriting sortColumn is not get called on client if clicking header

[Kapsonfire-DE]

columns() {
        let tmp = super.columns();
        tmp.__proto__.sort = (t,e) => {
            this.sort(t,e);
        }
        return tmp;
    }

ok hacking into proto does work xD

[johanneswilm]

Hmm, ok, I see, yeah that should be easier to do.

[jgracia]

Hi guys someone managed to get it working with serverside.

[jamols09]

This would be a really awesome implementation. I'm quite worried also if we load a bunch of data lets say it would include images from the cdn then those image would still be loaded even when not viewed. This would really save some performance loss.

[Abusiddiq18]

in simple datatable support server side?

[Rai-burger]

any progress on this one? I'm about to give https://github.com/fiduswriter/Simple-DataTables/issues/97#issuecomment-786109787 a try...

[tacman]

Now that Symfony 6.1 is out and it's much easier to configure bundles, I'll poke around with getting this to work. Then maybe we can import the js to the main branch here.

I've become a huge fan of stimulus, so for my demo I'll likely wrap this in a stimulus controller.

[tacman]

Brainstorming on how to best implement this...

There is currently a data option, which is only used in the constructor. What about adding a "remote" option, which is a callback to load the data. The callback signature should include this sort field/direction (or fields/directions, but that's messier), the records_per_page, and the search option.

Unfortunately, this quickly starts to look like the datatables.net, where each column needs to be defined with sort / search options. The setColumns() method has an ajax argument, but I'm not sure what it does.

It looks like there's already code in the library to support this, with a combination of clearing the data and inserting new rows. The key, I think, is some sort of callback that reloads the data and doesn't update the table with the DOM data.

[tacman]

Alas, my javascript skills aren't up to the task. I've hacked in a few emits, but nothing worth sharing. I can help out with a demo (especially the server side) if someone else adds the code to fire events.

[AdarshHatkar]

It'd be great to be in the main package. I'm working on a plugin (extension?) that integrates with API Platform. I'll put that together, then maybe you can integrate it as part of the main package. Of course, it quickly becomes a bit complicated. The jQuery datatables plugin is very powerful, but I don't believe it takes advantage of the newer Javascript capabilities (ES2018, etc.) and of course has a jQuery dependency. I'd love for the library to be more powerful, especially since Bootstrap 5 is almost out, which for me was the inflection point for trying to drop jQuery altogether. Anyway, I'll add pagination to this and publish a demo. On Thu, Feb 25, 2021 at 1:41 PM Johannes Wilm [email protected] wrote: … Looks good! Do you want some of this included into the main package or maybe add it as a demo? If so, feel free to make a PR. On Thu, Feb 25, 2021, 19:28 Tac Tacelosky @.***> wrote: > Try this, it's working okay for my app, hope to post a demo soon, but I > think this is the right approach, overwriting the search method. It still > needs to do other things, like fire the event. I thin > > let url = '/api/works.json?page=1&itemsPerPage=10&&title='; > class ApiDataTable extends simpleDatatables.DataTable { > search(query) { > let rows = this.rows() > while (this.data.length > 0) > rows.remove(); > > let data = fetchCommunities(query).then((data) => { > this.insert({ > data: data.map(item => Object.values(item)) > }); > } > ); > }} > async function fetchCommunities(searchTerm = '') { > let resp = await fetch(url+searchTerm); > return resp.json()} > function test(query){ > fetchCommunities() > .then(data => { > if (!data.length) { > return > } > let tableData = data.map(item => Object.values(item)); > let table = new ApiDataTable(".table", { > data: { > headings: Object.keys(data[0]), > data: tableData > }, > }); > });} > > — > You are receiving this because you are subscribed to this thread. > Reply to this email directly, view it on GitHub > < #97 (comment) >, > or unsubscribe > < https://github.com/notifications/unsubscribe-auth/AAERMOD5KHY7GLCW7QFIKETTA2JFZANCNFSM4X7C55LA > > . > — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#97 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEXIQIXZSXTNZGPOMY33K3TA2KUHANCNFSM4X7C55LA .

waiting for an update from you 🥰 and I am really need server-side search and pagination

[tacman]

Yeah, I was over-optimistic on what I thought I could do.

In the end, I went back to datatables.net for searching/pagination.

[AdarshHatkar]

😢 means I also have to use datatables.net for searching/pagination

but you tried that amazing 😘🥰

[johanneswilm]

Hey @tacman @AdarshHatkar @Rai-burger @Abusiddiq18 @DMW007 @davidsellen @shaunroselt @jamols09 with version 6.0.0 this should be pretty simple to do now. You can just define your own search algorithm. You can for example make it work by adding your own filter and run datatable.paginate() && datatable.renderTable(), whenever you need to. Let me know if you have further questions.

[tacman]

Thanks! Can you add an example on https://fiduswriter.github.io/simple-datatables/?

[johanneswilm]

@tacman I can put one up if you provide a PR for it.