Using the LIKE and IN operators

ostmal

Hello!
On the server side (PHP), I need to apply " ->where (...)". The SQL query has "LIKE" and "IN" operators.
Unfortunately, I couldn't find out if they can be applied in datatables.

rf1234

Well, did you try it? I would give it a try before asking. But here are two examples from my own coding. Scroll to the right and you'll see a couple of LIKEs. I think these examples show that you can do virtually ANYTHING

->where( function ( $q ) {
    $q  ->where( function ( $r ) { 
        $r  ->where( 'ctr.id',  
            '( SELECT DISTINCT a.ctr_id
                FROM ctr_has_ctr_govdept a
          INNER JOIN ctr_govdept b                   ON a.ctr_govdept_id = b.id    
          INNER JOIN ctr_govdept_has_user_complete c ON b.id = c.ctr_govdept_id 
          INNER JOIN user_has_selected_ctr_govdept d ON b.id = d.ctr_govdept_id    
               WHERE c.user_id = :id 
                 AND d.user_id = :id 
               )', 'IN', false);
        $r  ->bind( ':id', $_SESSION['id'] );
    } );
    if ( isset($_POST['fullTextSearchString']) ) {
        $q  ->where( function ( $r ) { 
            $srchNoSpaces = str_replace( ' ', '', $_POST['fullTextSearchString'] );                
            $srchSpaces = $_POST['fullTextSearchString'];                
            if ($_SESSION['lang'] === 'de') {   
                $r  ->where( 'ctr_search_german.global_search_field', '%'. trim($srchNoSpaces, '"') .'%', 'LIKE' )
                    ->or_where( 'ctr_search_german.global_search_field', '%'. trim($srchSpaces, '"') .'%', 'LIKE' );
            } else {
                $r  ->where( 'ctr_search_english.global_search_field', '%'. trim($srchNoSpaces, '"') .'%', 'LIKE' )
                    ->or_where( 'ctr_search_english.global_search_field', '%'. trim($srchSpaces, '"') .'%', 'LIKE' );
            }
        } );
    }
    if ( isset($_POST['startDateExpired']) ) {
        $q  ->where( function ( $r ) { 
            $r  ->where( 'ctr.expired', 0 )
                ->or_where( function ( $s ) { 
                    $s  ->where( 'ctr.expired', 1 )
                        ->where( 'ctr.end_date', $_POST['startDateExpired'] . ' 00:00:00', '>='); 
                } );
        } );
    }
} ) 

and another one with IN:

->where( function($q) {                    
    $q ->where( 'user_id', $_SESSION['id'] );
    if ( strpos($_SERVER['HTTP_HOST'], 'localhost') === false ) {
       //currently available reports:
       $availableReports = '(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23)';        
       $q ->where( 'number', $availableReports, 'IN', false );
    }
} )

rf1234

Editor also has a where_in method https://editor.datatables.net/docs/1.9.2/php/class-DataTables.Database.Query.html#_where_in

Using it the above example is even simpler:

->where( function($q) {                   
    $q ->where( 'user_id', $_SESSION['id'] );
    if ( strpos($_SERVER['HTTP_HOST'], 'localhost') === false ) {
       //currently available reports:
       $availableReports = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23];       
       $q ->where_in( 'number', $availableReports );
    }
} )

ostmal

Thank you very, very much.