Write my sql statement

Archives Feature Requests
1

Hi friends,

How can I write an SQL query in criteria.

In my case, I want to create a function in model and then controller call model’s function to receive results in array format.

It is my code:

Model (I named "LichChay" is my model name)




 public function getAvailableYearModel(){

        $criteria = new CDbCriteria();

        $criteria->select = "subStr(ngay_kh, 1,4) ngay";

        $criteria->distinct = TRUE;


        return LichChay::model()->findAll($criteria);

Controller:




public function getAvailableYear(){

        return LichChay::model()->getAvailableYearModel();        

    }

View:




$years = $this->getAvailableYear();

I’d tested in phpmysql with statement:




SELECT DISTINCT substr( ngay_kh, 1, 4 ) years

FROM `lich_chay`

and it return my expect results. But with the above MVC, it seem no effective!

Any idea ? :) :D

2

Hi Logos,

I highly recommend you a good readin: http://www.yiiframework.com/doc/guide/1.1/en/basics.best-practices

Good place to put the business logic is within the model, then, instead of using the controller shortcut you can just use in the view the LichChay::model()->getAvailableYearModel(); . Let the controller do what is doing best.

About your question:

findAll returns an array of AR and maybe because of the alias you do not get the results. Try to include a public property on your model object as ngay and then run the statement again (I never had that tested, i am curious about the result)

3

Thanks :D