WHERE (0=1)

Before I post a bunch of code that might be unnecessary, does anyone know off the top of his/her head what would produce that where clause from a CActiveDataProvider?

When I have a default call to the ADMIN controller, the entire WHERE clause of the query is the above WHERE (0=1) and I get no results found.

If there is no obvious answer I will post code.

post your code and database engine you are using

Hey redguy, thanks for responding. Coincidentally, I am making heavy use of your wiki on searching/sorting by related tables!

My database is MySQL, and the storage engine for all three tables is MyISAM. I guess that is the default on my hosting provider (HostGator).

Code:

Model:




class Customers extends CActiveRecord


	public $statusSearch=array();

	public $budget_search;

	public $size_search;

	public $deadline_search;

	public $source_search;

	public $neighborhood_search;

	public $anyAll='AND';

	public $sortBy='t.last';

	public $colSelect='*';

	public $leadsSelect='*';

	public $searchSelect='*';




...


	public function search()

	{

		// Warning: Please modify the following code to remove attributes that

		// should not be searched.


		$criteria=new CDbCriteria;


		$criteria->select=$this->colSelect;

			$criteria->together=true;

			$criteria->with=array(

				'leads'=>array('select'=>$this->leadsSelect),

				'searches'=>array('select'=>$this->searchSelect)

				);

			$criteria->order=$this->sortBy . ' ASC';

			

		$criteria->compare('id',$this->id,false,$this->anyAll);

		$criteria->compare('last',$this->last,false,$this->anyAll);

		$criteria->compare('pref',$this->pref,false,$this->anyAll);

		$criteria->compare('first',$this->first,false,$this->anyAll);

		$criteria->compare('middle',$this->middle,false,$this->anyAll);

		$criteria->compare('suf',$this->suf,false,$this->anyAll);

		$criteria->compare('email',$this->email,false,$this->anyAll);

		$criteria->compare('phone',$this->phone,false,$this->anyAll);

		$criteria->compare('preferred',$this->preferred);

		$criteria->compare('status',$this->status);

		$criteria->compare('home',$this->home,false,$this->anyAll);

		$criteria->compare('notes',$this->notes,false,$this->anyAll);

		$criteria->compare('agent',$this->agent,false,$this->anyAll);

		$criteria->compare('agnt_x',$this->agnt_x,false,$this->anyAll);

		$criteria->compare('createdDate',$this->createdDate,false,$this->anyAll);

		$criteria->compare('upDated',$this->upDated,false,$this->anyAll);

		$criteria->compare('searches.budget',$this->budget_search,false,$this->anyAll);

		$criteria->compare('searches.size',$this->size_search,false,$this->anyAll);

		$criteria->compare('searches.deadline',$this->deadline_search,false,$this->anyAll);

		$criteria->compare('searches.neighborhood',$this->neighborhood_search,false,$this->anyAll);

		$criteria->compare('leads.source',$this->source_search,false,$this->anyAll);

		

		$criteria->addInCondition('status',$this->statusSearch);

		


		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

			'pagination'=>array('pageSize'=>20),

		));

	}

Controller:


	public function actionAdmin()

	{

		$model=new Customers('search');

		$model->unsetAttributes();  // clear any default values

		if(isset($_GET['Customers']))

			$model->attributes=$x=$_GET['Customers'];

			if($model->budget_search) $model->budget_search=$x['budgetOpr'] . $model->budget_search;

			if($model->size_search) $model->size_search=$x['sizeOpr'] . $model->size_search;

			if($model->deadline_search) $model->deadline_search=$x['deadlineOpr'] . $model->deadline_search;

			$model->colSelect='t.status,t.last,t.first,t.email,t.phone,t.agent';

			$model->leadsSelect='leads.source,leads.dateContact';

			$model->searchSelect='searches.budget,searches.size,searches.deadline,searches.neighborhood';

				


		$this->render('admin',array(

			'model'=>$model,

		));

	}



