Regex searchCols problem

tchristfort · December 2022

I upgraded to Data Tables 1.12.1 and now the following search no longer produce any results:

"searchCols": [ null, null, { "search": "^40306", "regex" : true }],

Are there any changes in the syntax for this in 1.12.1?

If I remove the ^ and $ signs in above search pattern the rows are found correctly, so basically it seams as if it does not activate the regex feature.

Thomas

kthorngren · December 2022

Your code works here with 1.12.1:
http://live.datatables.net/susevoke/1/edit

I added characters to the end of Edinburgh for Cedric Kelly and that row doesn't display.

Please update the test case or provide a link to your page or test case that replicates the issue so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

tchristfort · December 2022

Hi Kevin,

I have cut down the file as much as possible and end up with a test page that demonstrate the problem: DataTablesTest.html

My problem seems related to the sever side feature. If I comment out the server side setting and run file on local data the seach works fine, but with server side data not.

You can access the page on this link: https://alfresco.ranchsystems.dk/weight/DataTablesTest.html

File code:

<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="font-awesome-4.7.0/css/font-awesome.min.css">
<link rel="stylesheet" type="text/css" href="jquery-ui-1.12.1/jquery-ui.min.css">

<script type="text/javascript" language="javascript" src="jquery-ui-1.12.1/external/jquery/jquery.js"></script>  
<script type="text/javascript" language="javascript" src="jquery-ui-1.12.1/jquery-ui.js"></script> 
<script type="text/javascript" language="javascript" src="include/js/moment.min.js"></script> 
<title>Analyser</title>

<link rel="stylesheet" type="text/css" href="DataTables/datatables.min.css">
<link rel="stylesheet" type="text/css" href="DataTables/Select-1.4.0/css/select.dataTables.css">
<link rel="stylesheet" type="text/css" href="DataTables/Buttons-2.2.3/css/buttons.dataTables.min.css">
<link rel="stylesheet" type="text/css" href="DataTables/DateTime-1.1.2/css/dataTables.dateTime.min.css">
<script type="text/javascript" language="javascript" src="DataTables/datatables.min.js"></script>  
<script type="text/javascript" language="javascript" src="DataTables/Select-1.4.0/js/dataTables.select.min.js"></script>
<script type="text/javascript" language="javascript" src="DataTables/Buttons-2.2.3/js/dataTables.buttons.min.js"></script>

<script type="text/javascript" language="javascript" src="include/js/custom-fieldtypes.js"></script>  
<script type="text/javascript" language="javascript" src="DataTables/DateTime-1.1.2/js/dataTables.dateTime.min.js"></script>

<script type="text/javascript" language="javascript" src="include/js/vs_funcs.js"></script>
<link rel="stylesheet" type="text/css" href="jquery-ui-1.12.1/jquery-ui.min.css">


<script>
  

$(document).ready(function() {

    var table = $('#analysis')
                .on('xhr.dt', function( e, settings, json, xhr ) {
                    if (json.timeout) {
                        location.reload();
                    }
                })
                .DataTable( {
        dom: "Bfrtip",
               "serverSide": true,
        ajax: { 
                    url: "analysis_server.php",
                    type: "POST"
                },
        columns: [
            { data: "analysis.number" },
            { data: "analysis.weighing_closed" },
            { data: "analysis.customer" },
                        { data: "customer.name" },
            { data: "analysis.item" },
                        { data: "item.name" },
            { data: "analysis.date" },
                        { data: "analysis.moisture" },
                        { data: "analysis.protein" },
                        { data: "analysis.density" }
        ],
        select: {
                    style: 'single',
                    info: false
                },
        "searchCols": [ null,  {
            "search": "40306$",
            "regex": true }]
                } );
} );
</script>
</head>
<body>
<div class="bodycont">

 

<h2>Analyser</h2>

<table  cellspacing="0" class="display" id="analysis" width="100%">
    <thead>
      <tr>
        <th>Nummer</th>
        <th>Vejeseddel</th>
        <th>Kunde</th>
        <th>Kundenavn</th>
        <th>Vare</th>
        <th>Varenavn</th>
        <th>Dato</th>
        <th>Vand</th>
        <th>Protein</th>
        <th>Hektoliter</th>
       </tr>
    </thead>
    <tbody>
      <tr>
        <td>23</td>
        <td>40300</td>
        <td>1000</td>
        <td>No Name</td>
        <td>1100</td>
        <td>Wheat</td>
        <td>27-02-2022</td>
        <td>14</td>
        <td>10</td>
        <td>80</td>
      </tr>
       <tr>
        <td>24</td>
        <td>40306</td>
        <td>1000</td>
        <td>No Name</td>
        <td>1100</td>
        <td>Wheat</td>
        <td>27-02-2022</td>
        <td>14</td>
        <td>10</td>
        <td>80</td>
      </tr>
       <tr>
        <td>25</td>
        <td>40309</td>
        <td>1000</td>
        <td>No Name</td>
        <td>1100</td>
        <td>Wheat</td>
        <td>27-02-2022</td>
        <td>14</td>
        <td>10</td>
        <td>80</td>
      </tr>
     </tbody>
