Cakephp Find all WHERE in two categories

Question

I have trouble finding items that belong to two or more categories in cakephp 2.

In my Controller I have the following:

$kategories = array(1,2);
$options['conditions'] = array('Categorie.id' => $kategories);
$items = $this->Item->find('all',$options);

The query above shows all Items that have either associated category-id 1 or 2.

How can I search for all items, that belong to category.id 1 AND 2 (not OR).

I tried using the 'AND' array with no luck:

$options['conditions'] = array('AND' => array('Categorie.id' => $kategories));

Any ideas?

Thanks in advance!!

Answer 1

I looked a little further on this and found the solution:

To find all items, that belong to category.id 1 AND 2 (not OR) I just had to add a 'group' parameter to my find like this:

$options['group'] = array('Item.id HAVING COUNT(DISTINCT Categorie.id) > 1');

The complete query (working):

$kategories = array(1,2);
$options['conditions'] = array('Categorie.id' => $kategories);
$options['group'] = array('Item.id HAVING COUNT(DISTINCT Categorie.id) > 1');
$items = $this->Item->find('all',$options);

Answer 2

You can use IN operator:

$options['conditions'] = array('Categorie.id IN' => $kategories);

Or in that case if you always want to look for this two categories use AND:

$options['conditions'] = array('AND' => array(
    array('Categorie.id' => $kategories[0]),
    array('Categorie.id' => $kategories[1])
));

Answer 3

Try this:

$kategories = array(1,2);
$kategories = implode(',',$kategories );


 $options['conditions'] = array('Categorie.id in ( ' . $kategories . ' )') ;
 $items = $this->Item->find('all',$options);