How we can define those sql statement inside model but not in controller?

Yii 1.1 General Discussion
1

My project now actually can run, but my sql statement is in controller but not in model, if i wan all sql statement is in model. How can i do it?

2

I often get the model adding some functionality.




class MyModel ... {


       public function getSomeParticularValue () {

              $this->model(...)

                      ->select(...)

                      ->with(...)

                      ->join(...)

                      ->where(...)

                      ->query();

       }


       public function getSomeOtherParticularValue () {

              $this->model(...)

                      ->select(...)

                      ->with(...)

                      ->join(...)

                      ->where(...)

                      ->query();

       }


}

In this way you can, for example, type Users::model()->getAllRegistered(); or maybe Users::model()->getAllBlocked(); Is really semantic. And in your controller you’ll can write:




class TheControllerController extends .... {


      public function someAction() {

            $this->render('page', array(

                  'usersRegistered' =>Users::model()->getAllRegistered(),

                  'usersBlocked' =>Users::model()->getAllBlocked(),

            ));

      }


}

And in the view:




foreach($usersRegistered as $item) {

      echo $item->username;

}
3

I’m having problems getting this to work. I get the error


vwOwnerDetails and its behaviors do not have a method or closure named "select".

and here is the code in my model …




        public function getNameEmail($pid) {

              $this->model()->select('o1.property_id, o1.address AS eMail, o2.address AS NAME')

                    ->from('vwownerdetails o1')

                    ->leftjoin('vwownerdetails o2', 'o2.property_id=o1.property_id AND o2.address_type=\'name\'')

                    ->where('o1.property_id=:property_id and o1.address_type=\'Email\'',array('property_id'=>$pid))

                    ->queryRow();

        }

In your example you specified $this->model(…) what did you mean to put inside these brackets?

Thanks

4

/* Moved to General Discussion */

See last example in this section of the guide.

5

Thanks,

Kyhe’s original question (and mine also) refers to embedding SQL into the main model.

According to this, though, the SQL builder functions are only available in the with() clause and therefore relate to related models only - Not the model in question.

or have I got the wrong end of the stick?

6

Now I’m confused too :)

The syntax $this->model() will return the class instance. So I suggested to use a CDbCriteria property array as parameter to a find…() call.

Query Builder syntax was shown above. See syntax example here. Possibly a generic solution might use $this-getDbConnection() (e.g in case of multiple db connections). The class instance shouldn’t be needed for this.

(untested)

/Tommy

7

Confused … me x2 too :D !

But finally got there, thanks for pointers. The final syntax is nothing like above but based on the CDbCriteria, as you suggested:-


     $this->model()->find(array(

                    'select'=>'o1.property_id, o1.address AS eMail, o2.address AS NAME',

                    'alias'=>'o1',

                    'join'=>'LEFT JOIN vwownerdetails o2 ON (o2.property_id=o1.property_id AND o2.address_type=\'name\')',

                    'condition'=>'o1.property_id=:property_id and o1.address_type=\'Email\'',

                    'params'=>array('property_id'=>$pid),

                    ));