</table>
</div>
 
</body>   
</html>

Edited by Kevin: Syntax highlighting. Details on how to highlight code using markdown can be found in this guide

tchristfort · December 2022

my copy paste of the code did not work. Is there a way to attach the file?

tchristfort · December 2022

Thanks

kthorngren · December 2022

Use Markdown. You will see this note below the Post Comment button:

Posts are formatted using Markdown. To highlight code, please use triple back ticks (```) on new lines before and after the code block.

If you enable server side processing then the searching is to take place in the server script. First question is do you need server side processing enabled? Second is if you need server side processing are you using a Datatables supplied SSP script?

Your server script will need to process the column search parameters as described here. The server script will also need to support using regex search with your query.

Kevin

tchristfort · December 2022

I need server side, and was not aware of a SSP script. This page is part of a larger system with many tables and pages with large tables. Server side is PHP and I pack return json manually, but please guide me to SSP documentation if this can help me.

What I want to achieve in the above code is just a cilent side filtering of data, this worked fine in DataTables 10.1 but apparently not in 12.1. If so I will have to rewrite and to a Server side search instead.

Thanks a lot for your help,

Thomas

kthorngren · December 2022

Are you saying that with 1.10.1 you had server side processing enabled and the search worked?

Server side is PHP and I pack return json manually,

Are you returning all rows or just the rows for the page? How are you handling paging of the data?

The server side processing protocol is documented here.

Kevin

tchristfort · December 2022

I have programmed server side as per 10.1 documentation. My server side php for above page inserted below (split in two due to size).

Yes in 10.1 version the I had server side enabled and the search worked as expected.

<?php

/* 
 * (C) Ranch Systems Europe ApS, 2018
 */

 
// DataTables PHP library
include( "php/DataTables.php" );
include( "include/vejesystem_config.php" );
  
// Alias Editor classes so they are easy to use
require_once ( "include/vs_funcs.php");

session_start();
if (!checkTimeOut()) {
    $reply = array ('timeout' => 'Du har været inaktiv for længe, log ind igen');
    print json_encode($reply);
    exit();
} 

function updateAnalysis($nitID, $editor) {
// Read analysis data from NIT file and insert into database
        global $globalConfig;
    
    $filename = $globalConfig['fossPath'] . $nitID . ".txt";
    if ($fh=@fopen($filename,'r')) {
            $nit_array = fgetcsv($fh,100,';');
            fclose($fh);
               
            $editor->field( 'analysis.date')->setValue( dateNitToDMY($nit_array[0]) );
            $editor->field( 'analysis.time')->setValue( $nit_array[1] );
            $editor->field( 'analysis.moisture')->setValue( $nit_array[3] );
            $editor->field( 'analysis.starch')->setValue( $nit_array[5] );
            $editor->field( 'analysis.gluten')->setValue( $nit_array[6] );
            $editor->field( 'analysis.zeleny')->setValue( $nit_array[7] );
            $editor->field( 'analysis.oil')->setValue( $nit_array[8] );
            $editor->field( 'analysis.crop_template')->setValue( $nit_array[2] );
            $editor->field( 'analysis.density')->setValue( $nit_array[9] );
            $editor->field( 'analysis.protein')->setValue( $nit_array[4] );
        return true;
    } else return false;
}

use DataTables\Editor;
use DataTables\Editor\Field;
use DataTables\Editor\Format;
use DataTables\Editor\Options;
use DataTables\Editor\Validate;
use DataTables\Editor\ValidateOptions;

tchristfort · December 2022

```php
Editor::inst( $db, 'analysis' , 'number')
->fields(
Field::inst( 'analysis.number' )
->set( false ),
Field::inst( 'analysis.weighing_closed' ),
Field::inst( 'analysis.customer')
->validator( Validate::dbValues( ValidateOptions::inst()
->message( 'Kunden findes ikke' ),
'number',
'customer'
)),
Field::inst( 'analysis.item' )
->options( Options::inst()
->table( 'item' )
->value( 'number' )
->label( 'name' )
)
->validator( 'Validate::dbValues', array('message' => 'Varen findes ikke') )
->setFormatter( function($val, $data, $field) { if ($val==='') { return null; } else { return $val; };}),
Field::inst( 'analysis.date' )
->validator( Validate::dateFormat( 'd-m-Y' , ValidateOptions::inst()
->message( 'Ugyldig dato')))
->getFormatter( Format::dateSqlToFormat( 'd-m-Y' ) )
->setFormatter( Format::dateFormatToSql('d-m-Y' ) ),
Field::inst('analysis.time'),
Field::inst( 'analysis.moisture' )
->setFormatter( function($val, $data, $field) { if($val === '') { return null; } else { return str_replace ( ',' , '.' , $val );}})
->getFormatter( function ( $val, $data) { if($val === null) { return ''; } else { return str_replace ( '.' , ',' , $val ); } })
->validator( Validate::numeric($decimal = ',', ValidateOptions::inst()
->message( 'Ugyldig værdi'))),
Field::inst( 'analysis.density' )
->setFormatter( function($val, $data, $field) { if($val === '') { return null; } else { return str_replace ( ',' , '.' , $val );}})
->getFormatter( function ( $val, $data) { if($val === null) { return ''; } else { return str_replace ( '.' , ',' , $val ); } })
->validator( Validate::numeric($decimal = ',', ValidateOptions::inst()
->message( 'Ugyldig værdi'))),
Field::inst( 'analysis.protein' )
->setFormatter( function($val, $data, $field) { if($val === '') { return null; } else { return str_replace ( ',' , '.' , $val );}})
->getFormatter( function ( $val, $data) { if($val === null) { return ''; } else { return str_replace ( '.' , ',' , $val ); } })
->validator( Validate::numeric($decimal = ',', ValidateOptions::inst()
->message( 'Ugyldig værdi'))),
Field::inst( 'analysis.starch' )
->setFormatter( function($val, $data, $field) { if($val === '') { return null; } else { return str_replace ( ',' , '.' , $val );}})
->getFormatter( function ( $val, $data) { if($val === null) { return ''; } else { return str_replace ( '.' , ',' , $val ); } })
->validator( Validate::numeric($decimal = ',', ValidateOptions::inst()
->message( 'Ugyldig værdi'))),
Field::inst( 'analysis.gluten' )
->setFormatter( function($val, $data, $field) { if($val === '') { return null; } else { return str_replace ( ',' , '.' , $val );}})
->getFormatter( function ( $val, $data) { if($val === null) { return ''; } else { return str_replace ( '.' , ',' , $val ); } })
->validator( Validate::numeric($decimal = ',', ValidateOptions::inst()
->message( 'Ugyldig værdi'))),
Field::inst( 'analysis.zeleny' )
->setFormatter( function($val, $data, $field) { if($val === '') { return null; } else { return str_replace ( ',' , '.' , $val );}})
->getFormatter( function ( $val, $data) { if($val === null) { return ''; } else { return str_replace ( '.' , ',' , $val ); } })
->validator( Validate::numeric($decimal = ',', ValidateOptions::inst()
->message( 'Ugyldig værdi'))),
Field::inst( 'analysis.oil' )
->setFormatter( function($val, $data, $field) { if($val === '') { return null; } else { return str_replace ( ',' , '.' , $val );}})
->getFormatter( function ( $val, $data) { if($val === null) { return ''; } else { return str_replace ( '.' , ',' , $val ); } })
->validator( Validate::numeric($decimal = ',', ValidateOptions::inst()
->message( 'Ugyldig værdi'))),
Field::inst('analysis.crop_template'),
Field::inst( 'analysis.sieve1' )
->setFormatter( function($val, $data, $field) { if($val === '') { return null; } else { return str_replace ( ',' , '.' , $val );}})
->getFormatter( function ( $val, $data) { if($val === null) { return 0; } else { return str_replace ( '.' , ',' , $val ); } })
->validator( Validate::numeric($decimal = ',', ValidateOptions::inst()
->message( 'Ugyldig værdi'))),
Field::inst( 'analysis.sieve2' )
->setFormatter( function($val, $data, $field) { if($val === '') { return null; } else { return str_replace ( ',' , '.' , $val );}})
->getFormatter( function ( $val, $data) { if($val === null) { return 0; } else { return str_replace ( '.' , ',' , $val ); } })
->validator( Validate::numeric($decimal = ',', ValidateOptions::inst()
->message( 'Ugyldig værdi'))),
Field::inst( 'analysis.sieve3' )
->setFormatter( function($val, $data, $field) { if($val === '') { return null; } else { return str_replace ( ',' , '.' , $val );}})
->getFormatter( function ( $val, $data) { if($val === null) { return 0; } else { return str_replace ( '.' , ',' , $val ); } })
->validator( Validate::numeric($decimal = ',', ValidateOptions::inst()
->message( 'Ugyldig værdi'))),
Field::inst( 'analysis.sieve4' )
->setFormatter( function($val, $data, $field) { if($val === '') { return null; } else { return str_replace ( ',' , '.' , $val );}})
->getFormatter( function ( $val, $data) { if($val === null) { return 0; } else { return str_replace ( '.' , ',' , $val ); } })
->validator( Validate::numeric($decimal = ',', ValidateOptions::inst()
->message( 'Ugyldig værdi')))
->validator( function ($val, $data, $field, $host ) {
if ((floatvalue($data['analysis']['sieve1'])+floatvalue($data['analysis']['sieve2'])+floatvalue($data['analysis']['sieve3'])+floatvalue($val))>100.01) {
return 'Summen af de 4 sold må ikke være over 100';
} else {
return true;
}
}),
Field::inst( 'analysis.fn' )
->setFormatter( function($val, $data, $field) { if($val === '') { return null; } else { return str_replace ( ',' , '.' , $val );}})
->getFormatter( function ( $val, $data) { if($val === null) { return ''; } else { return str_replace ( '.' , ',' , $val ); } })
->validator( Validate::numeric($decimal = ',', ValidateOptions::inst()
->message( 'Ugyldig værdi'))),
Field::inst( 'analysis.comment' )
->getFormatter( function ( $val, $data) { if($val === null) { return ''; } else { return $val; } }),
Field::inst( 'analysis.parti' ),
Field::inst( 'analysis.quantity' )
->setFormatter( function($val, $data, $field) { if($val === '') { return null; } else { return $val; } })
->getFormatter( function( $val, $data) { if($val === null) { return ''; } else { return $val; } })
->validator( Validate::numeric()),
Field::inst( 'analysis.ccc' ),
Field::inst( 'analysis.glyphosfat' ),
Field::inst( 'analysis.impurity' )
->setFormatter( function($val, $data, $field) { if($val === '') { return null; } else { return str_replace ( ',' , '.' , $val );}})
->getFormatter( function ( $val, $data) { if($val === null) { return ''; } else { return str_replace ( '.' , ',' , $val ); } })
->validator( Validate::numeric($decimal = ',', ValidateOptions::inst()
->message( 'Ugyldig værdi'))),
Field::inst( 'analysis.germination' )
->setFormatter( function($val, $data, $field) { if($val === '') { return null; } else { return str_replace ( ',' , '.' , $val );}})
->getFormatter( function ( $val, $data) { if($val === null) { return ''; } else { return str_replace ( '.' , ',' , $val ); } })
->validator( Validate::numeric($decimal = ',', ValidateOptions::inst()
->message( 'Ugyldig værdi'))),
Field::inst('analysis.exported'),
Field::inst( 'customer.name'),
Field::inst( 'item.name')
)
->leftJoin('customer', 'customer.number', '=', 'analysis.customer')
->leftJoin('item', 'item.number', '=', 'analysis.item')
->on('preEdit', function ( $editor, $id, $data){
if (isset($_POST['nitID'])) {
if (!updateAnalysis($_POST['nitID'], $editor)) {
$reply = array ('error' => 'Kan ikke indlæse analysedata');
print json_encode($reply);
exit();
}
}
})
->on('preEdit', function ( $editor, $id, $values){
$editor
->field('analysis.exported')
->setValue(0);
})
->process( $_POST )
->json();

<?php > ``` ?>

allan · December 2022

Hi @tchristfort,

Server-side processing in Editor's libraries don't support regular expressions for search I'm afraid. It never has, so I'm surprised that it used to work for you. Perhaps you had a modified version of the libraries that added that?

As you note, the search used for Editor's server-side processing is wildcard for prefix and postfix. This is the specific line that does that. If you wanted to, you could just remove the initial % there and it would give the effect you are looking for.

Allan

tchristfort · December 2022

Thanks for clarifying. I will rewrite for server side filtering

Regex searchCols problem

Regex searchCols problem

Answers

Howdy, Stranger!

Categories

DataTables

Regex searchCols problem

Regex searchCols problem

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables