I have some troubles with CActiveDataProvider

toli · August 5, 2012, 10:33am

Hello Mates,

I have the following code segment:

    	&#036;dataProvider = new CActiveDataProvider('HotelsCommon',array ( 


        	'criteria' =&gt; array ( 


            	'with'=&gt;'hotels',


            	'together'=&gt;true,


            	'condition'=&gt;&quot;hotels.name like '%:searchstr%'&quot;,


            	'params'=&gt;array(':searchstr' =&gt; &#036;_POST['searchStr']),


            	'order' =&gt; 't.id', 


            	'distinct' =&gt; 't.id',


        	), 


        	'pagination' =&gt; array ( 


            	'PageSize' =&gt; 10, 


        	) 


    	));

It seems to be correct, except that in raw sql ":searchstr" is not replaced with its value. Sql from the log looks like:

Querying SQL: SELECT DISTINCT COUNT(DISTINCT t.id) FROM hotels_common t LEFT OUTER JOIN hotels hotels ON (hotels.hotel_id=t.id) WHERE (hotels.name like ‘%:searchstr%’)

How can I do this? Is there any mistakes in my approach?

abennouna · August 5, 2012, 11:01am

Hi there

It’s normal. You won’t see the actual value but only the placeholder. Why do you think it’s not working? Do you get any error?

For your query, it seems a bit awkward to me What are you trying to get?

toli · August 5, 2012, 12:10pm

So a simple description about my case:

I have 2 tables:

hotels_common - there are many records for hotels

hotels - there are many language specific records for hotels

hotels_common.id ( 1 to many) hotels.hotel_id

hotels.name is a name of a hotel. In this table there could be many records for one and the same hotel.

record 1:

hotels.hotel_id = 7

hotels.lang_id = 1

hotels.name = ‘name of a hotel in english’

record 2:

hotels.hotel_id = 7

hotels.lang_id = 2

hotels.name = ‘name of a hotel in german’

record 2:

hotels.hotel_id = 7

hotels.lang_id = 3

hotels.name = ‘name of a hotel in french’

I want to implement a search query for a part of hotels.names for all languages.

Also I need only the data from hotels_common as a result.

The issue : Every time I got the empty result, even I search for a single letter, which obviously is a part of the name.

abennouna · August 5, 2012, 1:39pm

Ok. In your case, “hotels” returns an array because it’s a HAS_MANY relationship, so you can’t use “hotels.name” in a query afaik (at least at the same time with eager loading / use of ‘together’ property).

To avoid that, you can use the Hotels model, not the HotelsCommon one in order to rely on a BELONGS_TO relationship, e.g.


$dataProvider = new CActiveDataProvider('Hotels',array ( 

    'criteria' => array (

        'with'=>'hotelsCommon', // name of the BELONGS_TO relationship

        'together'=>true,

        'condition'=>"name like '%:searchstr%'",

        'params'=>array(':searchstr' => $_POST['searchStr']),

        'order' => 'hotelsCommon.id', 

        // 'distinct' => 'hotelsCommon.id.id', // you don't need it in this case

    ),

    'pagination' => array ( 

        'PageSize' => 10, 

    )

));

PS Please use code formatting to improve readability. You can use the <> button after selecting your code.

Edit: Ok, I read now "Also I need only the data from hotels_common as a result."

So you can skip the ‘with’ and the ‘together’ properties:


$dataProvider = new CActiveDataProvider('Hotels',array ( 

    'criteria' => array (

        'condition'=>"name like '%:searchstr%'",

        'params'=>array(':searchstr' => $_POST['searchStr']),

        'order' => 'hotel_id', 

    ),

    'pagination' => array ( 

        'PageSize' => 10, 

    )

));

gusarov · August 5, 2012, 1:54pm

[sql]

SELECT

hotels_common.column

FROM

hotels_common

INNER JOIN hotels ON hotels_common.hotel_id = hotels.hotel_id

WHERE

hotels.name like ‘%searchstr%’[/sql]

Try to do things simple

toli · August 5, 2012, 2:06pm

bennouna:

Ok. In your case, “hotels” returns an array because it’s a HAS_MANY relationship, so you can’t use “hotels.name” in a query afaik (at least at the same time with eager loading / use of ‘together’ property).

To avoid that, you can use the Hotels model, not the HotelsCommon one in order to rely on a BELONGS_TO relationship, e.g.
$dataProvider = new CActiveDataProvider('Hotels',array ( 

    'criteria' => array (

        'with'=>'hotelsCommon', // name of the BELONGS_TO relationship

        'together'=>true,

        'condition'=>"name like '%:searchstr%'",

        'params'=>array(':searchstr' => $_POST['searchStr']),

        'order' => 'hotelsCommon.id', 

        // 'distinct' => 'hotelsCommon.id.id', // you don't need it in this case

    ),

    'pagination' => array ( 

        'PageSize' => 10, 

    )

));
PS Please use code formatting to improve readability. You can use the <> button after selecting your code.

Edit: Ok, I read now "Also I need only the data from hotels_common as a result."

So you can skip the ‘with’ and the ‘together’ properties:
$dataProvider = new CActiveDataProvider('Hotels',array ( 

    'criteria' => array (

        'condition'=>"name like '%:searchstr%'",

        'params'=>array(':searchstr' => $_POST['searchStr']),

        'order' => 'hotel_id', 

    ),

    'pagination' => array ( 

        'PageSize' => 10, 

    )

));

The last code segment will give me a records from hotels table not from hotels_common. I need a record from hotels_common.

toli · August 5, 2012, 2:07pm

In general you are right but we are using a framework not raw mysql + php.

abennouna · August 5, 2012, 2:27pm

