How to write table alias in ActiveRecord in yii2

Question

I have a sql query like below -

SELECT 
district, coalesce(sell.sale,0) as totalsale 
FROM `districts` 
left join 
(SELECT parties_district, billdate, sum(billamount) as sale FROM `bills` left join parties on bills.bills_partyname = parties.parties_partyname group by parties_district) as sell 
on sell.parties_district = districts.district

So far I've written the query till -

SELECT 
parties_district, billdate, sum(billamount) as sale 
FROM `bills` 
left join parties 
on bills.bills_partyname = parties.parties_partyname 
group by parties_district

And my query in yii2 ActiveRecord looks like -

$query = Parties::find()
->select(['parties_district','parties_partyname','sum(billamount) as sale'])
->joinWith('bills')
->groupby('parties_district');

Please tell me how can I write the whole query. I think I'm stuck with the alias part and there is a subquery in it. Please tell me how can I write a subquery within a query.

I've tried -

$subQuery = Parties::find()->select(['parties_district','parties_partyname','sum(billamount) as sale'])->joinWith('bills sell')->groupby('parties_district');
        $query = Districts::find()->select(['district','coalesce(sell.sale,0) as totalsale'])->leftJoin('$subQuery', 'sell.parties_district = districts.district');
        $models = $query->all();

but getting the following error

Database Exception – yii\db\Exception
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'gm.$subquery' doesn't exist
The SQL being executed was: SELECT `district`, coalesce(sell.sale,0) as totalsale FROM `districts` LEFT JOIN `$subQuery` ON sell.parties_district = districts.district

Error Info: Array
(
    [0] => 42S02
    [1] => 1146
    [2] => Table 'gm.$subquery' doesn't exist
)

↵
Caused by: PDOException
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'gm.$subquery' doesn't exist

Answer 1

I've figured out the answer. Thanks to this page - www.yiiframework.com/doc-2.0/guide-db-query-builder.html. It will be like below -

$subQuery1 = (new Query())->select(['parties_district','billdate','sum(billamount) as sale'])->from ('bills')->join('LEFT JOIN','parties','bills.bills_partyname = parties.parties_partyname')->groupby('parties_district')->where('billdate != "NULL"');
$query = (new Query())->select(['district','coalesce(sell.sale,0) as totalsale'])->from('districts')->leftJoin(['sell' => $subQuery1],'sell.parties_district = districts.district');

Answer 2

In this case is better use a literal notation for select content (and not hash notation) this way

$query = Parties::find()
->select('parties_district, parties_partyname, sum(billamount) as sale'])
->joinWith('bills')
 ->groupby('parties_district');

and for more complex query use findBySql Query

$sql = "SELECT 
        district, coalesce(sell.sale,0) as totalsale 
        FROM `districts` 
        left join 
        (SELECT parties_district, billdate, sum(billamount) as sale 
           FROM `bills` left join parties 
                  on bills.bills_partyname = parties.parties_partyname 
        group by parties_district) as sell 
           on sell.parties_district = districts.district"

$model = Districts::findBySql($sql)->all();