Yii - Parameterising a sub-select in SQL Builder

I spent hours trawling through Yii documentation, forums and tutorials to try and find out how to do this relatively simple task.  I couldn't find an answer anywhere and it seems that not many people need this kind of structure. But just in case there is somebody else out there, mad enough to try this -> and if you do, please just drop a comment below to let me know how you got on!

THE SCENARIO

I have developed a holiday reservation system in which a customer has one or more booking-enquiries for a property which has many features. Now I would like to provide a CGRIDVIEW with an Advanced Search form that enables me to query this whole chain. I decided therefore to create a sub-query as follows: SELECT customer.* FROM customer WHERE customer.id in ( SELECT DISTINCT `cs`.`id` FROM `customer` `cs` LEFT JOIN `booking` `bk` ON cs.id = bk.customer_id LEFT JOIN `properties` ON bk.property_id = properties.id LEFT JOIN `vwpropertyalloptions` ON properties.id = vwpropertyalloptions.id WHERE (booking_status='BOOKED') AND (option_id in (1,2,3,4,5)) ) So, note that the Parameters are in the sub-query.  How the heck do you do this simply in Yii??? Basically, I copied the standard Search  function in the Customer model and called it searchAdvanced() I added the search fields for all the other entities as Public properties of the customer model public $asBookingStatus; public $asBookingFrom1; public $asBookingFrom2; In the normal way I then went about populating the standard CDbCriteria object $criteria=new CDbCriteria; $criteria->compare('segment',$this->segment,true); $criteria->compare('temperature',$this->temperature,true); /** Setup Booking Conditions * **/ $criteria->compare('booking_from',$this->asBookingFrom); $criteria->compare('booking_to',$this->asBookingTo); ..... /** Setup Property Conditions **/ .....

Now comes the clever bits.  

Rather than using that CdbCriteria in the usual way, I have used it to handle all the messy bits about using parameters, like strings and arrays, column types, and PDO validation etc... So I grab the WHERE clause and the PARAMS from the CdbCriteria which it has made them all nice and pretty for me. $where=$criteria->condition; $params=$criteria->params;  I then created the SQL for my sub-select using a command object and inserted the where and params from above:- $subSQL=Yii::app()->db->createCommand() ->select('cs.id') ->from('customer cs') ->leftjoin('booking bk', 'cs.id = bk.customer_id') ->leftjoin('properties', 'bk.property_id = properties.id') ->leftjoin('vwpropertyalloptions', 'properties.id = vwpropertyalloptions.id') ->where($where, $params) ->group('cs.id') ->text; This gives me the full SQL that I need to insert into the main query:- $sql="SELECT * FROM customer where id in ($subSQL)"; $dataProvider=new CSqlDataProvider($sql, array( 'params'=>$params, // this is actually adding the parameters to the sub-query 'totalItemCount'=>$count, // see note below 'sort'=>array( 'attributes'=>array( 'firstname','surname','address', ), ), 'pagination'=>array( 'pageSize'=>10, ), )); return $dataProvider; There is one complication. If you want to use the standard Yii pagination, you must pass the CSqlDataProvider object the number of rows that this query returns. Normally, this would be a simple $count=Yii::app()->db->createCommand('Select count(*) from customer')->queryScalar but we need to apply all the Conditions and Parameters as well.  So we need to build a COUNT query before building the CSqlDataProvider using a query like this:- $count = Yii::app()->db->createCommand() ->select('count(DISTINCT cs.id)') ->from('customer cs') ->leftjoin('booking bk', 'cs.id = bk.customer_id') ->leftjoin('properties', 'bk.property_id = properties.id') ->leftjoin('vwpropertyalloptions', 'properties.id = vwpropertyalloptions.id') ->where($where, $params) ->queryScalar(); So the final SearchAdvanced() function looks like this public function searchAdvanced() { $criteria=new CDbCriteria; $criteria->compare('segment',$this->segment,true); $criteria->compare('temperature',$this->temperature,true); /** Setup Booking Conditions * **/ $criteria->compare('booking_from',$this->asBookingFrom); $criteria->compare('booking_to',$this->BookingTo); /** * Setup Property Criteria */ $criteria->compare('sleeps',$this->asSleeps,true); $criteria->compare('bedrooms',$this->asBedrooms,true); /** * Now setup IN conditions */ if ($this->asBookingStatus) $criteria->addInCondition('booking_status',$this->asBookingStatus); if ($this->asFeatures) $criteria->addInCondition('option_id',$this->asFeatures); $where=$criteria->condition; $params=$criteria->params; unset($criteria); $count = Yii::app()->db->createCommand() ->select('count(DISTINCT cs.id)') ->from('customer cs') ->leftjoin('booking bk', 'cs.id = bk.customer_id') ->leftjoin('properties', 'bk.property_id = properties.id') ->leftjoin('vwpropertyalloptions', 'properties.id = vwpropertyalloptions.id') ->where($where, $params) ->queryScalar(); // Annoying but you have to recreate the whole statement // just to change the SELECT clause! $subSQL=Yii::app()->db->createCommand() ->select('cs.id') ->from('customer cs') ->leftjoin('booking bk', 'cs.id = bk.customer_id') ->leftjoin('properties', 'bk.property_id = properties.id') ->leftjoin('vwpropertyalloptions', 'properties.id = vwpropertyalloptions.id') ->where($where, $params) ->group('cs.id') ->text; $sql="SELECT * FROM customer where id in ($subSQL)"; $dataProvider=new CSqlDataProvider($sql, array( 'params'=>$params, 'totalItemCount'=>$count, 'sort'=>array( 'attributes'=>array( 'firstname','surname','address', ), ), 'pagination'=>array( 'pageSize'=>10, ), )); return $dataProvider; }

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 ...


Leave a Comment

twitterfacebookgooglelinkedin https://me.yahoo.com