Search does not work when using multiple where clauses and serverSide true

coderx

Hi Allan,

when I use multiple where when building Editor instance:

...
->where( function ($q) use ( &$key, $value, $op, $key2, $value2, $op2 ) {
            $q->or_where( $key, $value, $op );
            $q->or_where( $key2, $value2, $op2 );
        } )
->process( $_POST )
->json();

the general search does not work.

Maybe I need to put something like this into the anonymous function:

...
->where( function ($q) use ( ... ) {
            ...
            $q->other_parameters( $other_parameters );
        } )
...

---

When I use simple where clause

...
->where( $key, $value, $op )
->process( $_POST )
->json();

the general search does work without problems.

allan

Hi,

Do you get any errors reported? Can you show me the full code you are using?

Thanks,
Allan

coderx

Hi,

unfortunately I could not catch any errors.

I simplified the code a bit, because I believe the important part is the multiple where clause (search does not work for full code nor for simplified code).

When changed to simple where clause (any of commented two), the search works as expected.

require_once APPPATH . 'third_party/Editor-PHP-1.4.0/DataTables.php';

// Alias Editor classes so they are easy to use
use DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Validate;


$CI = & get_instance();
if ( $CI->user_access_rights['stocks'] >= 4 )
{
    $key = 'ims_stocks_deliveries.id';
    $value = '0';
    $op = '>';
    $key2 = 'ims_stocks_deliveries.id';
    $value2 = '0';
    $op2 = '>';
}
else
{
    $key = 'ims_stocks_deliveries.station';
    $value = $CI->user_category;
    $op = '=';
    $key2 = 'ims_stocks_deliveries.to_station';
    $value2 = $CI->user_category;
    $op2 = '=';
}

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'ims_stocks_deliveries' )
    ->fields(
        Field::inst( 'ims_stocks_deliveries.part_number as part_number' )->validator( 'Validate::required' ),
        Field::inst( 'ims_stocks_deliveries.description as description' )->validator( 'Validate::required' ),
        Field::inst( 'ims_stocks_deliveries.station as station' ),
        Field::inst( 'ims_stocks_deliveries.to_station as to_station' )->validator( 'Validate::required' )
    )
    //->where( $key, $value, $op ) // search would work
    //->where( $key2, $value2, $op2 ) // search would work
        
    ->where( function ($q) use ( &$key, $value, $op, $key2, $value2, $op2 ) {
            $q->or_where( $key, $value, $op );
            $q->or_where( $key2, $value2, $op2 );
        } )
    ->process( $_POST )
    ->json();

I left also JS script bare, but it has no effect on search (the search did not work with full code and it does not with simplified one):

<script>

    $(document).ready(function() {
        
        var table = $('#stocks').DataTable( {
            dom: 'TClftip',

            ajax: {
                url: "/stocks/editor_deliveries",
                type: "POST"
            },

            serverSide: true,
            iDisplayLength: 25,
            lengthMenu: [[25, 50, 100, 200, 300, 500, 10000], [25, 50, 100, 200, 300, 500, "All"]],
            oLanguage: { sSearch: "" },
            columns: [
                    { data: "part_number" },
                    { data: "description" },
                    { data: "station" },
                    { data: "to_station" },
                    {
                        data: "action_column",
                        orderable: false,
                        searchable: false,
                        className: "center",
                        defaultContent: "N/A"
                       
                    },
            ],
            order: [ 0, 'desc' ],

            tableTools: {
                sRowSelect: "os",
                aButtons: [
                    
                ]
            },
        } );
    } );

</script>

Nothing really interesting in HTML:

<table id="stocks" class="display table table-condensed table-bordered" cellspacing="0" width="100%">
    <thead>
        <tr>
            <th>P/N</th>
            <th>Description</th>
            <th>From</th>
            <th>To</th>
            <th>Actions</th>
        </tr>
    </thead>
</table>

allan

unfortunately I could not catch any errors.

As in the server's error log shows no errors?

The only thing I see that looks slightly odd is &$key - but $key2 is not being passed by reference. Removing the & may help, but I doubt it.

I think the key will be in the server error logs.

The other thing to check is, what is the JSON that is being returned when you have the two or_where conditions?

Thanks,
Allan

coderx

As in the server's error log shows no errors?

Usually, when there is a bug in database column names or in JS editor or table, I can find errors with Chrome console.

I changed the &$key to $key, but it did not help.

It seems the search returns valid JSON response, but it returns more rows than it should. It is kind of hard to explain but I opened the access for you and if you have a minute, you could take a look for yourself. It might be something familiar for you.

URL: https://develop-ims.aei.sk/stocks/deliveries (login data are same as sent in PM on 29MAY2015)

Try to search for e.g. "FA259-50630-000XX", it should return only relevant rows but it does not.

Can you see what I mean?

allan

Ah! Yes, I see what you mean. Thanks!

The problem is that the query being constructed is ending up like:

columnFilter OR filter1 OR filter2

What you want is:

columnFilter AND ( filter1 OR filter2 )

To do that use:

    ->where( function ($q) use ( &$key, $value, $op, $key2, $value2, $op2 ) {
            $q->where_group( true );
            $q->or_where( $key, $value, $op );
            $q->or_where( $key2, $value2, $op2 );
            $q->where_group( false );
        } )

The where_group method will just add the braces. It is AND by default, so that should suit your needs. The other option is to use another closure, but I think this is just as easy, although perhaps not quite as elegant.

Allan

coderx

Thanks Allan, great help as always. :) This solution works well.

---

I just wonder – if I wanted

columnFilter OR (filter1 AND filter2)

I would write something like:

->where( function ($q) use ( $key, $value, $op, $key2, $value2, $op2 ) {
    $q->where_group( true, "OR" );
    $q->where( $key, $value, $op );
    $q->and_where( $key2, $value2, $op2 );
    $q->where_group( false );
    } )

Right?

allan

Absolutely bob on! :-)

Allan

coderx

Thanks again! :)