Searchbuilder orginal data diffrent type

ECEGROUPE

Is it possible to filter a collumn with searchbuilder, based on original data of the collumn (datetime) and not on the render data (string) ?

Thx for your answer

kthorngren

Maybe you need to use Orthogonal data and just render the data for the display operation. This will leave the the original data for all the other operations including search. If this doesn't help the please post the code used to render that column.

Kevin

ECEGROUPE

Thank for you anwser, here is the code ->

{ data: 'mydata2', editfield, editField:'mydata2-Edit',

        render: function ( data, type, full, meta ) {

                if (full.mydata1 == '0.0') {
                        return 'N/A';
            } else if (full.mydata3 != null) { 
                return mydata3;
            } else {
                return mydata2-Edit;
            }
        return data;
        },

}

What i want ->
i want to filter whith searchbuilder based on mydata2 and not on the render function that render 'N/A', mydata3 or mydata2-Edit depending on the condition, how to do this ? For information mydata2 type is datetime.

kthorngren

Try this:

{ data: 'mydata2', editfield, editField:'mydata2-Edit',
 
        render: function ( data, type, full, meta ) {
            if (type === 'display' ) {  // only render the data for display
                if (full.mydata1 == '0.0') {
                        return 'N/A';
            } else if (full.mydata3 != null) {
                return mydata3;
            } else {
                return mydata2-Edit;
            }
         }
        return data;
        },
 
}

Kevin

ECEGROUPE

Thank you very much, it work perfectly !

ECEGROUPE

Your answer work for what i asked here but it doesn't work for what i want to do so i have a new question but first i explain the situation.

My table is set up like this ->

By default, the column " mydata2 " is populated with date from my database (but not on every row, there is some row where there is no value). You can modify the value when there is no value (you can type a date or text) , the edited value to go to field " mydata2-Edit " :

{ data: 'mydata2', editfield, editField:'mydata2-Edit', searchBuilderTitle: 'MyColumn',

This column also have a render function that change the displayed value based on multiples conditions :
- if the value of the other column " mydata1 " is "0.0" then display "N/A"
- if the value of " Mydata2 " is not null then display it
- else display " mydata2-Edit " (empty field by default or edited value)

render: function ( data, type, full, meta ) {
    if (type === 'display' ) {  // only render the data for display
        if (full.mydata1 == '0.0') {
            return 'N/A';
        } else if (mydata2 != null) {
            return mydata2;
        } else {
            return mydata2-Edit;
        }
}

Now i want to use searchbuilder on that column -> filter based on the original data of the column " mydata2 " (only date or empty data) -> that work with the code above BUT i also want that when the user enter manually a date into this column (that go into " mydata2-Edit ") the seachbuilder filter take it into consideration.
So when i select the filter on " MyColumn " i take in consideration all " mydata2 " data and only date of " mydata2-Edit " data.

I know i can use this code to check if mydata2-Edit value is a date :
moment(full.mydata2-Edit, "DD/MM/YYYY", true).isValid()

How to set up the searchbuilder to take into consideration both data as described above ?

Thx for your help / time

kthorngren

Use Orthogonal data for the filter operation, like this:

        render: function ( data, type, full, meta ) {
            if (type === 'display' ) {  // only render the data for display
                if (full.mydata1 == '0.0') {
                        return 'N/A';
            } else if (full.mydata3 != null) {
                return mydata3;
            } else {
                return mydata2-Edit;
            }
         }
            if (type === 'display' ) { 
              // code to determine what to return for filtering
            }
        return data;
        },

Create the conditionals to return the data you want for the searchBuilder, like in the display if block.

Kevin

ECEGROUPE

you mean filter and not display right ? :

if (type === 'filter' ) {
      // code to determine what to return for filtering
    }

so to test it the code above, have tried this :

 if (type === 'filter' ) {
          if (full.mydata1 == '0.0') {
                return '30/11/2022';
        }
 }

so when i select today date in searchbuilder filter for "mydata2" column, it should show all row where "mydata1" is set to 0.0 but that's not the case, it show "No matching records found..."

Why ?

kthorngren

Its hard to say why the data isn't showing without seeing a test case that has your data. Please post a link to your page or a test case replicating the issue so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

You always need to return something with columns.render. You will need something like this:

if (type === 'filter' ) {
         if (full.mydata1 == '0.0') {
               return '30/11/2022';
       }
       return data;
}

Kevin

ECEGROUPE

Look like it doesn't work when server-side processing is enabled because when i use serverSide: false, it work

I'm working with large database so i need to use server-side processing, is there a way to do it ?

allan

Its a lot more difficult when using server-side processing, since you need to have the filtering done at the server-side. That means that, the SQL engine would also have to do the formatting (if you want to search on the formatted date). If you want to sort on the original data format (ISO8601 presumably) then that might work out of the box depending on what database you are using. What are you using for the server-side processing script?

Allan

ECEGROUPE

Yes my first question wasn't good, searchbuilder work out of the box with original data even with SSP.

As you understand what i need is searchbuilder using render data for filtering (as explain in my November 30 answer) with SSP ---> so i need that the filtering done at the server-side, how to do this ? is there an example somewhere ?

Currenlty my server side processing script (staff.php) look something like that (
I make it simpler for example purposes but in real there is multiple database, left join etc..) :

<?php
include( "../../../lib/DataTables.php" );

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions,
    DataTables\Editor\SearchPaneOptions;

Editor::inst( $db, 'Mydatabase' )
    ->fields(
        Field::inst( 'mydata1' ),
                Field::inst( 'mydata2' )        
            ->validator( Validate::dateFormat( 'd/m/Y' ) )
                        ->getFormatter( Format::dateSqlToFormat( 'd/m/Y' ) )
                        ->setFormatter( Format::dateFormatToSql('d/m/Y' ) ),
        Field::inst( 'mydata2-edit' ),
                        ->setFormatter( Format::ifEmpty( null ) ),
    )

    ->debug(true)
    ->process( $_POST )
    ->json();
?>

Thanks in advance for your time / help !

allan

Hi,

I'm afraid the formatters happen after the query is performed. What I would suggest is that you switch to client-side formatting of the date which would allow the wire format to be ISO8601, which is what the database uses, so the search would operate on that (example with ISO8601 - although no client-side formatting).

Allan

ECEGROUPE

I understand what you say Allan but i think it's not the right answer because i have tested it with text value on other collumn and it didn't work as well (exemple bellow) :

{ data: 'mydatatext', 
    render: function (data, type){
        if (data === 'THIS IS MY TEXT'){
                return " OK OK";
                }
        return data;
        }
}

So with this code, the render text is "OK OK" but the data 'THIS IS MY TEXT'. When i use searchbuilder, if i filter with 'OK OK' no data is displayed but if i filter with 'THIS IS MY TEXT' it work because it's the original data... How to make the filter work with the rendered data only (with SSP of course because without it work out of box...) ?

allan

At the moment that can't be done with our libraries I'm afraid. The renderer is a client-side function. The server-side, which is doing the filtering, would need to implement the same function before it does the filtering (i.e. in the database).

Allan