Sorting On Text Values Obtained From Lookup Model?

Yii 1.1 General Discussion
1

This post details how a Lookup Model can be created to centrally maintain a set of code-to-text translations.

My question is when using CListView or CGridView to sort data in models that make use of the Lookup Model, how do you go about sorting the results based on the looked-up text representation rather than the original integer code?

Hopefully someone can offer some guidance.

2

Hi AndrewM

The ordering is based on the data (for example in database). In Which column do you want to achieved the order? If you want to sorting with other way (after of loading) you may have to do that manually

3

you need to create dictionary table in database which map your integer codes to string representations and then use this manual: http://www.yiiframework.com/wiki/281/searching-and-sorting-by-related-model-in-cgridview/

to apply sorting.

4

That’s provided by the Lookup model in the link I gave

I’d seen that but couldn’t see how to make it work in this case.

The trouble as I see it is caused by the fact that the Lookup model handles code/string lookups for multiple attributes in multiple other data models. I’m sure this is a common technique - I know I’ve used it many times in the past in other environments. The trouble is when using Yii I’ve not found a way to express the relationship between the two models via the “relations” mechanism. So therefore I can’t make use of the linked article.

So maybe a better question for me to ask to ask would be how to add a relation between any old data model and the Lookup model in the OP?

5

Hi I dont know about the look up model have not used it, but I have used this trick quite few time it might help

in your CGridView you can do somthing following


<?php 

    $this->widget('zii.widgets.grid.CGridView', array(

    'id'=>'menu-grid',

    'dataProvider'=>$model->search(),

    'filter'=>$model,

    'columns'=>array(

        'id',

        'page',

         'content'

        array(

            'name'=>'Active',

            'header'=>'Active',

            'filter'=>array('1'=>'Yes','0'=>'No'),

            'value'=>'($data->active=="1")?("Yes")<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/sad.gif' class='bbc_emoticon' alt=':(' />"No")'

        ),

        array(

            'class'=>'CButtonColumn',

        ),

    ),

)); ?>

NOTE:

  • In my pages table i have a column active type Boolean which is either 1 or 0

  • Active = 1 and InActive = 0

6

so please describe your data model… in basic case dictionaries are pretty simple to define as AR relations:




table (

 id PK,

 dict_id int FK

)

dict (

 id PK,

 name string

)

for those tables you have two models: TableModel and DictModel. In TableModel you simply add relation like this:


'dict'=>array( self::BELONGS_TO, 'DictModel', 'dict_id' ),

then you can access string representation with:


$model->dict->name; //$model is instance of TableModel

and then you can follow instructions from the "searching and sorting" manual.

7

I agree that would be a trivial usage.

I’m using something similar to the model described here in one of the Yii tutotials. The Lookup table (Dictionary in your verbiage) contains a number of code/name pairs grouped by type:

  • id: the primary key of the lookup item. Only used for Lookup table maintenance

  • name: the textual representation of the data item that is to be displayed to end users;

  • code: the integer representation of the data item;

  • type: the type of the data item;

  • position: the relative display order of the data item among other items of the same type.

For this to be used in a particular data model, one or more attributes in the model would have a type defined in the Lookup model. Retrieving all code/name pairs for a given type would allow dropdown lists to be populated (using the order specified by the position attribute in the Lookup table). Looking up a particular code of a specific type would enable the textual value (name) to be retrieved.

So I think the main difference (and complexity) over the scenario you presented is that in your case the code is actually the id and will be unique across the entire system. In the above Lookup model, the code is only unique for all rows that have the same type; this provides much more flexibility in the use of the code values. But my inability to specify this relationship in the relations array is proving a problem.

Thanks for your consideration on this.

8

so why can’t you just add ‘type’ criteria in relation definition?




'dict'=>array( self::BELONGS_TO, 'DictModel', 'dict_id', 'on'=>'dict.type = :dtype', 'params'=>array( ':dtype'=>'type1' ) ),
9

Because the primary key in the Lookup model does not form part of the relationship - only the code & type fields do.

I’ve actually got a solution now, the key to which was derived from this thread that shows that the ForeignKey entry is not actually needed when specifying relations - that was news to me.

So my relations entry becomes effectively:




'fieldLookup'=>array( self::BELONGS_TO, 'Lookup', '', 'on'=>'fieldLookup.code=t.field', 'condition'=>"fieldLookup.type='type1'" ),

This does require eager loading of the Lookup model, though. So I modified the search() method in the model to include a with criteria and added a sort specification to the CActiveDataProvider constructor:




	$criteria->with = 'fieldLookup';


	return new CActiveDataProvider($this, array(

	    'criteria'=>$criteria,


            'sort'=>array(

                'attributes'=>array(

                    'field_search'=>array(

                        'asc'=>'fieldLookup.value',

                        'desc'=>'fieldLookup.value DESC',

                    ),

                    '*',

                ),

            ),

	));

This works perfectly, though I’ll look at refining it.

Thanks for your help.

  • Andrew