CakePHP many conditions to one field

Question

This code:

$conditions = array(    
    'fields' => array(
        'User.id'
        ),      
    'conditions' => array(
        'AND' => array(
            'UsersProblem.problem_id' => 38,
            'UsersProblem.problem_id' => 34,
            ),
        ),
    'recursive' => -1
    );

$conditions['joins'][] = array(
    'table' => 'users_problems',
    'alias' => 'UsersProblem',
    'type' => 'INNER',
    'conditions' => array(
        'User.id = UsersProblem.user_id',
    ));

Is transforming to this SQL query:

SELECT `User`.`id` 
FROM `lawyers`.`users` 
  AS `User` 
INNER JOIN `lawyers`.`users_problems` 
  AS `UsersProblem` 
  ON (`User`.`id` = `UsersProblem`.`user_id`) 
WHERE `UsersProblem`.`problem_id` = 34

Where is AND UsersProblem.problem_id = 38"? How to create correct find condition to get users with some list of problems? Which are linked as many to many relationship.

Using $this->Problem->find is not possible, beause I need to use 2 joins: users_problems and users_practices. And use for them AND condition, like this:

'AND' => array(
    'UsersProblem.problem_id' => 38,
    'UsersProblem.problem_id' => 34,
    'UsersPractices.practice_id' => 1,
    'UsersPractices.practice_id' => 2,
),

score 4 · Answer 1 · answered Jan 20 '14 at 03:52

You're overwriting 'UsersProblem.problem_id'. Just think about it like the array it is. CakePHP is just using the PHP array structure to do these finds, and that means it follows array rules. But to get around that in cakephp, you're supposed to use a two dimensional array

Like this:

'AND' => array(
        array('UsersProblem.problem_id' => 38),
        array('UsersProblem.problem_id' => 34)
),

Instead of this:

'AND' => array(
        'UsersProblem.problem_id' => 38,
        'UsersProblem.problem_id' => 34
),

That way, instead of trying to set the key 'UsersProblem.problem_id' twice, you're making an array that looks like

0 -> array('UsersProblem.problem_id' => 38)
1 -> array('UsersProblem.problem_id' => 34)

And that will work, it's a workaround the CakePHP guys built into the system for this kind of situation.

score 0 · Answer 2 · answered Jan 20 '14 at 03:55

I think you need to use OR instead of AND:

$conditions = array(    
'fields' => array(
    'User.id'
    ),      
'conditions' => array(
    'OR' => array(
        array('UsersProblem.problem_id' => 38),
        array('UsersProblem.problem_id' => 34),
        ),
    ),
'recursive' => -1
);

You can also roll this up as follows:

 $conditions = array(    
    'fields' => array(
        'User.id'
        ),      
    'conditions' => array(
        'OR' => array(
            'UsersProblem.problem_id' => array(34, 38)
        ),
    'recursive' => -1
);

score 0 · Answer 3 · edited May 23 '17 at 10:25

Change your conditions array as shown below:

 $options = array(    
        'fields' => array('User.id'),      
        'conditions' => array('UsersProblem.problem_id' => array(38,34)),
        'recursive' => -1
        );
 $options['joins'] = array(
                array(
                     'table' => 'users_practices',
                     'alias' => 'UsersPractices',
                     'type' => 'INNER',
                     'conditions' => array(
                              /CONDITION OF JOIN/
                      ),
               array(
                     'table' => 'users_problems',
                     'alias' => 'UsersProblem',
                     'type' => 'INNER',
                     'conditions' => array(
                              'User.id = UsersProblem.user_id',
                      )
                );

From User Model:

 $this->find('list',$options);

Do not use AND/ OR because they are not optimized for above case.

For reference:

MYSQL OR vs IN performance and CakePHP right way to do this (get value from setting's table)

score 0 · Answer 4 · answered Jan 20 '14 at 09:06

**Try the code for join tables in cakephp.......**

$options = array('joins' => array(
                        array(
                            'table' => 'modelname1',
                            'alias' => 'modelName1',
                            'type' => 'LEFT',
                            'foreignKey' => false,
                            'conditions' => array('modelname1.id = modelname2.mid')
                        )
                    ),
                    'fields' => array('modelName1.field1', 'modelName1.field2', 'modelName2.field1', 'modelName2.field2'),
                    'conditions' => array('modelname.id' => 1, 'modelname2.field1' => ''),
                    'limit' => 10, 'page' => 1);
                $this->modelName2->find($options);

CakePHP many conditions to one field

4 Answers4