Adding an additional column as part of CDbCriteria

This was a tough one today and I found the solution in a number of places, but this one was the most helpful.

Problem

I’ve got an Property Rental application which also holds availability by dates for each property.  If you want to search for properties that available (or have some availability) between two dates, I wanted to add a column to the gridview which indicated this.

Solution

I already use additional attributes in my models quite often.  they are so useful for storing additional values or transformations of existing values.  This is the route that I found, thanks to Dana Luther – Web Design & Development on the Yii forums.

Now, my search function in my model is already pretty complex with some INNER joins and some OUTER joins depending on whether various fields are selected in the Advanced Search form on the CGridview.

However, a simple change to some existing code was all it needed.

My existing code was something like this:

 PHP | 
 copy code |
?
 
01
if (!empty($this->from_date) && !empty($this->to_date)  ) {
02
        $ds=CDateTimeParser::parse($this->from_date,'dd/MM/yyyy');
03
        $from=date('Y/m/d',$ds);
04
        $ds=CDateTimeParser::parse($this->to_date,'dd/MM/yyyy');
05
        $to=date('Y/m/d',$ds);
06
 
07
        $sql.=" inner JOIN (SELECT DISTINCT property_id from availability WHERE available_dt BETWEEN '$from' AND '$to' AND availability='A') as A ON A.property_id=t.id" ;
08
}
09
if ($sql!='') $criteria->join=$sql;
10

And a simple change to this as follows:-

 PHP | 
 copy code |
?
 
1
         $sql.=" INNER JOIN (SELECT a.property_id, COUNT(*) AS FreeDays FROM availability a WHERE (a.available_dt BETWEEN '$from' AND '$to' ) AND a.availability='A' GROUP BY a.property_id having count(*)>1) as p3 ON p3.property_id=t.id";
2
         $criteria->select = "t.*, p3.FreeDays as freedays";

Plus define the public variable at the top of the model “Public $freedays;”

The next challenge is how to convert this into a parametrised query and then merge it with the existing CDbcriteria …. any ideas?