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:


if (!empty($this->from_date) && !empty($this->to_date)  ) {
        $ds=CDateTimeParser::parse($this->from_date,'dd/MM/yyyy');
        $from=date('Y/m/d',$ds);
        $ds=CDateTimeParser::parse($this->to_date,'dd/MM/yyyy');
        $to=date('Y/m/d',$ds);

        $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" ;
}
if ($sql!='') $criteria->join=$sql;

And a simple change to this as follows:-


         $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";
         $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?


Did you know you can hire me?

I take on projects of all sizes. From Consulting to large Development Projects.

If you're starting a new Yii project and would like some help to get setup and running or you need some help with a particular module or you just need someone to develop the whole dang thing, then just ask ...


Jumpstart Yii2 ... Coming Soon!

I'm in the process of planning a new course on Yii2 and want your feedback.

What is your preferred format? What did you think of my Yii 1.x video course "Beginning Yii"?

What would you like to see different?

How much would you pay?

Leave a Comment

twitterfacebookgooglelinkedin https://me.yahoo.com