Ok forget it, and the first one?

gusarov · August 5, 2012, 3:15pm

[font=“Menlo, Consolas, Courier New, Courier, mono”][size=“2”][color="#222222"]Ok, if you need to find the names, and you have BELONGS_TO relation in your model, try to create a new instance of CDbCriteria, then make a compare(‘name’,$model->name,true);[/color][/size][/font]

[font="Menlo, Consolas, Courier New, Courier, mono"][size="2"][color="#222222"]Ok, more detailed:[/color][/size][/font]

[font="Menlo, Consolas, Courier New, Courier, mono"][size="2"][color="#222222"]Your relation in Hotels model:[/color][/size][/font]

[font=“Menlo, Consolas, Courier New, Courier, mono”][size=“2”][color="#222222"]‘hotel’ => array(self::BELONGS_TO, ‘hotels_common’, ‘hotel_id’),[/color][/size][/font]

[color="#222222"][font="Menlo, Consolas,"]Right?[/font][/color]

[color="#222222"][font=“Menlo, Consolas,”]This means, that when you initialize your model with findByPk() (for example), you can access to ‘[/font][/color][color="#222222"][font=“Menlo, Consolas,”]hotels_common[/font][/color] [color="#222222"][font=“Menlo, Consolas,”]’ table data associated with your search key “hotel_id” simply like this - $model->hotel.[/font][/color]

[color="#222222"][font="Menlo, Consolas,"]

[/font][/color]

[font="Menlo, Consolas, Courier New, Courier, mono"][size="2"][color="#222222"]So, simply create new function in your model:[/color][/size][/font]

[font="Menlo, Consolas, Courier New, Courier, mono"] [/font]

[font="Menlo, Consolas, Courier New, Courier, mono"][size="2"][color="#222222"]


public function searchHotelByName()

	{


		$criteria=new CDbCriteria;


		$criteria->compare('name',$this->name,true);


		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

	}

[/color][/size][/font]

[font="Menlo, Consolas, Courier New, Courier, mono"][size="2"][color="#222222"]When you make search, data provider should be $model->[/color][/size][/font][color="#222222"][font="Menlo, Consolas,"]searchHotelByName() and $data->hotel will be your search result. Simple acces to your column will be $data->hotel->column. Try to debug it and you will understand how relations work.[/font][/color]

toli · August 5, 2012, 4:11pm

The first one also does not work.

The strange thing is in the "like" clause. With the following example I do not have any results.




$dataProvider = new CActiveDataProvider('Hotels',array ( 

            	'criteria' => array (

                	'condition'=>"name like '%:searchstr%'",

                	'params'=>array(':searchstr' => $_POST['searchStr']),

            	), 

            	'pagination' => array ( 

                	'PageSize' => 10, 

            	) 

        	));

If i remove the ‘condition’ element from my first example I have results, but if I add it I have empty set again.

I think there is a problem with like in condition element …




        	$dataProvider = new CActiveDataProvider('HotelsCommon',array ( 

            	'criteria' => array (

                	'select' => 'id, hotels.name as name',

                	'with'=>'hotels',

                	'together'=>true,

                	'order' => 't.id', 

                	'distinct' => 't.id',

            	), 

            	'pagination' => array ( 

                	'PageSize' => 10, 

            	) 

        	));

This last part of code works well but without a condition, so I cannot use it … bad for me

I do not know. It looks very strange. So without condition section it works and with it everitime I have empty result.

abennouna · August 5, 2012, 4:23pm

Are you sure your $_POST[‘searchStr’] returns something?

You can also try like this:


$criteria = new CDbCriteria;

$criteria->compare('name', $_POST['searchStr'], true);

$dataProvider = new CActiveDataProvider('Hotels', array(

    'criteria'=>$criteria

));

toli · August 5, 2012, 4:30pm

With next code it works, but the usage is not clear.




$dataProvider = new CActiveDataProvider('HotelsCommon',array ( 

            	'criteria' => array (

                	'select' => 'id, hotels.name as name',

                	'with'=>'hotels',

                	'together'=>true,

                	'condition'=>"hotels.name like '%$_POST[searchStr]%'",

                	'order' => 't.id', 

                	'distinct' => 't.id',

            	), 

            	'pagination' => array ( 

                	'PageSize' => 10, 

            	) 

));

abennouna · August 6, 2012, 12:50am


                        'condition'=>"hotels.name like '%$_POST[searchStr]%'",

Honestly speaking, I doubt it could ever work

Edit: Sorry if I was unclear, but I was speaking about the line above, specifically, because you can’t have searchStr like that, it’s neither a variable nor a string, so it can’t be an index of the $_POST array

toli · August 6, 2012, 7:58am

I asked one of my friend, who is a Yii guru, and he advised me to use it as the following example:





'condition'=>"name like ':searchstr'",

'params'=>array(':searchstr' => '%'.$_POST['searchStr']).'%',

I am going to test it later today.

I think it should work so the %% have to be in the params not in the condition.

toli · August 7, 2012, 11:49am

Finally :





$dataProvider = new CActiveDataProvider('HotelsCommon',array ( 

    'criteria' => array ( 

        'with'=>'hotels',

        'together'=>true,

        'condition'=>"hotels.name like :searchstr",

        'params'=>array(':searchstr' => '%'.$_POST['searchStr'].'%'),

        'order' => 't.id', 

        'distinct' => 't.id',

    ), 

    'pagination' => array ( 

        'PageSize' => 10, 

    ) 

));

Ta Da

abennouna · August 7, 2012, 12:11pm

Oh great. I think I’ve just learnt something new today !

Thanks for bringing your issue onboard