criteria a little tricky with relation

Hello everyone,

I have a little problem, I hope that someone can help me.

This is 2 of my tables:

[sql]table consult(

id_consult


date_limit

)

table lots(

id_lot


id_consult


date_end

)[/sql]

My consult can have many Lots, but the consult end as soon as at least one of the date_end is reached

I have a Cgridview with my consults which display in one of the columns the most recent date_end

I try to add an search date rangepicker (I already have done this for date_limit but that was much easier)

For date_limit I used something like that :


if (isset($this->date_limit) && $this->date_limit != '')

{

                    $search_date_limit = explode(' - ', $this->date_limit);

		   $criteria->condition="date_limit BETWEEN '".$search_date_limit[0]."' AND '".$search_date_limit[1]."'";

}

I also know how to search a field in a has_many relation but for date_end I have to compare only the first (most recent) date_end and it’s where I’m lost.

Any idea?

Thank you for your time.

You can define a virtual attribute named "last_date_end" to your Consult model.




public $last_date_end;



And you can populate it with the following sub query:




(select max(date_end) from lots where lots.id_consult = t.id_consult) as last_date_end



Now you can search and sort by "last_date_end".

Please take a look at the following wiki. The wiki is for the count of the related model, but the idea is applicable to your problem. You may easily modify the code to handle the max value in the related model.

http://www.yiiframework.com/wiki/319/searching-and-sorting-by-count-of-related-items-in-cgridview

Hi softark,

It seems to work, thank you very much for your help (I’ve spend so many time to try to understand how to resolve my problem…)

Laurent