server-side DataTable search API (regular expressions) - Questions

Woon

Hello,
I tried make DataTable using below code. DataTable(server-side=true) is drawed normally (about 740,000 rows, 32 columns).
I want to create the DataTable like this form (https://datatables.net/examples/api/regex.html), and I want use only global search. But search function was not recognized the "|" (or condition).

Then, I made a client-side DataTable with 1000 rows of data. "|" (or condition) was well recognized.

So my question is, is not the example (https://datatables.net/examples/api/regex.html) a good way in serverside way?
Please let me know if anyone have an appropriate method...
Thank you.

{% extends "db/base.html" %}
{% block content %}

<div class="row">
        <div class="container-fluid">
                <div class="jumbotron">
                        <p> MGA version 1.1  </p>
                        <input type="button" value="Export" onClick="window.open('http://10.23.3.44:8080/FindQuery/download_MGAv1')">
                        <p></p>
                        <table>
                                <thead>
                                        <tr>
                                                <th>Target</th>
                                                <th>Search</th>
                                                <th>Treat as regex</th>
                                                <th>Smart search</th>
                                        </tr>
                                </thead>
                                <tbody>
                                        <tr id="filter_global">
                                                <td>Global search</td>
                                                <td><input type="text" class="global_filter" id="global_filter"></td>
                                                <td><input type="checkbox" class="global_filter" id="global_regex"></td>
                                                <td><input type="checkbox" class="global_filter" id="global_smart" checked="checked"></td>
                                        </tr>
                                </tbody>
                        </table>
                        <table id = "MGAv1_table" class="table table-striped table-sm table-bordered" cellspacing="0" width="100%">
                                <thead>
                                        <tr>
                                                <th>Number</th>
                                                <th>ChipName</th>
                                                <th>Chr</th>
                                                <th>Position</th>
                                                <th>rsID</th>
                                                <th>Ref</th>
                                                <th>Alt</th>
                                                <th>Gene</th>
                                                <th>Biotype</th>
                                                <th>Variant</th>
                                                <th>BaseChange</th>
                                                <th>AminoAcidChange</th>
                                                <th>Disease</th>
                                                <th>CLN_PMID</th>
                                                <th>X1KG_AFR</th>
                                                <th>X1KG_AMR</th>
                                                <th>X1KG_EAS</th>
                                                <th>X1KG_EUR</th>
                                                <th>X1KG_SAS</th>
                                                <th>gnomAD_AFR</th>
                                                <th>gnomAD_AMR</th>
                                                <th>gnomAD_EAS</th>
                                                <th>gnomAD_EUR</th>
                                                <th>KRGDB</th>
                                                <th>GT</th>
                                                <th>GT_count</th>
                                                <th>GT_percentage</th>
                                                <th>AL</th>
                                                <th>MA</th>
                                                <th>ALT_AF</th>
                                                <th>NoCall</th>
                                                <th>Diff</th>
                                        </tr>
                                </thead>
                        </table>
                </div>
        </div>
</div>
{% endblock content %}

{% block js %}
<script>
function filterGlobal() {
        $('#MGAv1_table').DataTable().search(
                $('#global_filter').val(),
                $('#global_regex').prop('checked'),
                $('#global_smart').prop('checked')
        ).draw();
}

$(document).ready(function(){
        var table = $('#MGAv1_table').DataTable({

                "serverSide":true,
                "processing":true,
                "scrollX": '500vh',
                "scrollY": '100vh',
                "scrollCollapse":true,
                "ajax": {
                        "url": "/api/VARIANTINFO/",
                        "type": "GET",
                },
                "order": [[1, 'asc']],
                "columns" : [
                        {"data": "id"},
                        {"data": "ChipName"},
                        {"data": "Chr"},
                        {"data": "Position"},
                        {"data": "rsID"},
                        {"data": "Ref"},
                        {"data": "Alt"},
                        {"data": "Gene"},
                        {"data": "Biotype"},
                        {"data": "Variant"},
                        {"data": "BaseChange"},
                        {"data": "AminoAcidChange"},
                        {"data": "Disease"},
                        {"data": "CLN_PMID"},
                        {"data": "X1KG_AFR"},
                        {"data": "X1KG_AMR"},
                        {"data": "X1KG_EAS"},
                        {"data": "X1KG_EUR"},
                        {"data": "X1KG_SAS"},
                        {"data": "gnomAD_AFR"},
                        {"data": "gnomAD_AMR"},
                        {"data": "gnomAD_EAS"},
                        {"data": "gnomAD_EUR"},
                        {"data": "KRGDB"},
                        {"data": "GT"},
                        {"data": "GT_count"},
                        {"data": "GT_percentage"},
                        {"data": "AL"},
                        {"data": "MA"},
                        {"data": "ALT_AF"},
                        {"data": "NoCall"},
                        {"data": "Diff"},
                ]
        });

        $('input.global_filter').on( 'keyup click', function() {
                filterGlobal();
        });
});


</script>
{% endblock js %}

Woon

Sorry, example link was wrong.
Here is example url link.
https://datatables.net/examples/api/regex.html

allan

o my question is, is not the example (https://datatables.net/examples/api/regex.html) a good way in serverside way?

Because "/api/VARIANTINFO/" is responsible for the sorting and filtering in your code above. But presumably it hasn't been configured or written to work with regex search conditions. Whatever the code is that you are using for that API end point, you'd need to update how it generates SQL to allow for a regex search. That is not a feature that we provide in any of the server-side scripts we've published thus far.

Allan

Woon

Thank you for reply, Allan
I found another way, so I resolve this problem.

But I am curious about or condition using "|".
Can I change "|"(or condition) to ","(comma)?

allan

You could if your regex engine supported that, or you translated whatever format you describe for the filtering into regex. A replace of commas to a pipe for example.

Allan

Huraganek

Hello,
Woon could You tell what solution You've found?