Filtering Related Model Attribute In Grid, With, Together

Hi Guys,

I’m a little bit confused now about filtering in grid for related model’s attribute. I’ve found a few “solutions” in different topics (for example here), but it’s not really clear for me.

so there is with, and together functionality. it’s very good, but it makes no sense to do it if in grid view $data->something is used. because this way, there will be lazy loading anyway (right?). if in grid I do (used together with “with” and “together” of course):




'columns' => array(

        array(

            'name' => 'relationName.field',



there is no lazy loading, but there is no filtering capability either.

at the moment, it’s enough for me to only show related model’s representingColumn, as it is so by default. but is there a way at the moment to avoid lazy loading and keep filtering functionality at the same time? or am I missing something?

thanks a lot!

no. if you use ‘with’ there will be no lazy loading when accessing related data this way. ‘with’ causes Yii to load main and related data simultaneously in one query. ‘together’ is used when relation is of HAS_MANY or MANY_MANY type and also causes such data to be loaded in one query (without ‘together’ such data will be loaded in second query and merged with main data - but there will be still no lazy loading when you access such data from code).

thank you very much. I see now, I was wrong, you are right.

Why is “with” not generated by default? It would be better in almost all cases, wouldn’t it?

thanks a lot!

‘with’ causes more difficult queries to run and you do not always need all related data. So it is up to you to choose whether you want lazy loading (on demand) or eager loading using ‘with’

So, there are virtually 3 ways of loading.

[1] Single-query Eager Loading … just one query

[2] Double-query Eager Loading … 2 queries (one for the main objects and the other for the related objects)

[3] Lazy Loading … 1 + N (at max) queries (the 1st one for main objects and other N queries for the related objects)

If "with" is not specified, then the loading will be lazy.

If “with” is specified it will be eager … but sometimes it’s single-query and sometimes double-query. The default is single-query, but when “OFFSET” or “LIMIT” is applied to the main objects and the relation is HAS_MANY or MANY_MANY, then the double-query will be used.

"together" will force the single-query eager loading.

And there’s an important notice:

  • You can not filter or order by the columns of the related table with the double-query eager loading.

  • You may not get the correct count of main objects with the single-query eager loading.

Phew! ;)

It took me quite a long time to comprehend this.

I really appreciate your efforts, thank you so much!

What can we do? I think it would be self explanatory to be able to filter related attributes. The lack of this ability is kinda killer.

Is there anything else I can do? Custom query? Custom filtering functionality?

Or do I have to change database design?

Hey, wait a second, it’s a BELONGS_TO relation in my models, so theoretically I should be able to filter related fields?

I wrote a wiki on the issue, trying to explain the problem, why it has to be like that, and also trying to show some possible techniques to overcome it.

http://www.yiiframework.com/wiki/428/drills-search-by-a-has_many-relation/

Yes, you are. When it’s a BELONGS_TO or a HAS_ONE relation, you can always do safely the single query eager loading quite easily.

There’s a good wiki on that written by redguy.

http://www.yiiframework.com/wiki/281/searching-and-sorting-by-related-model-in-cgridview

[EDIT]

Ah, sorry. You have already read it before the 1st post.

Once I have tried to make it based on this wiki, unfortunately it was absolutely not working for me, I couldn’t figure out why. I’m not sure right now is it only for filtering one single corresponding field or any other fields.

I don’t really understand why is this not working like so:

I have:

  • defined in model one of the related models’ attributes:



public $relatedAttribute;



  • put into search() function the following:



$criteria->compare('relation.relatedAttribute',$this->relatedAttribute, true);



  • put into rules():



array('... relation.relatedAttribute', 'safe', 'on' => 'search'),



or




array('... relatedAttribute', 'safe', 'on' => 'search'),



no change in ‘not-working’ state.

it should work like so, shouldn’t it?

this is the only thing I haven’t put into my code:




return new CActiveDataProvider( 'Post', array(

    'criteria'=>$criteria,

    'sort'=>array(

        'attributes'=>array(

            'author_search'=>array(

                'asc'=>'author.username',

                'desc'=>'author.username DESC',

            ),

            '*',

        ),

    ),

));



because I thought I don’t need this sorting capability. or do I have to put it there anyway?

once I could make it based on Hollyii’s Yii Blog article, but I can’t find right now which model it was…

I don’t know why, but there was even absolutely no textfield in the header of the grid, I thought I have to put there one:




array(

            'name' => 'relation.relatedAttribute',

            'header' => 'relatedAttribute',

            'value' => '$data->relation->relatedAttribute',

            'filter' => CHtml::textField('relatedAttribute'),

        ),



but still not working.

can the problem be that I’m using bootstrap? but I could make it once and it was also bootstrap…

now, if I do this:




array(

            'name' => 'relatedAttribute', ...

        ),



the textfield appears on the top of the grid. but still no filtering.

it works!!!




array('... , relatedAttribute', 'safe', 'on' => 'search'),



it’s not allowed to put ‘relation.’ in front of variable in grid and in relations.

I never used the former format. Is it a valid syntax? I don’t know.

I don’t think you need it when you don’t need the sorting on it.

Ah, it should be like this according to the wiki:




array(

            'name' => 'relatedAttribute',

            'value' => '$data->relation->relatedAttribute',

     ),



I absolutely don’t know what the heck I was doing. Now it’s completely the same as in the wiki and it works. I guess there are slightly different solutions, and maybe this was confusing me, I had too many things in my head. The interesting thing is, that in Hollyii’s Blog, it is like so:




array('name, street, street2, city, state.name', 'safe', 'on'=>'search')



and this should not be like so, but interestingly, despite this, I could somehow make it based on her article. Maybe I was doing a little bit different thing…

Anyway, thanks a lot for your help, I’m glad you came. You are great!

Thanks again!

BR

c

Hi,

I have a small issue now. With basic textfield filtering situation it’s working, but we have to select as a dropdown. so I have put this into the grid columns:




'filter' => GxHtml::listDataEx(Order::model()->findAllAttributes(null, true,

array('order' => 'CAST(ordernr AS SIGNED)')), 'ordernr', 'ordernr'),



it works very good at the first 6 columns, but after that, the dropdowns are empty :blink:

how can it be? it should work. is there a limitation, how many static model queries are allowed in a view?

thanks.

BR

c

I suggest that you look into my ‘RelatedSearchBehavior extension’.

Much of the headaches encountered above are ‘gone’ with that extension. Maybe you’ll get a few other issues, but once you start using it you’ll likely use it more and more.

Yes, thanks, I have already seen your extension, but I have to use drop-downs for filtering. Or is this feature available since then?

Thanks,

BR

c

Update: I have optimized a bit in the code lines:




'filter' => GxHtml::listDataEx(Order::model()->findAllAttributes('ordernr', false,

array('order' => 'CAST(ordernr AS SIGNED)')), 'ordernr', 'ordernr'),



because in fact I don’t need all attributes in all dropdowns at all, and it’s only a text search so I don’t need pk also, and now all dropdowns are rendered correctly.

It seems it was a little bit too much for Yii.

Thanks.

BR

c

Hi

The first part of this thread is about setting up the search which is handled in that extensions.

The drop down filtering is complementary, it is not handled by the extension, but it is compatible.