Search code examples
phpcakephp

get data using not ids not working in cakephp

I want to select data for all rows where the below ids dont exist. I thought this would work but there is no error and the below ids still appear in the retrieved records. I am using cakephp 2.5 for this

  $ids///

array(
(int) 0 => '14721',
(int) 1 => '14731',
(int) 2 => '14905',
(int) 3 => '15808',
(int) 4 => '15818',



 $test=$this->Lessonbak->find('all', array(
      'fields'=>array('id'),
     'recursive' => -1 ) );

 $ids=array();
 foreach(  $test as  $item):
     array_push($ids,$item['Lessonbak']['id']);

 endforeach;  

  $lessonstudent2=$this->LessonsStudent2->find('all', array(
         'conditions' => array(  "NOT" => array( 'LessonsStudent2.lesson_id' => $ids )),
         'recursive' => -1 ) );
Solution

  • Instead of selecting all of the Lessonbak ids, pushing them to an array, and using that in your LessonsStudent2 query, you should simply use a subquery:

    $dbo = $this->getDataSource();
$subquery = $dbo->buildStatement(
    array(
        'table' => 'lessonbak',
        'alias' => 'Lessonbak',
        'recursive' => -1,
        'fields' => array(
            'Lessonbak.id'
        )
    ),
    $this
)


$this->LessonsStudent2->find('all', array(
    'conditions' => array(
        'NOT' => array(
            'LessonsStudent2.lesson_id IN (' . $subquery . ')'
        )
    )
)