Related Search Behavior

[le_top (Ext Yiiframework Com)]

Hi

I got an idea for extra functionnality in this extension which is to support[size="2"] CDBExpressions similar to this:[/size]

        array(

                'criteria'=>array(

                        'select'=>array(

                                'DATEDIFF(t.date_expires, CURDATE()) AS datediff',

                           	),

                ),

        )

)

The field name would be the expression’s alias, a new ‘with’ option would allow us to indicate the tables used, an ‘expression’ option would indicate the CDbExpression.

Any field of this type would also be added automatically to the fetched fields (when searched and/or with KeenDataProvider).

[petit_point]

Have you tried it with a Postgres Database ?

Because i did, for a long time but unfortunatly,

I have always errors like

SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "relationName"

I changed your code by adding some "" to relation names and a different way to search :

From : $column="$shortrelation.$column";

To   : $column='"'.$shortrelation.'"."'.$column.'"';


From : $criteria->compare($column,$search_value,true);

To   : $criteria->addSearchCondition($column,$search_value, true, 'AND', 'ILIKE');

but it’s still not working …

Have you an idea ?

[rasoul_par]

hi

I have a problem with this ext when I’m trying to run your attached sample, I face this error:

PHP Error

Description

Object of class Invoiceline could not be converted to string

Source File

C:\wamp\www\Yii\framework\web\CDataProvider.php(62)

00050: {

00051: $this->_id=$value;

00052: }

00053:

00054: /**

00055: * @return CPagination the pagination object. If this is false, it means the pagination is disabled.

00056: */

00057: public function getPagination()

00058: {

00059: if($this->_pagination===null)

00060: {

00061: $this->_pagination=new CPagination;

00062: $this->_pagination->pageVar=$this->getId().’_page’;

00063: }

00064: return $this->_pagination;

00065: }

00066:

00067: /**

00068: * @param mixed the pagination to be used by this data provider. This could be a {@link CPagination} object

00069: * or an array used to configure the pagination object. If this is false, it means the pagination should be disabled.

00070: */

00071: public function setPagination($value)

