如何在ActiveRecord中用yii2编写表别名
如何在ActiveRecord中用yii2编写表别名
提问于 2016-06-04 05:38:09
我有如下所示的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 ActiveRecord中的查询看起来像是-
$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 – 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回答 2
Stack Overflow用户
回答已采纳
发布于 2016-06-06 03:42:31
我已经想出答案了。感谢这个页面- 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');Stack Overflow用户
发布于 2016-06-04 06:26:59
在本例中，最好以这种方式对选择的内容(而不是哈希符号)使用文字符号。
$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(); 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/37626777
复制相关文章
相似问题
腾讯云开发者
