[FIXED] cakephp 2.x using 'NOT IN' in subquery

Issue

I have this tiny mysql query(it returns element which dont have 1,3,5 state):

SELECT DISTINCT number FROM records WHERE number NOT IN
( SELECT number FROM records WHERE state_id = 1 or state_id = 3 or state_id = 5)

In cakephp i am not good at subquery but i was able to do it:

    $conditionsSubQuery['`Record2`.`state_id`'] = array(1,3,5);

      $db = $this->Record->State->getDataSource();

      $subQuery = $db->buildStatement(
    array(
        'fields'     => array('`Record2`.`number`'),
        'table'      => $db->fullTableName($this->Record),
        'alias'      => 'Record2',
        'limit'      => null,
        'offset'     => null,
        'joins'      => array(),
        'conditions' => $conditionsSubQuery,
        'order'      => null,
        'group'      => null
    ),
    $this->Record
);

      $subQuery = ' `Record`.`number` NOT IN (' . $subQuery . ') ';

      $subQueryExpression = $db->expression($subQuery);

     $conditions[] = $subQueryExpression;



     $subQuery=$this->Record->find('first', compact('conditions'),
             //array('recursive'=>0),
             array('fields'=>array('Record.state_id')
             ));

Maybe i don't understand something good but it is really annoying to get the same result takes tons of code,i know there is $this->query for core mysql But is there a way in the find to get the same result with just properly used 'conditions'? Or cakephp does not support 'NOT IN' and thats why no easier way?

Update1: The scheme of the query look like:

(1, 'A', 1),
(2, 'A', 2),
(3, 'B', 3),
(4, 'C', 2),
(5, 'B', 1),

The result of mysql query retrive only C, since B has also the value 3,1 same as with A. Without the 'not in' B is also returned(because of having 1 value too).

I try to make it as clear as possible,i add some additional values to show which fits for the results: http://www.sqlfiddle.com/#!9/cc92a/1

Solution

Unless I've misunderstood your original query you shouldn't need to use subqueries for this at all.

In CakePHP you can use the keyword NOT in the conditions of a find to apply the NOT part of your query; to handle the IN part just pass an array of options:-

$this->Record->find(
    'all',
    array(
        'fields' => array(
            'number'
        )
        'conditions' => array(
            'NOT' => array(
                'state_id' => array(1, 3, 5)
            )
        ),
        'group' => array(
            'number'
        )
    )
);

I've used fields to limit the response to just the number column and used group to only return distinctive results.

Otherwise, to use subqueries in Cake 2 the easiest way is to perform the subquery separately and then use that response in the main query. Cake 3's new ORM is better setup for subqueries.

Update:

Based on your updated example in the question I think the easiest way of achieving what you want it to do two queries. First to get the record numbers you don't want to include ($exclude) and then use that result to exclude them from your results query:-

$exclude = $this->Record->find(
    'list',
    array(
        'fields' => array(
            'id', 'number'
        ),
        'conditions' => array(
            'state_id' => array(1, 3, 5)
        ),
        'group' => array(
            'number'
        )
    )
);

$result = $this->Record->find(
    'all',
    array(
        'fields' => array(
            'number'
        )
        'conditions' => array(
            'NOT' => array(
                'number' => $exclude
            )
        ),
        'group' => array(
            'number'
        )
    )
);

Answered By - drmonkeyninja