Yii2 NOT IN formatting

Yii 2.0 General Discussions
1

I am trying to use the QueryBuilder to get the following query, but when I try to run it I get the following error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’(2,7,9)’ AND (parent_unit_id IN ‘(2,7,9)’ at line 1

[size=“2”] The SQL being executed was: SELECT id FROM ol_unit WHERE (id NOT IN ‘(2,7,9)’ AND (parent_unit_id IN ‘(2,7,9)’[/size]

      Error Info: Array ( 	[0] => 42000 	[1] => 1064 	[2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''(2,7,9)' AND (`parent_unit_id` IN  '(2,7,9)' at line 1 )

I realize the additional single quotes surrounding the array to search are incorrect, but I cannot figure out how to remove them or change the code to show the proper results. If I don’t use the implode then only the first value is included and if I remove the added parentheses on each side of the implode, I get a cannot convert array to string error.

My code is below:




            	$ids = array();

            	$ids[] = 2;

            	$ids[] = 7;

            	$ids[] = 8;


            	while(count($result_array) > 0)

            	{                    	

                	unset($result_array);

                	$query = (new \yii\db\Query())

                    	->select('id')

                    	->where(['not in ', 'id', '('.implode(",",$ids).')'])

                    	->andWhere(['in ', 'parent_unit_id', '('.implode(",",$ids).')'])

                    	->from('ol_unit');


                	$command = $query->createCommand();

                	$result_array = $command->queryAll();
2 



<?php

$ids = [];

$ids[] = 2;

$ids[] = 7;

$ids[] = 8;


while(count($result_array) > 0) {

      unset($result_array);

      $query = (new \yii\db\Query())

      ->select('id')

      ->where(['not in ', 'id', $ids])

      ->andWhere(['in ', 'parent_unit_id', $ids])

      ->from('ol_unit');


    $command = $query->createCommand();

    $result_array = $command->queryAll();
3

Thanks samdark. Now I am receiving this error which looks like the $ids array is waiting for some parameter but I am not using params.

[size="3"]Database Exception – yii\db\Exception[/size]

[b][size="3"]Undefined offset: 1

Failed to prepare SQL: SELECT id FROM ol_unit WHERE (id NOT IN :qp0) AND (parent_unit_id IN :qp1)[/size][/b]

[size="3"] ↵ [/size]

[size="3"]Caused by: yii\base\ErrorException[/size]

[size="3"]Undefined offset: 1[/size]

4

I think the problem is the space after the operator ('in ’ and 'not in ').

Try this code:




<?php

$ids = [];

$ids[] = 2;

$ids[] = 7;

$ids[] = 8;


while(count($result_array) > 0) {

      unset($result_array);

      $query = (new \yii\db\Query())

      ->select('id')

      ->where(['not in', 'id', $ids])

      ->andWhere(['in', 'parent_unit_id', $ids])

      ->from('ol_unit');


    $command = $query->createCommand();

    $result_array = $command->queryAll();
1 Like
5

Thanks Argent that was what it was.