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