Using 'OR' in CakePHP find

Question

I am using CakePHP 3.2 and following find().

if($filter_data_array['age_younger'] != '')
        {
            $conditions["icases.age <= "] = $filter_data_array['age_younger'];
        }

        if($filter_data_array['age_older'] != '')
        {
            $conditions["icases.age >= "] = $filter_data_array['age_older'];
        }

        if($filter_data_array['go_date_after'] != '')
        {
            $conditions["icases.go_date >= "] = $filter_data_array['go_date_after'];
        }

        if($filter_data_array['go_date_before'] != '')
        {
            $conditions["icases.go_date <= "] = $filter_data_array['go_date_before'];
        }

        if($filter_data_array['client'] != '')
        {
            $conditions["icases.client_id = "] = $filter_data_array['client'];
        }

        if($filter_data_array['case_name'] != '')
        {
            $conditions["icases.name = "] = $filter_data_array['case_name'];
        }

        if($filter_data_array['case_ref'] != '')
        {
            $conditions["icases.case_reference_i_d = "] = $filter_data_array['case_ref'];
        }

        if($filter_data_array['instruction_date'] != '')
        {
            $conditions["icases.date_instruction_received >= "] = $filter_data_array['instruction_date'];
        }

        $cases_data = TableRegistry::get('icases')->find('all')
                ->select(['icases.id', 'icases.state', 'icases.client_id', 'icases.name', 'icases.age', 'icases.case_reference_i_d', 'icases.go_date', 'clients.name'])
                ->innerJoin('icases_users', 'icases_users.icase_id = icases.id')
                ->where($conditions)
                ->innerJoin('clients', 'clients.id = icases.client_id')
                ->order(['clients.name' => 'ASC', 'icases.name' => 'ASC'])
                ->execute()
                ->fetchAll('assoc');

Now there is a state column in icases table and I want to filter by

icases.state='active' OR icases.state='pending' OR icases.state='archive'

My question is how do I integrate it into my existing $conditions array?

Thank you in advance.

Answer 1

First of all, I would recommend to follow the CakePHP Conventions. Your Code would become much more readable.

I would also recommend to connect your relations inside the Table objects. This keeps your code DRY and you can just load the associated models by using contain instead of 'manually' using innerJoin.

Check out the following code. It’s not tested, but should work. Using the Advanced conditions is a little more 'Cake-ish' and easily mantainable. If you don’t care about the rest, just have a look at the $orConditions in the code.

$query = TableRegistry::get('Cases')->find('all')
    ->select([
        'Cases.id', 
        'Cases.state', 
        'Cases.client_id', 
        'Cases.name', 
        'Cases.age', 
        'Cases.case_reference_i_d', 
        'Cases.go_date', 
        'Clients.name'
    ])
    ->order([
        'Clients.name' => 'asc', 
        'Cases.name' => 'asc'
    ])
    ->contain([
        'Clients',
        'Users'
    ])
    ->where(function ($exp) use ($filter_data_array) {

        if (!empty($filter_data_array['age_younger'])) {
            $exp = $exp->lte('Cases.age', $filter_data_array['age_younger']);
        }
        if (!empty($filter_data_array['age_older'])) {
            $exp = $exp->gte('Cases.age', $filter_data_array['age_older']);
        }
        if (!empty($filter_data_array['go_date_after'])) {
            $exp = $exp->gte('Cases.go_date', $filter_data_array['go_date_after']);
        }
        if (!empty($filter_data_array['go_date_before'])) {
            $exp = $exp->lte('Cases.go_date', $filter_data_array['go_date_before']);
        }
        if (!empty($filter_data_array['client'])) {
            $exp = $exp->eq('Cases.client_id', $filter_data_array['client']);
        }
        if (!empty($filter_data_array['case_name'])) {
            $exp = $exp->eq('Cases.name', $filter_data_array['case_name']);
        }
        if (!empty($filter_data_array['case_ref'])) {
            $exp = $exp->eq('Cases.case_reference_i_d', $filter_data_array['case_ref']);
        }
        if (!empty($filter_data_array['instruction_date'])) {
            $exp = $exp->gte('Cases.date_instruction_received', $filter_data_array['instruction_date']);
        }

        $orConditions = $exp->or_([
            'Cases.state' => 'active',
            'Cases.state' => 'pending',
            'Cases.state' => 'archive'
        ]);

        return $exp->add($orConditions);
    });

$cases_data = $query->execute();

Answer 2

Here is an solution to find all matching with OR conditions

$conditions = [];     #For storing all of your conditions as an array

if($filter_data_array['age_younger'] != ''){
    $conditions[]["icases.age <= "] = $filter_data_array['age_younger'];
}

if($filter_data_array['age_older'] != ''){
    $conditions[]["icases.age >= "] = $filter_data_array['age_older'];
 }

if($filter_data_array['go_date_after'] != ''){
    $conditions[]["icases.go_date >= "] = $filter_data_array['go_date_after'];
}

if($filter_data_array['go_date_before'] != ''){
    $conditions[]["icases.go_date <= "] = $filter_data_array['go_date_before'];
}

if($filter_data_array['client'] != ''){
    $conditions[]["icases.client_id = "] = $filter_data_array['client'];
}

if($filter_data_array['case_name'] != ''){
    $conditions[]["icases.name = "] = $filter_data_array['case_name'];
}

if($filter_data_array['case_ref'] != ''){
    $conditions[]["icases.case_reference_i_d = "] = $filter_data_array['case_ref'];
}

if($filter_data_array['instruction_date'] != ''){
    $conditions[]["icases.date_instruction_received >= "] = $filter_data_array['instruction_date'];
}

$cases_data = TableRegistry::get('icases')->find('all')
        ->select(['icases.id', 'icases.state', 'icases.client_id', 'icases.name', 'icases.age', 'icases.case_reference_i_d', 'icases.go_date', 'clients.name'])
        ->innerJoin('icases_users', 'icases_users.icase_id = icases.id')
        ->where(['OR'=>$conditions])   #All of your `OR` conditions here
        ->innerJoin('clients', 'clients.id = icases.client_id')
        ->order(['clients.name' => 'ASC', 'icases.name' => 'ASC'])
        ->execute()
        ->fetchAll('assoc');

Note : Your code has been changed like as -

$conditions[INDEX] to $conditions[][INDEX]

AND

->where($conditions) to ->where(['OR'=>$conditions])

Answer 3

I think you should use the Advanced Conditions provided by cake. The first example suits your need as far as I understand.

So you should add this before executing the query

$conditions['OR'] = [['icases.state' => 'active'], ['icases.state' => 'pending'], ['icases.state' => 'archive']];