Yii using virtual attributes

Hello guys,

I`m a bit stuck on doing something.

I have a project in which a must get a budget_type estimated value per year based on multiple commands estimated prices.

So I have budget_type table, budgets table and commands table.

Budget_type [id, name]

Budgets [id, type_id, year, month]

Commands[id, budget_id, estimated_value, units]

All table are related.

I’ve set a virtual attribute year for the budget_type model (it gets its value from a POST request) and now I have a function getEstimatedValue in budget_type model in which I have a select query like this:

public function getEstimatedValue(){

$sum = Yii::app()->db->createCommand()

                ->select('SUM(estimated_price * units)')

                ->from('commands com')

                ->join('budgets bug', 'com.budget_id = bug.id')

                ->where('bug.year = ' . $this->year)

                ->queryAll();

return $sum;


}

When i try to run the code i get an mysql error saying that CDbCommand failed to execute the SQL statement:SELECT SUM(estimated_price * units)

FROM commands com

JOIN budgets bug ON com.budget_id = bug.id

WHERE bug.year =

Any advice on how to use a model virtual attribute to get another virtual attribute?

Tnx in advance.

Try with something like

$model = new Yourmodel();

$model->year = '2012';

$this->getEstimatedValue();

Hello mdomba and thanks for the quick reply.

I’ve forgot to mention that i do something like

$model = new BudgetType;

$model ->attributes = $_POST['BudgetType];


//i render my view

When i try to build a grid or export it to excel - with $model->search(); - it gives me that error.

I wonder if i can get estimated value as an attribute because i have much more budget-types and i want to filter them by year. (i want to be able to access them as $model->estimatedValue)

Thanks again

My point was for you to test the code to see that it works…

in my example you can use

 $model->estimatedValue;

without a problem…

then you would ask yourself how come this works now… but in my example it does not… and possibly you would understand that $this->year is empty in your case.

With your additional info: the reason why it’s not working for you (I think) is that when the seach() method is called $this->year is empty as it did not get the value from the $_POST array

I verified your previous piece of code and it works fine. That`s why i didn’t said anything about it.

In search method $this->year has the posted value, but when getEstimatedValue is called … $this->year is empty.

This is quite awkward …

You did not post any code about your usage so I’m just guessing here…

If you are calling getEstimatedValue() from the search model then one solution would be to pass the year value as a parameter

In my controller:

public function actionExportValuesPerYear() {

        $model = new BudgetType;

        $model->unsetAttributes();


        $years = Yii::app()->db->createCommand()

                ->selectDistinct('year')

                ->from('budgets')

                ->order('year desc')

                ->queryAll();


        $years = CHtml::listData($years, 'year', 'year');

       

        $model->year = date('Y');


        if (isset($_POST['BudgetType'])) {

            $model->attributes = $_POST['BudgetType'];

              

            $budgets = $model->search();

            $budgets->pagination = false;


            $columns = array(

                'name',

                'estimatedValue',

            );


            $excelGenerator = new ExcelView(array('dataProvider' => $budgets,

                        'columns' => $columns,

                    ));

            $excelGenerator->run();

        }


        $this->renderPartial('yearsituation', array(

            'model' => $model, 'years' => $years

        ));

    }

My BudgetType model :

class BudgetType extends CActiveRecord {


    public $year;


public function relations() {

        return array(

            'budgets' => array(self::HAS_MANY, 'Budget', 'type_id'),

        );

}

  

public function getEstimatedValue() {

        //this->year is empty


        $sum = Yii::app()->db->createCommand()

                ->select('SUM(estimated_price * units)')

                ->from('commands com')

                ->join('budgets bug', 'com.budget_id = bug.id')

                ->where('bug.year = ' . $this->year)

                ->queryAll();

        return $sum;

    }





public function search(){

        $criteria = new CDbCriteria;

       

        $criteria->compare('name', $this->name, true);

               

        // here if I dump $this->year it returns me $_POST['BudgetType']['year'] value


        if ($this->year != "") {

            $criteria->with = array('budgets');

            $criteria->compare('budgets.year', $this->year);

        }


        return new CActiveDataProvider($this, array(

                    'criteria' => $criteria,

        ));

}  


//and other built in methods

}

I will try your suggestion to pass year as a parameter to see if it works.

Thanks a lot for your time.

I found a solution but I don’t know if it is the right one.

$this->widget('GridView', array(

            'id' => 'command-grid',

            'dataProvider' => $model->search(),

            'columns' => array(

                'name',

                array(

                    'name' => 'estimated value',

                    'type' => 'raw',

                    'value' => 'BudgetType::getEstimatedValue($data->id, '.$model->year.')'

                ),

            ),

        ));

I realised that after the search() method virtual attributes are not passed through CActiveDataProvider to the rest of models found by search.

Thanks again mdomba for your time.