How to use ->OR_WHERE

jawz302

I am trying to dial in a query result and need some help.
I can get the WHERE clause working fine if they are WHERE AND WHERE, but I am having problems with syntax to mix in a few OR_WHERE clauses.

I need to refine query so that these 3 WHERE clauses are ALL true (AND)
->where( 'system.domain', 'eagle.com' )
->where( 'system.status', 'production' )
->where( 'software.current', 'y' )

AND at least 1 of the following WHERE clauses is true (OR)
->where( 'software.name', 'Microsoft Office%Standard%', 'LIKE' )
->or_where( 'software.name', 'Microsoft Office%Professional%', 'LIKE' )
->or_where( 'software.name', 'Microsoft Office%Basic%', 'LIKE' )
->or_where( 'software.name', 'Microsoft Office%Home%', 'LIKE' )

All help would be appreciated. Thank you in advance.

allan

You need to use a grouping function:

->where( 'system.domain', 'eagle.com' )
->where( 'system.status', 'production' )
->where( 'software.current', 'y' )
->where( function ( $q ) {
  $q
    ->where( 'software.name', 'Microsoft Office%Standard%', 'LIKE' )
    ->or_where( 'software.name', 'Microsoft Office%Professional%', 'LIKE' )
    ->or_where( 'software.name', 'Microsoft Office%Basic%', 'LIKE' )
    ->or_where( 'software.name', 'Microsoft Office%Home%', 'LIKE' )
} )

Allan

jawz302

Allan,
I figured that I needed to use a function, but keep getting syntax errors. I receive the following error when I use your syntax above - Parse error: syntax error, unexpected '}' in report2.php on line 49.
I removed the bracket, added semicolon, deleted bracket, etc..., but just can't get it right.
Here is my current code and the current error I receive:

<?php

// DataTables PHP library
include( "/DataTables.php" );

// Increase memory limit
ini_set('memory_limit', '256M'); // or you could use 1G

// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate,
DataTables\Editor\ValidateOptions;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'system' )
->field(
Field::inst( 'system.name' ),
Field::inst( 'system.ip' ),
Field::inst( 'system.description' ),
Field::inst( 'system.os_name' ),
Field::inst( 'system.model' ),
Field::inst( 'system.serial' ),
Field::inst( 'system.switch_system_id' ),
Field::inst( 'system.switch_port' ),
Field::inst( 'system.id' )
->options( Options::inst()
->table( 'software' )
->value( 'system_id' )
->label( 'name' )
->where( 'system.domain', 'eagle.com' )
->where( 'system.status', 'production' )
->where( 'software.current', 'y' )
->where( function ( $q ) {
$q
->where( 'software.name', 'Microsoft Office%Standard%', 'LIKE' )
->or_where( 'software.name', 'Microsoft Office%Professional%', 'LIKE' )
->or_where( 'software.name', 'Microsoft Office%Basic%', 'LIKE' )
->or_where( 'software.name', 'Microsoft Office%Home%', 'LIKE' )
} )
)
->validator( Validate::dbValues() ),
Field::inst( 'software.name' )
)
->leftJoin( 'software', 'software.system_id', '=', 'system.id' )
->process( $_POST )
->json();

Error received - Parse error: syntax error, unexpected '}' in report2.php on line 45.
Line 45 seems to be the }) just below the end of the WHERE function.
Any ideas?
Thank you.

rf1234

Try this please:

<?php

// DataTables PHP library
include( "/DataTables.php" );

// Increase memory limit
ini_set('memory_limit', '256M'); // or you could use 1G

// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate,
DataTables\Editor\ValidateOptions;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'system' )
->field(
Field::inst( 'system.name' ),
Field::inst( 'system.ip' ),
Field::inst( 'system.description' ),
Field::inst( 'system.os_name' ),
Field::inst( 'system.model' ),
Field::inst( 'system.serial' ),
Field::inst( 'system.switch_system_id' ),
Field::inst( 'system.switch_port' ),
Field::inst( 'system.id' )
    ->options( Options::inst()
        ->table( 'software' )
        ->value( 'system_id' )
        ->label( 'name' )        
        ->where( function ( $q ) {
            $q ->where( 'system.domain', 'eagle.com' )
               ->where( 'system.status', 'production' )
               ->where( 'software.current', 'y' )
               ->where( function($r) {
                  $r ->where( 'software.name', 'Microsoft Office%Standard%', 'LIKE' )
                     ->or_where( 'software.name', 'Microsoft Office%Professional%', 'LIKE' )
                     ->or_where( 'software.name', 'Microsoft Office%Basic%', 'LIKE' )
                     ->or_where( 'software.name', 'Microsoft Office%Home%', 'LIKE' );
                  });
        })
    )
->validator( Validate::dbValues() ),
Field::inst( 'software.name' )
)
->leftJoin( 'software', 'software.system_id', '=', 'system.id' )
->process( $_POST )
->json();

jawz302

Thank you rf1234!
Code you sent did not quite work, but when I moved your WHERE function below the LeftJoin that did the trick!
Here is the working code:

<?php
 
// DataTables PHP library
include( "/DataTables.php" );
 
// Increase memory limit
ini_set('memory_limit', '256M'); // or you could use 1G
 
// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate,
DataTables\Editor\ValidateOptions;
 
// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'system' )
->field(
Field::inst( 'system.name' ),
Field::inst( 'system.ip' ),
Field::inst( 'system.description' ),
Field::inst( 'system.os_name' ),
Field::inst( 'system.model' ),
Field::inst( 'system.serial' ),
Field::inst( 'system.switch_system_id' ),
Field::inst( 'system.switch_port' ),
Field::inst( 'system.id' )
    ->options( Options::inst()
        ->table( 'software' )
        ->value( 'system_id' )
        ->label( 'name' )       
    )
->validator( Validate::dbValues() ),
Field::inst( 'software.name' )
)
->leftJoin( 'software', 'software.system_id', '=', 'system.id' )
        ->where( function ( $q ) {
            $q ->where( 'system.domain', 'eagle.com' )
               ->where( 'system.status', 'production' )
               ->where( 'software.current', 'y' )
               ->where( function($r) {
                  $r ->where( 'software.name', 'Microsoft Office%Standard%', 'LIKE' )
                     ->or_where( 'software.name', 'Microsoft Office%Professional%', 'LIKE' )
                     ->or_where( 'software.name', 'Microsoft Office%Basic%', 'LIKE' )
                     ->or_where( 'software.name', 'Microsoft Office%Home%', 'LIKE' );
                  });
        })
->process( $_POST )
->json();

rf1234

ok, good that you got it solved! I had the impression that you needed the where clause for the options instance and not for the entire query. Both is possible in parralel by the way.