GridView - filtering on calculated column

Yii 2.0 General Discussions
1

So I have a calculated column age which I retrieve as follows:

$query = Profile::find()
    ->select(['*', 'age' => 'TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE())']);

I have added public $age; in my Profile model.

And here’s the rest of the search($params) function:

$dataProvider = new ActiveDataProvider([
    'query' => $query,
]);

$this->load($params);

// grid filtering conditions
$query->andFilterWhere([
    'id' => $this->id,
    'user_id' => $this->user_id,
    'gender' => $this->gender,
    'age' => $this->age,
]);

return $dataProvider;

GridView:

<?php echo GridView::widget([
    'dataProvider' => $dataProvider,
    'filterModel' => $searchModel,
    'columns' => [
        ['class' => 'yii\grid\SerialColumn'],

        'id',
        'user_id',
        'gender',
        'age',

        [
            'class' => 'yii\grid\ActionColumn',
        ],
    ],
]); ?>

The age is displayed correctly, however when I enter a value in the age search filter, I get an error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘age’ in ‘where clause’
The SQL being executed was: SELECT COUNT(*) FROM profile WHERE (age=‘35’)

2

Hi There,

Here you go !

Your Query and Data Providers

    <?php
    $query = Profile::find();

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
            ]);

    $this->load($params);
    $ageYear = '';
    if (!empty($this->date_of_birth)) {
        $stripInt = (int) $this->date_of_birth;
        $ageYear = date('Y', strtotime("-$stripInt years"));//Search by year with respect to user entered date!
    }
    // grid filtering conditions
    $query->andFilterWhere([
        'id' => $this->id,
        'user_id' => $this->user_id,
        'gender' => $this->gender,
        'YEAR(date_of_birth)' => $ageYear,
    ]);

    return $dataProvider;
    ?>

In your Gridview use PHP to calculate the age

    <?=
GridView::widget([
    'dataProvider' => $dataProvider,
    'filterModel' => $searchModel,
    'columns' => [
        ['class' => 'yii\grid\SerialColumn'],
        'id',
        'user_id',
        'gender',
        [
            'attribute' => 'date_of_birth',
            'label' => 'Age', //Only user's point of view it's age but from programmers side it's DOB
            'value' => function ($model, $key, $index, $column) { //Make use of the construct
                $dateOfBirth = $model->date_of_birth;
                $today = date("Y-m-d");
                $diff = date_diff(date_create($dateOfBirth), date_create($today));
                return $diff->format('%y');
            },
            'format' => 'raw'
        ],
        [
            'class' => 'yii\grid\ActionColumn',
        ],
    ],
]);
?>

Cheers!

3

No, that doesn’t look right at all. I want to make use of my calculated column from the database - not do the calculations in my PHP code.

4

I think you should use having instead of where for age, because it’s a calculated column.

$query = Profile::find()
    ->select(['*', 'age' => 'TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE())']);
...
$query->andFilterWhere([
    'id' => $this->id,
    'user_id' => $this->user_id,
    'gender' => $this->gender,
//    'age' => $this->age,
]);
$query->andFilterHaving([
    'age' => $this->age
]);

Check the following section of the guide. It’s very useful.
Guide > ActiveRecord > Selecting extra fields

1 Like
5

This should also work:

$queryForAge = new Expression('TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE())');
$query = Profile::find()->select(['*', 'age' => $queryForAge]);
$query->andFilterWhere([
    'id' => $this->id,
    'user_id' => $this->user_id,
    'gender' => $this->gender,
    $queryForAge => $this->age,
]);

I’m not sure whether using having is more effective or not.

1 Like
6

Thanks, this works perfectly.