Hi, I need to do this query in Yii2 and I could find the solution using query builder
MYSQL
select employee_id, sum(amount)
from
(
select amount, employee_id, status from company_expense
union
select amount, employee_id, status from expense
) as expenses
where status = 1
group by employee_id
YII
$query = new Query;
$query->select('amount, employee_id, status')->from('company_expense');
$anotherQuery = new Query;
$anotherQuery->select('amount, employee_id, status')->from('expense');
$query->union($anotherQuery);
I need to create a new query to group by employee_id. Is there any way to make a select from a temporary table ($QUERY)?
$query = new Query;
$query->select('amount, employee_id, status')->from('company_expense');
$anotherQuery = new Query;
$anotherQuery->select('amount, employee_id, status')->from('expense');
$query->union($anotherQuery);
SO I PUT A QUERY IN YO QUERY
$qiang_will_kill_me = new \yii\db\Query;
$qiang_will_kill_me->select(['SUM(t.amount) as gotcha']);
$qiang_will_kill_me->from('(' . $query->createCommand()->getRawSql() . ') as t');