CGridView - latest item of a HAS_MANY relationship

Yii 1.1 General Discussion
1

I have 2 tables, say, Parent and Status. Parent HAS_MANY Statuses. When displaying in CGridView, I want to display only the latest Status (there is a status_time) in the table.

Status has a status_id and a virtual attribute getStatusText that I use to display the status text.

In the Parent, I have a getCurrentStatus to return the latest status entry, and then i use currentstatus->statusText.

I want to show the statusText in the CGridView and have it be filterable. I’ve tried to follow 2 or 3 tutorials and really haven’t gotten the solution. Has anyone done this and can show a simple generic way to do this? I’m pretty confused. I imagine someone has done this before. I don’t want all the statuses, just the most current.

TIA

Judging by all the CGridView posts in here - it seems like it’s the toughest, most finicky basic part of Yii…

2

Couple things.

You need to eager load it, not lazy load. So you need ‘with’ relation and together=true.

Also, since you need to have $criteria->compare you need a virtual attribute.

Sounds like your select query needs to have max(related_field) as a column.

3

Is there a way to limit it to one status, just the max one? I’m having trouble doing that. I don’t need all the statuses, just the maximum one. I feel like someone must have had this before, there should be a standard way to do this but I can’t seem to figure it out.

4

in you criteria you setup the limit


$criteria->limit = 1

for use find instead of findAll

5

I ended up going another way, I couldn’t get it to work well.

I ended up adding a new relation to the model


'maxStatus'=> array(self::HAS_ONE,'ProtocolStatus','protocol_id','alias'=>'i',

                                        'select'=>'*',

                                        'join'=>'INNER JOIN (select max(status_time) as maxtime from ppc_dev.tbl_protocol_status group by protocol_id) j ON i.status_time=j.maxtime',                 ),

I was able to get that all to work.

6

thats not bad at least you got it working