Problem with CActiveDataProvider

Hi to everybody.

This is my code:




public function actionSearch()

	{

		$criteria=new CDbCriteria;

		$qs='';


		if (isset($_POST['criteria']) && $_POST['criteria'])

		{

			$qs=$_POST['criteria'];

			

			$criteria->with=array(

				'owner',

				'creator',

				'editor',

				'tags',

			);

			

			

			$criteria->addCondition(

				array(

					"`t`.`name` LIKE :criteria",

					"`t`.`description` LIKE :criteria",

					"`t`.`created_at` LIKE :criteria",

					"`t`.`last_edit_at` LIKE :criteria",

				

					"`owner`.`nick` LIKE :criteria",

					"`creator`.`nick` LIKE :criteria",

					"`editor`.`nick` LIKE :criteria",

				

					"`tags`.`name` LIKE :criteria",

				),

				'OR'

			);

			

			

			$criteria->params=array(

				':criteria'=>'%' . $_POST['criteria'] .'%'

			);

			

			

		}

		else

		{

			$criteria->condition="1=0";

		}

		

		$nodes=Node::model()->findAll($criteria);

//		foreach ($nodes as $n)

//		{

//			print $n->name.'<br>';

//		}

//		exit;

		

		

	//FIXME se uso CActiveDataProvider ottengo un errore

//		$dataProvider=new CActiveDataProvider('Node',array(

//			'criteria'=>$criteria

//		));


		$dataProvider=new CArrayDataProvider($nodes);

		

		

		

		$this->render(

			'search',

			array(

				'dataProvider'=>$dataProvider,

				'qs'=>$qs

			)

		);


	}

}



This method work.

But if I use code with FIXME lines I obtain this error:




CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not 

found: 1054 Unknown column 'tags.name' in 'where clause'. The SQL statement

executed was: SELECT `t`.`id` AS `t0_c0`, `t`.`name` AS `t0_c1`,

`t`.`description` AS `t0_c2`, `t`.`subtype_id` AS `t0_c3`, `t`.`owner_id` AS

`t0_c4`, `t`.`collection_id` AS `t0_c5`, `t`.`link_to` AS `t0_c6`,

`t`.`created_at` AS `t0_c7`, `t`.`creator_id` AS `t0_c8`, `t`.`last_edit_at` AS

`t0_c9`, `t`.`editor_id` AS `t0_c10`, `owner`.`id` AS `t1_c0`, `owner`.`surname`

AS `t1_c1`, `owner`.`name` AS `t1_c2`, `owner`.`nick` AS `t1_c3`,

`owner`.`password` AS `t1_c4`, `owner`.`last_login` AS `t1_c5`, `owner`.`active`

AS `t1_c6`, `owner`.`created_at` AS `t1_c7`, `owner`.`creator_id` AS `t1_c8`, 

`owner`.`last_edit_at` AS `t1_c9`, `owner`.`editor_id` AS `t1_c10`,

`creator`.`id` AS `t2_c0`, `creator`.`surname` AS `t2_c1`, `creator`.`name` AS

`t2_c2`, `creator`.`nick` AS `t2_c3`, `creator`.`password` AS `t2_c4`,

`creator`.`last_login` AS `t2_c5`, `creator`.`active` AS `t2_c6`,

`creator`.`created_at` AS `t2_c7`, `creator`.`creator_id` AS `t2_c8`,

`creator`.`last_edit_at` AS `t2_c9`, `creator`.`editor_id` AS `t2_c10`,

`editor`.`id` AS `t3_c0`, `editor`.`surname` AS `t3_c1`, `editor`.`name` AS

`t3_c2`, `editor`.`nick` AS `t3_c3`, `editor`.`password` AS `t3_c4`,

`editor`.`last_login` AS `t3_c5`, `editor`.`active` AS `t3_c6`,

`editor`.`created_at` AS `t3_c7`, `editor`.`creator_id` AS `t3_c8`,

`editor`.`last_edit_at` AS `t3_c9`, `editor`.`editor_id` AS `t3_c10` FROM `node`

`t` LEFT OUTER JOIN `user` `owner` ON (`t`.`owner_id`=`owner`.`id`) LEFT OUTER

JOIN `user` `creator` ON (`t`.`creator_id`=`creator`.`id`) LEFT OUTER JOIN

`user` `editor` ON (`t`.`editor_id`=`editor`.`id`) WHERE ((`t`.`name` LIKE

:criteria) OR (`t`.`description` LIKE :criteria) OR (`t`.`created_at` LIKE

:criteria) OR (`t`.`last_edit_at` LIKE :criteria) OR (`owner`.`nick` LIKE

:criteria) OR (`creator`.`nick` LIKE :criteria) OR (`editor`.`nick` LIKE

:criteria) OR (`tags`.`name` LIKE :criteria)) LIMIT 10. Bound with

:criteria='%tag%' 




Why?

I cannot use CActiveDataProvider with some criteria in findAll() method?

Thanks.

Best regards

freemind

I can see you have


$criteria->with=array(

                                'owner',

                                'creator',

                                'editor',

                                'tags',

                        );



