如何在 yii2 中的活动记录中编写表别名

Question

我有一个像下面的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

到目前为止，我已经编写了查询，直到 -

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

我在 yii2 活动记录中的查询看起来像 -

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

请告诉我如何编写整个查询。我想我被别名部分困住了，里面有一个子查询。请告诉我如何在查询中编写子查询。

我试过了——

$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();

但收到以下错误

Database Exception – yiidbException
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

在这种情况下，最好以这种方式对选择内容使用文字表示法（而不是哈希表示法）

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

对于更复杂的查询，请使用 findBySql 查询

$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();  

Answer 2

我已经找到了答案。感谢此页面 - www.yiiframework.com/doc-2.0/guide-db-query-builder.html。会像下面这样——

$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');