mardi 5 mai 2015

cakephp 2.x using 'NOT IN' in subquery

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(27,28,30);

      $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?

Aucun commentaire:

Enregistrer un commentaire