A quick tip when using Views to simplify Model requests

Building Complex Queries with the ACtiveRecord Object

It's very easy to expect the Active Record object to do everything we need, to accomplish complex queries and make everything easy... This is often an over-rated past-time!

You could use the command object and write your complex SQL by hand - maybe something like this ...

$Sql="SELECT
  reservations.id                          AS id,
  reservations.reservation_ref                 AS reservation_ref,
  reservations.reservation_status              AS reservation_status,
  ...
  properties.currency                 AS currency,
  reservations.owner_deposit               AS owner_deposit,
  reservations.owner_balance               AS owner_balance,
  reservations.last_updated                AS last_updated,
  reservations.created_dt                  AS created_dt,
  vwreservationhistorymax.max_action_date AS max_action_date
FROM (((reservations
     LEFT JOIN customers
       ON ((reservations.customer_id = customer.id)))
    LEFT JOIN properties
      ON ((reservations.property_id = properties.id)))
   LEFT JOIN vwreservationhistorymax
     ON ((reservations.id = vwreservationhistorymax.reservation_id)))";
      $command =Yii::app()->db->createCommand($Sql);
      $res=$command->findAll();
      

Using Views

With Yii it's easy to create a view in our database and then, using Gii, generate a model for this view

CREATE
    /*[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]*/
    VIEW `vwReservations` 
      AS
    (SELECT
      reservations.id                          AS id,
      reservations.reservation_ref                 AS reservation_ref,
      reservations.reservation_status              AS reservation_status,
      ...
      properties.currency                 AS currency,
      reservations.owner_deposit               AS owner_deposit,
      reservations.owner_balance               AS owner_balance,
      reservations.last_updated                AS last_updated,
      reservations.created_dt                  AS created_dt,
      vwreservationhistorymax.max_action_date AS max_action_date
    FROM (((reservations
       LEFT JOIN customers
         ON ((reservations.customer_id = customer.id)))
      LEFT JOIN properties
        ON ((reservations.property_id = properties.id)))
       LEFT JOIN vwreservationhistorymax
       ON ((reservations.id = vwreservationhistorymax.reservation_id))));
  

Model generstor: vwResrervations

<!--?php
    /**
     * This is the model class for table "vwReservations".
     *
     * The followings are the available columns in table 'vwReservations':
     * @property integer $id
     * @property string $reservation_ref
     * @property string $reservation_status
     * @property string $currency
     * @property string $owner_deposit
     * @property string $owner_balance
     * @property string $last_updated
     * @property string $created_dt
     */
    class VwReservations extends CActiveRecord
    {
      /**
       * Returns the static model of the specified AR class.
       * @param string $className active record class name.
       * @return VwReservations the static model class
       */
      public static function model($className=__CLASS__)
      {
        return parent::model($className);
      }
      /**
       * @return string the associated database table name
       */
      public function tableName()
      {
        return 'vwReservations';
      }
      /**
       * @return array validation rules for model attributes.
       */
      public function rules()
      {
        // NOTE: you should only define rules for those attributes that
        // will receive user inputs.
        return array(
          array('reservation_status', 'required'),
          array('id', 'numerical', 'integerOnly'=-->true),
          array('reservation_ref', 'length', 'max'=>20),
          array('reservation_status, owner_deposit, owner_balance', 'length', 'max'=>10),
          array('currency', 'length', 'max'=>5),
          array('last_updated, created_dt', 'safe'),
          // The following rule is used by search().
          // Please remove those attributes that should not be searched.
          array('id, reservation_ref, reservation_status, currency, owner_deposit, owner_balance, last_updated, created_dt', 'safe', 'on'=>'search'),
        );
      }
      etc ....

Using our new model in forms and GridViews

Now, as you might expect or even already do, we can use this model of our view like all other models, so here's a summary ...

  $reservations=vwReservations::model()->findAll();
  $this->render('admin',array(
      'reservations'=>$reservations,
  ));
  /**
  *  admin View
  **/
  ... other stuff first
<!--?php $this--->widget('zii.widgets.grid.CGridView', array(
  'id'=>'reservations-grid',
  'dataProvider'=>$reservations->search(),
  'filter'=>$reservations,
  'columns'=>array(
    'id',
    'reservation_ref',
    'reservation_status',
    'owner_balance',
    'last_updated',
    array(
      'class'=>'CButtonColumn',
    ),
  ),
)); ?>

The Not so Obvious

So, all this so far, is relatively easy and for the seasoned MVC'er would be common place. But in my applications, one huge help is that the vwReservations model can extend my original Reservations model where I have already built many helper properties, functions, relations, scopes etc...

  class vwReservations extends Reservations
  {

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