Multiple where clauses

coderx

Hello,

I know I can use where clause as described here: http://editor.datatables.net/docs/current/php/class-DataTables.Editor.html#_where.

And it works as expected. :)

Is it somehow possible to use two or more where clauses? E.g.

Editor::inst( $db, 'table_name' )
  ->fields( ... ) // other code
  ->where( $key, $value, $op ) // First where clause
  ->where_and( $key2, $value2, $op2 ) // Second where clause
  ->where_or( $key3, $value3, $op3 ) // Third where clause
  ->process( $_POST )
  ->json();

coderx

The formatting went wrong.. :(

Editor::inst( $db, 'table_name' )

->fields( ... ) // other code

->where( $key, $value, $op ) // First where clause

->where_and( $key2, $value2, $op2 ) // Second where clause

->where_or( $key3, $value3, $op3 ) // Third where clause

->process( $_POST )

->json();

allan

Hi,

I aded the formatting (doc for how to do it).

The answer is yes, this is possible, but Editor doesn't have where_and or where_or methods, which is why your above code doesn't work. The Query class however does, and it can be used in a closure method - but this requires the 1.4 beta (the 1.3 libraries do not support this!).

To use you would do something like:

Editor::inst( $db, 'table_name' )
  ->fields( ... )
  ->where( function ( $q ) {
    $q
      ->where( $key, $value, $op )
      ->and_where( $key2, $value2, $op2 )
      ->or_where( $key3, $value3, $op3 );
  } )
  ->process( $_POST )
  ->json();

The 1.4 beta is available for download on the Editor downloads page. It will be released as final in the next couple of weeks - just a few .NET things to finish off!

Allan

coderx

Hi Allan,

thanks for prompt reply! :) This seems like the solution I was looking for.

Peter

coderx

Hi Allan,

I am sorry that I reopen this issue, but I just tried to download newest beta Editor (Editor v1.4 beta.1 - PHP + platform-less) and the recommended solution does not work:

->where_and( $key2, $value2, $op2 ) // Error: Call to undefined method DataTables\Database\DriverMysqlQuery::where_and() in ...
->where_or( $key3, $value3, $op3 ) // Error: Call to undefined method DataTables\Database\DriverMysqlQuery::where_or() in ...

What am I missing?

Peter

allan

Apologies, the correct function names are and_where and or_where (docs). I'll update the code above.

Allan

coderx

It is strange, but it seems that only the first where works. Is there any full code example in documentation? I was not able to find it.. :(

Note: I mean there is example in http://editor.datatables.net/docs/1.4.0-beta/php/class-DataTables.Database.Query.html#_exec after opening section where( $key, $value = null, $op = "=", $bind = true ), but I was not able to use it successfully.

My code:

$key = 'column_name';
$value = 'value';
$op = '>';

$key2 = 'column_name_2';
$value2 = 'value_2';
$op2 = '=';

Editor::inst( $db, 'table' )
    ->fields( ... )
    ->leftJoin( ... )
    ->where( $key, $value, $op ) // This works as expected.
    ->where( function ($q) {
            global $key2, $value2, $op2;
            $q->where( $key2, $value2, $op2 ); // Does not work. :(
        } )
    ->process( $_POST )
    ->json();

allan

I've just tried the following with a simple value and also a closure like you have and it appears to work as expected:

    ->where( 'position', 'S%', 'LIKE' )
    ->where( function ( $q ) {
        $q->where( 'MONTH(start_date)', 'MONTH(CURRENT_DATE)', '=', false );
    } )

If you code the values into your anonymous function rather than using global does it then work?

Allan

coderx

Shortly: Yes. :) It works now as expected. Thank You, Allan! :)

I redesigned my code in order to use it this way.

More description: non-mandatory reading :)

// I needed this structure, but I can imagine, I will be able to do anything now.

// Working for my purposes
->where( function ($q) {
  $q->or_where( 'column1', 'value1', '=' );
  $q->or_where( 'column2', 'value2', '=' );
} )
// Result: Filtered data, I suppose query was '... part1 OR part2 ...'.

Just a note for others: It seems that anonymous function function ($q) { ... } can not really work with global keyword. My code and results:

// key, value variables defined beforehand

// 1. Test
->where( $key3, $value3, $op3 );
// 1. Result: Works as expected.

// 2. Test
->where( $key3, $value3, $op3 )
->where( function ($q) {
  global $key, $key2, $value, $value2, $op, $op2;
  $q->where( $key, $value, $op );
  $q->where( $key2, $value2, $op2 );
} )
// 2. Result: All rows of table. The keyword global does not work here.

// 3. Test
->where( $key3, $value3, $op3 )
->where( function ($q) {
  $q->where( 'column1', 'value1', '=' );
  $q->where( 'column2', 'value2', '=' );
} )
// 3. Result: No data displayed, I suppose query was '... part3 AND (part1 AND part2) ...'.

allan

Just a note for others: It seems that anonymous function function ($q) { ... } can not really work with global keyword.

Interesting! You could use the use() option to access external variables instead (see the PHP closure documentation).

Allan

coderx

Great help, Allan! Thanks! :)

The code is a bit cleaner using the use(). :)

Peter