But I cant see that tags table joined in the generated SQL, can you please shows us the relations method of Node model?

Thanks for reply!

This is method:




	public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

			'collection' => array(self::BELONGS_TO, 'Collection', 'collection_id'),

			'linkTo' => array(self::BELONGS_TO, 'Node', 'link_to'),

			'nodesLinked' => array(self::HAS_MANY, 'Node', 'link_to'),

			'subtype' => array(self::BELONGS_TO, 'Subtype', 'subtype_id'),

			'creator' => array(self::BELONGS_TO, 'User', 'creator_id'),

			'editor' => array(self::BELONGS_TO, 'User', 'editor_id'),

			'owner' => array(self::BELONGS_TO, 'User', 'owner_id'),

			'tags' => array(self::MANY_MANY, 'Tag', 'node_has_tag(node_id, tag_id)','order'=>'tags.name ASC'),

		// qui le relazioni con i sottotipi

			'NodeText' => array(self::HAS_ONE, 'NodeText', 'node_id'),

			'NodeFile' => array(self::HAS_ONE, 'NodeFile', 'node_id'),

		);

	}



I use this relation in some parts without problems.

Thanks.

Regards

freemind

I dont know but i can guess the problem its it being a many_many relationship, can you please try to se thogether=>true in the "with" config and post the generated SQL again? thanks

Hi Asgaroth,

your suggest is good and now query its right with left join on node_has_tag and tag tables!

But, why?

I think that I should have the same problem in other parts of the program but the problem is only here!

The query now is:




SELECT `t`.`id` AS `t0_c0`, `t`.`name` AS

`t0_c1`, `t`.`description` AS `t0_c2`, `t`.`subtype_id` AS `t0_c3`,

`t`.`owner_id` AS `t0_c4`, `t`.`collection_id` AS `t0_c5`, `t`.`link_to` AS

`t0_c6`, `t`.`created_at` AS `t0_c7`, `t`.`creator_id` AS `t0_c8`,

`t`.`last_edit_at` AS `t0_c9`, `t`.`editor_id` AS `t0_c10`, `owner`.`id` AS

`t1_c0`, `owner`.`surname` AS `t1_c1`, `owner`.`name` AS `t1_c2`,

`owner`.`nick` AS `t1_c3`, `owner`.`password` AS `t1_c4`,

`owner`.`last_login` AS `t1_c5`, `owner`.`active` AS `t1_c6`,

`owner`.`created_at` AS `t1_c7`, `owner`.`creator_id` AS `t1_c8`,

`owner`.`last_edit_at` AS `t1_c9`, `owner`.`editor_id` AS `t1_c10`,

`creator`.`id` AS `t2_c0`, `creator`.`surname` AS `t2_c1`, `creator`.`name`

AS `t2_c2`, `creator`.`nick` AS `t2_c3`, `creator`.`password` AS `t2_c4`,

`creator`.`last_login` AS `t2_c5`, `creator`.`active` AS `t2_c6`,

`creator`.`created_at` AS `t2_c7`, `creator`.`creator_id` AS `t2_c8`,

`creator`.`last_edit_at` AS `t2_c9`, `creator`.`editor_id` AS `t2_c10`,

`editor`.`id` AS `t3_c0`, `editor`.`surname` AS `t3_c1`, `editor`.`name` AS

`t3_c2`, `editor`.`nick` AS `t3_c3`, `editor`.`password` AS `t3_c4`,

`editor`.`last_login` AS `t3_c5`, `editor`.`active` AS `t3_c6`,

`editor`.`created_at` AS `t3_c7`, `editor`.`creator_id` AS `t3_c8`,

`editor`.`last_edit_at` AS `t3_c9`, `editor`.`editor_id` AS `t3_c10`,

`tags`.`id` AS `t4_c0`, `tags`.`user_id` AS `t4_c1`, `tags`.`name` AS

`t4_c2` FROM `node` `t`  LEFT OUTER JOIN `user` `owner` ON

(`t`.`owner_id`=`owner`.`id`)  LEFT OUTER JOIN `user` `creator` ON

(`t`.`creator_id`=`creator`.`id`)  LEFT OUTER JOIN `user` `editor` ON

(`t`.`editor_id`=`editor`.`id`)  LEFT OUTER JOIN `node_has_tag` `tags_tags`

ON (`t`.`id`=`tags_tags`.`node_id`) LEFT OUTER JOIN `tag` `tags` ON

(`tags`.`id`=`tags_tags`.`tag_id`)  WHERE ((`t`.`name` LIKE :criteria) OR

(`t`.`description` LIKE :criteria) OR (`t`.`created_at` LIKE :criteria) OR

(`t`.`last_edit_at` LIKE :criteria) OR (`owner`.`nick` LIKE :criteria) OR

(`creator`.`nick` LIKE :criteria) OR (`editor`.`nick` LIKE :criteria) OR

(`tags`.`name` LIKE :criteria)) ORDER BY tags.name ASC



with correct joins.

Thanks for your help!

Best regards

freemind