SQL from the log (this is the query generated with no search terms:


Querying SQL: SELECT `t`.`status` AS `t0_c9`, `t`.`last` AS `t0_c1`,

`t`.`first` AS `t0_c3`, `t`.`email` AS `t0_c6`, `t`.`phone` AS `t0_c7`,

`t`.`agent` AS `t0_c12`, `t`.`id` AS `t0_c0`, `leads`.`source` AS `t1_c7`,

`leads`.`dateContact` AS `t1_c4`, `leads`.`id` AS `t1_c0`,

`searches`.`budget` AS `t2_c4`, `searches`.`size` AS `t2_c6`,

`searches`.`deadline` AS `t2_c7`, `searches`.`neighborhood` AS `t2_c9`,

`searches`.`id` AS `t2_c0` FROM `customers` `t`  LEFT OUTER JOIN `leads`

`leads` ON (`leads`.`personId`=`t`.`id`)  LEFT OUTER JOIN `searches`

`searches` ON (`searches`.`personId`=`t`.`id`)  WHERE (0=1) ORDER BY t.last

ASC LIMIT 20

Thanks!

Have a look on addInCondition doc and source code

http://www.yiiframework.com/doc/api/1.1/CDbCriteria#addInCondition-detail


public function addInCondition($column,$values,$operator='AND'){

    if(($n=count($values))<1)

        return $this->addCondition('0=1',$operator);

// 0=1 is used because in MSSQL value alone can't be used in WHERE

    ···

Are you sure your $this->statusSearch complies?

Edit: From your model, you initialize it as array() maybe yoy forgot to populate it in your controller?

The above code compiles, produces the query and displays the view with "no results found."

Thanks! I knew I had seen something about 0=1 in the docs but had no idea where. I will look there. Maybe if I give statusSearch a default value of all the options, that will work!

It worked! I got the complete list of customers. Now I will add in my search form and see what happens.

Thanks to both of you!

I added my search form and it still works. Ready to move on to the next step!

Thanks, guys! Redguy, your wiki was of enormous value to me!

My question was with verb “comply” not “compile” :D

Oops haha nerd mistake ;D

Nice to hear it :)

All right, so I was a bit premature in announcing everything ok. Everything is ok EXCEPT that my statusSearch variable never changes.

I fixed the original problem by modifying the model as such:


class Customers extends CActiveRecord


        public $statusSearch=array(1,2,3,4);



So, by default, the grid will show the customers of every status.

In my search form, I use a checkbox list, because I want people to be able to search for any combination of statuses. My search form code looks as follows:


	<div class="inputFieldCheckList">

		<?php echo $form->labelEx($model,'statusSearch'); ?>

		<?php echo $form->checkBoxList($model,  'statusSearch', getStatusOptions(),array('separator'=>'&nbsp;&nbsp;')); ?>

		<?php echo $form->error($model,'statusSearch'); ?>

	</div>



This produces a checkbox list with four boxes, all of them checked.

Yii produces the following source code from my code:




	<div class="inputFieldCheckList">

		<label for="Customers_statusSearch">Status Search</label>

		<input id="ytCustomers_statusSearch" type="hidden" value="" name="Customers[statusSearch]" />

<span id="Customers_statusSearch">

<input id="Customers_statusSearch_0" value="1" checked="checked" type="checkbox" name="Customers[statusSearch][]" />

<label for="Customers_statusSearch_0">Inquiry</label>&nbsp;&nbsp;

<input id="Customers_statusSearch_1" value="2" checked="checked" type="checkbox" name="Customers[statusSearch][]" /> 

<label for="Customers_statusSearch_1">Active Customer</label>&nbsp;&nbsp;

<input id="Customers_statusSearch_2" value="3" checked="checked" type="checkbox" name="Customers[statusSearch][]" />

 <label for="Customers_statusSearch_2">Inactive Customer</label>&nbsp;&nbsp;

<input id="Customers_statusSearch_3" value="4" checked="checked" type="checkbox" name="Customers[statusSearch][]" />

 <label for="Customers_statusSearch_3">Client</label></span>			</div>



When I use the Advanced Search form, no matter what I do with the checkboxes, the grid displays all status customers.

Every other search field works.

The one difference between searchStatus and all the other search fields is that the other fields are in a compare() CDbCriteria call, and searchStatus is in an addInCondition() call.

Thanks for all answers so far, and for your time.

Check if you added ‘statusSearch’ to safe attributes in ‘search’ scope (in rules() function)

In addition to the safe array, maybe you should do:




if(count($this->statusSearch)

    $criteria->addInCondition('status',$this->statusSearch);



Thanks, guys, you were spot on. It is all working great now.

I was able to set up the individual record view all by myself! B)