00072: {

00073: if(is_array($value))

00074: {

Stack Trace

#0 C:\wamp\www\Yii\framework\web\CDataProvider.php(75): KeenActiveDataProvider->getPagination()

#1 C:\wamp\www\Yii\framework\base\CComponent.php(152): KeenActiveDataProvider->setPagination()

#2 C:\wamp\www\Yii\framework\web\CActiveDataProvider.php(56): KeenActiveDataProvider->__set()

#3 C:\wamp\www\relatedsearchbehavior\protected\extensions\KeenActiveDataProvider.php(65): KeenActiveDataProvider->__construct()

#4 C:\wamp\www\relatedsearchbehavior\protected\extensions\RelatedSearchBehavior.php(179): KeenActiveDataProvider->__construct()

#5 unknown(0): RelatedSearchBehavior->relatedSearch()

#6 C:\wamp\www\Yii\framework\base\CComponent.php(234): call_user_func_array()

#7 C:\wamp\www\Yii\framework\db\ar\CActiveRecord.php(192): Invoiceline->__call()

#8 C:\wamp\www\relatedsearchbehavior\protected\models\Invoiceline.php(137): Invoiceline->__call()

#9 C:\wamp\www\relatedsearchbehavior\protected\models\Invoiceline.php(137): Invoiceline->relatedSearch()

#10 C:\wamp\www\relatedsearchbehavior\protected\views\site\index.php(15): Invoiceline->search()

#11 C:\wamp\www\Yii\framework\web\CBaseController.php(119): require()

#12 C:\wamp\www\Yii\framework\web\CBaseController.php(88): SiteController->renderInternal()

#13 C:\wamp\www\Yii\framework\web\CController.php(748): SiteController->renderFile()

#14 C:\wamp\www\Yii\framework\web\CController.php(687): SiteController->renderPartial()

#15 C:\wamp\www\relatedsearchbehavior\protected\controllers\SiteController.php( 8 ) : SiteController->render()

#16 C:\wamp\www\Yii\framework\web\actions\CInlineAction.php(32): SiteController->actionIndex()

#17 C:\wamp\www\Yii\framework\web\CController.php(300): CInlineAction->run()

#18 C:\wamp\www\Yii\framework\web\CController.php(278): SiteController->runAction()

#19 C:\wamp\www\Yii\framework\web\CController.php(257): SiteController->runActionWithFilters()

#20 C:\wamp\www\Yii\framework\web\CWebApplication.php(320): SiteController->run()

#21 C:\wamp\www\Yii\framework\web\CWebApplication.php(120): CWebApplication->runController()

#22 C:\wamp\www\Yii\framework\base\CApplication.php(135): CWebApplication->processRequest()

#23 C:\wamp\www\relatedsearchbehavior\index.php(13): CWebApplication->run()

[le_top (Ext Yiiframework Com)]

Hi

I have Yii 1.1.12; I can’t see a constructer at

[color=#1C2837][size=3]C:\wamp\www\Yii\framework\web\CActiveDataProvider.php(56): KeenActiveDataProvider->__set().[/size][/color]

[color=#1C2837][size=3]

[/size][/color]

[color=#1C2837][size=3]In your case the ‘getId’ returns the object I’ld say.[/size][/color]

[color=#1C2837][size=3]In Yii 1.1.12, the getPagination code looks like this:[/size][/color]

[color=#1C2837][size=3]

	public function getPagination()

	{

		if($this->_pagination===null)

		{

			$this->_pagination=new CPagination;

			if(($id=$this->getId())!='')

				$this->_pagination->pageVar=$id.'_page';

		}

		return $this->_pagination;

	}

Surely we can identify what does not work - I think by identifying what ‘getId’ corresponds to in your version.[/size][/color]

[color=#1C2837][size=3]

[/size][/color]

[color=#1C2837][size=3]

[/size][/color]

[rasoul_par]

hi le_top

I’m so sorry, it was my fault. I included an old (ancient ) version of Yii!

thanks for your reply!

[le_top (Ext Yiiframework Com)]

Here is a way to use the ‘autoScope’ feature now available in RelatedSearchBehavior.

AutoScope automagically adds scopes to your CActiveRecord for each field. If you have a field ‘author’ then you’ll get a scope like this for free:

public function author(mixed $value, boolean $partialMatch=false, string $operator='AND', boolean $escape=true)

Why do you need this: well, to speed up development and avoid writing ‘condition’.

Autoscope provides:

• Automatic specification of the table alias for the field;

• Same functionnality as provided by CDbCriteria::compare();

• Runtime checking that the used field/scope exists (instead of getting a failed SQL command);

• The opportunity to write less code.

How do I use this?

To understand, have a look at the "manual" scope definition below where I[size=2]select Devices with a given protocol.[/size]

[size=2]It requires that the CActiveRecord referenced in the relation ‘deviceType’ also has a scope called ‘device_type_protocol’:[/size]

class Device extends CActiveRecord {

/** Scope to select devices with specific protocol. */

public function protocol($protocol) {

        $this->getDbCriteria()->mergeWith(

                array(

                        'with'=>array(

                                'deviceType'=>array(

                                        'scopes'=>array('device_type_protocol'=>$protocol),

                                ),

                        ),

                )

        );

        return $this;

    }

}

In the case where the scope ‘DeviceType::device_type_protocol’ does not exist, I’ld have to use ‘condition’ to filter on it:

class Device extends CActiveRecord {

/** Scope to select devices with specific protocol. */

public function protocol($protocol) {

        $this->getDbCriteria()->mergeWith(

                array(

                        'with'=>array(

                                'deviceType'=>array(

                                      'condition'=>"deviceType.device_type_protocol=$protocol",

                                ),[

                        ),

                )

        );

        return $this;

    }

}

That is the code when I am ‘lazy’: no quoting for the alias, duplication of the alias name (‘deviceType’ is written twice), non escaping or parameter for ‘$protocol’. And to do better, I’ld have to create a lot of stupid scopes.

Too many things I do not like, so I developed ‘autoScope’ which provides the scope ‘device_type_protocol()’ automagically.

Unfortunately, I could not put everything in the extension, and you must extend the ‘__call’ method of the ActiveRecord. I’ve automated that by updating the ‘Gii’ generator template for the model.

class DeviceType extends CActiveRecord {


	public function __call($name,$parameters) {

    	try {

        	return parent::__call($name,$parameters);

    	} catch (CException $e) {

        	if(preg_match('/ and its behaviors do not have a method or closure named /',$e->getMessage())) {

                 return $this->autoScope($name, $parameters);

        	} else {

            	throw $e;

        	}

    	}

	}

}

If you look closer you’ll see that there is a call to ‘$this->autoScope()’. Yii will not find ‘autoScope’ in the model and will go look for it in the behaviors and should find it in the the RelatedSearchBehavior.

AutoScope checks if the attribute exists and if it does applies a scope on it using ‘compare’ as the method, here is the line of code to the call of ‘compare’ in autoScope:

[size=2]           $owner->getDbCriteria()->compare($column, $value,$partialMatch,$operator,$escape);[/size]

[kasper.j]

Your extension saves so much time, i really appreciate your work.

Is it possible for you to add the possibility to order by 2 or more columns by default,

or do you know another way to get this to work properly?

[le_top (Ext Yiiframework Com)]

Clearly a great timesaver and cleaner code resulting from it.

There is another way, but then why would there be the extension?

I updated the extension to add support for multiple columns. I also updated the demo to use multiple columns.

[kasper.j]

Wow that was fast

Thank you very much!

[le_top (Ext Yiiframework Com)]

In reply to:

i wanted to know if anybody facing issues with pagination where the data rows stay the same if you search using one of the related fields or sort by one of them and i noticed also that the indicators of the current page are changing but the data staying the same

everything works normally if i search using the fields of the same model i’m using crisu bootstrap grid and

and

…anyway i found out that the the three order by clauses in the query (the outer and inner data sets query)that pick up the data are missing and i have no idea why …

There is only one order by clause at a time because only one column is sorted at a time.

The sort order is determined by a get variable which is typically ‘sort’:

RESTOFURL&sort=start_date

The above example would sort by ‘start_date’. ‘start_date’ must correspond to the virtual attribute if it is a column in a relation for ‘RelatedSearchBehavior’ to work. And of course the column should be sortable according to the grid view (but I guess that this is the case).

Check the URL that is called when you try to do a sort - an easy way to do so is ‘click right’/‘open link in new window’ - the Url will appear in the navigator’s location bar and you can even manually play with it.

Example with the demo: http://relatedsearchbehavior.ynamics.com/index.php?r=site/index&Invoiceline_page=7&ajax=yw0&sort=SupportEmail.desc

[kasper.j]

How to setup a MANY MANY relation?

If set it up the same way, as with simple relations there is no data in the column and no error

[le_top (Ext Yiiframework Com)]

I have not really thought of many-many relations and it is a topic the regularly pops up in the forum.

Could you setup a demo for that which could be integrated in the demo for this extension and which can at the same time be used as a test case to check what the solution is?

[kasper.j]

I am thinking about how to integrate it into your demo,

but i currently have no idea how to do it on a sensemaking basis.

In my application I have these 3 tables

User

PK - [color="#FF0000"]id[/color]

AuthAssignment

PK1,[color="#0000FF"]FK1[/color] - itemname

PK2,[color="#FF0000"]FK2[/color] - userid

AuthItem

PK - [color="#0000FF"]name[/color]

I want to display the name of the assigned item in my User gridview

[kasper.j]

'roles' => array(self::HAS_ONE,'AuthAssignment','userid'),

'items' => array(self::HAS_ONE,'AuthItem',array('itemname'=>'name'),through'=>'roles')

'role' => 'items.description',

It works, just a workaround for MANY MANY relation

[le_top (Ext Yiiframework Com)]

Hi

To set up within the demo, you can see that a track can appear on more than one invoice and that an invoice can have more than one Track.

I added the following relations the Track (untested):

   'invoice_lines'=>array(self::HAS_MANY,'InvoiceLine',array('TrackId'=>'TrackId')),

   'invoices'=>array(self::HAS_MANY,'Invoice',array('InvoiceId'=>'InvoiceId'),'through'=>'invoice_lines'),

However, these relations do not seem usefull for your purpose.

The demo uses ‘InvoiceLines’ as the base model class (equivalent to AuthAssignment) and therefore any existing combination of Track/Invoice appears in the table.

Hence, in a way, the demo already demonstrates how you can use ‘many_many’.

Knowning this, you might be able to demonstrate through the demo the situation where you said that there is no data in the column and no error.

[Johannn (Johanvanmeel)]

First of all, great extension, I love it

But I seems to have a problem with the autoscope.

Among other things I added the code below to my model, I also adjust to search function as mentioned in the install guide.

public function __call($name,$parameters) {

		try {

			return parent::__call($name,$parameters);

		} catch (CException $e) {

			if(preg_match('/ en zijn behaviors hebben geen method of closure met naam /',$e->getMessage())) {

				return $this->autoScope($name, $parameters);

			} else {

				throw $e;

			}

		}

	}

	

	function behaviors() 

	{

		return array(

			'relatedsearch'=>array(

				 'class'=>'RelatedSearchBehavior',

				 'relations'=>array(

					  'appProcesId'=>'applicationProces.proces_id',

				 ),

			 ),

		);

	}

In a gridview searching and displaying the ‘appProcesId’ works great. But when I want to do something like this:

ApplicationProcesActivity::model()->appProcesId(2)->findAll();

I get an error:

Call to a member function findAll() on a non-object

While:

ApplicationProcesActivity::model()->id(40)->findAll();

works fine. the autoscope does work, but only with properties from that model, not the ones defined in the behaviour. Am I doing something wrong?

[le_top (Ext Yiiframework Com)]

Hi Johannn

Autoscope is not currently implemented for related fields.

It is possible to do so and would require either duplicating some of the code or refactoring it to avoid code duplication.

Basically, the code of the ‘relatedSearch’ method is needed without the ‘sort’ logic and dataProvider creation - we just need to update the criteria.

For performance, the analysis of the relations would better be cached, but to start we could ignore caching.

The autoScope method then has to call on this new method in case the field does not exist as a regular field of the table, but when it exists as a relation.

I do not have much time currently, so on my end this implementation has to wait.

You can still do something like indicated in the documentation (I haven’t checked the code precisely, but it gives an idea):

...->findAll(array(

'with'=>array('applicationProcess'=>array('scopes'=>array('id'=>2))))

I notice that there also seems to be an internationalisation issue which I hadn’t notices (even though my application language is in French).

Which is logical because there is no translation for french for this message.

To resolve that, we have to modify _call:

/**

     * Add automatic scopes for attributes (uses RelatedSearchBehavior).

     */

	public function __call($name,$parameters) {

    	try {

        	return parent::__call($name,$parameters);

    	} catch (CException $e) {

        	if(preg_match(

                	'/'.Yii::t(

                        	'yii',

                        	quotemeta(

                                	Yii::t(

                                        	'yii',

                                        	'{class} and its behaviors do not have a method or closure named "{name}".'

                                        	)

                                	),

                                	array('{class}'=>'.*','{name}'=>'.*')

                        	)

                	.'/',$e->getMessage())) {

            	return $this->autoScope($name, $parameters);

        	} else {

            	throw $e;

        	}

    	}

P.S: (in Dutch): nog veel plezier met de extensie!

[Johannn (Johanvanmeel)]

Thank you for the quick reply.

I’ll use the solution provided in the documentation by using the scope in the findAll()

Again thanks for this great extension

[gb5256 (Write2christian)]

Hello out there,

I do use this extension quite a lot, but I run right now into an incompatibility with another extension.

Perhaps someone solved this already…

I am using the "yii-remember-filters-gridview" component which stores the filter, the pagination and the sorting in a session. When combing back to the grid (for example after updating one record) all three above mentioned parameters are active again. Nice.

But… as there is quite some logic in RelatedSearchBehaviour about Sorting, it breaks the function of the other extension. Does anybody see a way to solve this?

The "yii-remember-filters-gridview" is a small component, so I pasted the function for the storage of sorting in the session below.

Any ideas how to solve this? Could we add a check for the session into RSB?

* http://www.yiiframework.com/extension/remember-filters-gridview

*/




    private function doReadSave() {

     if ($this->owner->scenario == 'search' || $this->owner->scenario == $this->rememberScenario ) {

        $this->owner->unsetAttributes();


        // store also sorting order

        $key = get_class($this->owner).'_sort';

        if(!empty($_GET[$key])){

          Yii::app()->user->setState($this->getStatePrefix() . 'sort', $_GET[$key]);

        }else {

          $val = Yii::app()->user->getState($this->getStatePrefix() . 'sort');

          if(!empty($val))

            $_GET[$key] = $val;

        }

.......

        

[le_top (Ext Yiiframework Com)]

There is actually a function in Yii for it (http://www.yiiframework.com/doc/api/1.1/CGridView#enableHistory-detail ) with limitations.

HOwever, I am using the same[size=2] ‘ERememberFiltersBehavior’ which I add automatically in my models using gii. [/size]

To fix the other extension, we need to consider that the sort key is in ‘sortVar’ . You are correct: sorting does not work correctly. I don’t see it at first sight - I think that RSB is doing it the right way, and the way RFB works needs some investigation.