AR, missing parenthesis generation for IN/NOT IN

Yii 2.0 General Discussions
1

I am not sure If I missed something, but if I use

the following condition like mentioned in the documentation, I get no parenthesis around the in/where in part of the SQL query, which leads to an exception.

It’s a postgresql database.

concrete code:

This does not work




$updateWorkflows = models\UpdateWorkflow::find()

			->where([

				'and',

				'status='.Workflow::WORKFLOW_STATUS_TODO,

				'type='.Workflow::WORKFLOW_TYPE_UPDATE,

				['in ','library', ['lib1','lib2']]

			])

			->orderBy('date asc')->all();

this does not work either




$updateWorkflows = models\UpdateWorkflow::find()

			->where([

				'status'=>Workflow::WORKFLOW_STATUS_TODO,

				'type'=>Workflow::WORKFLOW_TYPE_UPDATE,

			])

			->andWhere(['not in ','library', $blockedLibs])

			->orderBy('date asc')->all();

Edit:

Not sure about the parenthesis. (I used getRawSQL, which is not reliable as the docs states)

I get an "Undefined Offset" Message:

Exception ‘yii\db\Exception’ with message 'Undefined offset: 1

Failed to prepare SQL: SELECT * FROM “workflow” WHERE (status=103) AND (type=0) AND (“library” NOT IN :qp0) ORDER BY “date”’

2

you have spaces in the operators 'in ’ and 'not in ', remove them and it should work, I have used these operators successfully a few times

3

The example in the documentation has spaces too, and I did try without spaces. Have to investigate further, I can’t see the query in the postgres log (only in Yii application log), so there should be something wrong in the query building/ parameter binding process.

4

Edit: got it to work now, strange…

Thought I tried everything last time, but




$updateWorkflows = models\UpdateWorkflow::find()

			->where([

				'and',

				'status='.Workflow::WORKFLOW_STATUS_TODO,

				'type='.Workflow::WORKFLOW_TYPE_UPDATE,

				['not in','library',$blockedLibs]

			])

			->orderBy(['date'=>SORT_ASC])

			->all();

works. Sorry for my thread, seems that I needed a rest before trying again :)