Join + calculated column + having as a data source for GridView = problem

Yii 2.0 General Discussions
1

Hi all.
I have 5 tables and I am joining them using joinWith (eager loading). Plus I want to add one calculated column which will be used in HAVING condition in the same query.

Example:

SELECT *, TIMESTAMPDIFF(YEAR,user.birth,CURRENT_TIMESTAMP()) as userAgeYears FROM user JOIN post JOIN article JOIN ... HAVING userAgeYears  > 15

PHP code:

$query = User::find()->select([
'*',
'TIMESTAMPDIFF(YEAR,user.birth,CURRENT_TIMESTAMP()) as userAgeYears',
])
->joinWith('post')
->joinWith('article')
...
->having('userAgeYears > 1');

The problem is that I want to display results in GridView therefore Yii is automatically calling SELECT COUNT(*) FROM {my query}

And as all of those tables contain ID columns I receive error:

SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'id'

The problem disapeares when I remove “->having()” from the PHP code or if I specify manually all columns I want to work with (I omit IDs). But there are for example 30-50 columns and I dont want to do this.

Is there any other way how this can work?

2

Sometimes i have best performance when i create a view into my database and then a use the view in the same way of other tables with the proper model, dataprovider etc…