search field Editor datatable questions

search field Editor datatable questions

eyal_hbeyal_hb Posts: 98Questions: 31Answers: 0

first question: how search field work on server-Side when get the data from editor database function? did the search field search on all columns?
second question: if i have table that get data from other function that return json how i can make the search work as with the editor database return json function?
third question: when using server-side with editor datatbase function, i get this message when try to search input:

DataTables warning: table id=example - Unknown field: ServiceType (index 5)

if i make server-side= false the search input works fine

thanks

Answers

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited January 2020

    "how search field work on server-Side when get the data from editor database function? did the search field search on all columns?"
    The search is performed on all columns based on the values returned from the database BEFORE any getFormatting. MySQL would save a date usually as
    2020-01-01 00:00:00. If you are searching for this date with server side turned on you will only find it if you enter exactly this or you transform your search string before sending it to the server or you use a view that returns the date in the format your users see at the front end.

    "second question: if i have table that get data from other function that return json how i can make the search work as with the editor database return json function?"
    Just pass the search string to the server in the ajax-call that you use to get the data and use "LIKE" in your SQL statement to filter.

    d.search.value has the content of the search field like in here. The function you see right here can be used to manipulate the search string entered by the user so that it matches with the database format.

    var rateTable = $('#tblRate').DataTable( {
        dom: "Bfrltip",
        processing: true,
        serverSide: true,    
        ajax: {
            url: 'actions.php?action=tblRate',
            type: 'POST',
            data: function ( d ) {            
                d.fullTextSearchString = d.search.value;
            }
        },
        language: {
            "searchPlaceholder": lang === 'de' ? 
                        "z.B. usd libor overnight 17.06.2019" :
                        "e.g. usd libor overnight 17/06/2019",
            "search": lang === 'de' ? 
                        "Suche (Geduld bitte: 100k+ Datensätze!):" :
                        "Search (Patience please: 100k+ records!):"
        }, ...........
    

    Here is an example for a proprietary search using the Data Tables search field but NOT data tables smart search but my own search which is based on two views containing the formatted values that the user sees at the front-end in the language the user selected.

    if ($lang === 'de') {     
        $searchTable = "rate_search_german";
    } else {
        $searchTable = "rate_search_english";
    }
    Editor::inst( $db, 'rate' )
        ->field(
            Field::inst( 'rate.ref_rate' ),
            ................
            Field::inst( 'rate.update_time' )
        )
        ->leftJoin( $searchTable,  'rate.id', '=', $searchTable . '.rate_id')
        ->where( function ( $q ) {
            if ( isset($_POST['fullTextSearchString']) ) {
                if ( $_POST['fullTextSearchString'] > '' ) {
                    if ($_SESSION['lang'] === 'de') {   
                        $q  ->where( 'rate_search_german.global_search_field', '%'. trim($_POST['fullTextSearchString'], '"') .'%', 'LIKE' );
                    } else {
                        $q  ->where( 'rate_search_english.global_search_field', '%'. trim($_POST['fullTextSearchString'], '"') .'%', 'LIKE' );
                    }
                }
            }
        } )              
        ->process($_POST)
        ->json();
    

    "third question: when using server-side with editor datatbase function, i get this message when try to search input:"
    You seem to have a mismatch between your HTML and your Javascript columns. I would also check the server code whether the field in question is being retrieved.

This discussion has been closed.