Yii中 CDbCriteria with join 聚合查询

在yii里面我们会经常使用到聚合函数来查询语句，在relations定义好关联关系后基于可以使用了。

 比如我们使用 yii  CDbCriteria的join , group 来进行连表查询，这种情况下我们是不需要定义relations的关联关系的：

$seq = [89857,89856];
$c = new CDbCriteria();
$c->addInCondition("t.putrec_seqno",$seq);
$c->addCondition("i.ie_flag = 'I'");
$c->addCondition("i.shop_id=".$shopId);
$c->addCondition("i.sta = 2");
$c->addCondition("i.message_status = 30");
$c->join = "LEFT JOIN tbl_shop i ON t.shop_id = i.id";
$c->select= "( t.deal_num ) AS SUM,t.putrec_seqno";
$c->group = "t.putrec_seqno";
$content = Goods::model()->findAll($c);

 执行的sql如下：

SELECT SUM
	( T.deal_num ) AS SUM,
	T.putrec_seqno 
FROM
	"tbl_goods" "t"
	LEFT JOIN tbl_shop i ON T.invt_id = i.ID 
WHERE
	(
		(
			( ( T.putrec_seqno IN ( 89857, 89856 ) ) AND ( i.ie_flag = 'I' ) ) 
			AND ( i.shop_id = 19930 ) 
		) 
		AND ( i.sta = 2 ) 
	) 
	AND ( i.message_status = 30 ) 
GROUP BY
	T.putrec_seqno

使用with方法,那么我们必须定义关联关系：

$seq = [89857,89856];
$c = new CDbCriteria();
$c->addInCondition("t.putrec_seqno",$seq);
$c->addCondition("i.ie_flag = 'I'");
$c->addCondition("i.shop_id=".$shopId);
$c->addCondition("i.sta = 2");
$c->addCondition("i.message_status = 30");
$c->with = array(
            'shop' => array(
                'alias' => 'i',
                'joinType'  => 'left join',
            )
);
$c->group = 't.putrec_seqno';
$content = Goods::model()->findAll($c);

直接使用命令执行查批量：

$where = "i.shop_id={$shopId}";
$sql = "SELECT sum(t.deal_num) AS sum,t.putrec_seqno from {{goods}} t
        INNER JOIN {{shop}} i ON t.shop_id = i.id
        WHERE {$where} AND i.ie_flag='I' AND i.message_status=30 AND i.sta = 2 group by t.putrec_seqno";    

$rows = Yii::app()->db->createCommand($sql)->queryAll();    
 

直接使用命令执行查单条：

$where = "i.shop_id={$shopId}";
$sql = "SELECT sum(t.deal_num) AS sum from {{shop_details}} t
                INNER JOIN {{shop}} i ON t.shop_id = i.id
                WHERE {$where} AND i.ie_flag='E' AND i.message_status='30'";
$row = Yii::app()->db->createCommand($sql)->queryRow();
 

直接使用命令执行查批量翻页：

public function setShopDetaliSql($shopId, $starttime, $endtime, $page, $pageSize) {
  $sql = " select g.putrec_seqno,g.matnr_no,g.hs_code,g.goods_name,g.deal_uom,g.deal_currency,g.deal_price,g.goods_model,i.shop_no,i.shop_dcl_time,g.deal_num,g.goods_seq_no from tbl_shop_goods as g LEFT JOIN  tbl_shop as i on g.shop_id = i.id  where   i.shop_id = {$shopId}  AND i.create_time >= {$starttime} AND i.create_time <= {$endtime} AND i.sta = 2 AND i.message_status = 30";
  $offset =  ($page - 1) * $pageSize;
  $sql.=" limit ".$pageSize." offset ".$offset;

  $countSql = "select count(*) from (select g.putrec_seqno,g.matnr_no,g.hs_code,g.goods_name,g.deal_uom,g.deal_currency,g.deal_price,g.goods_model from tbl_shop_goods as g LEFT JOIN  tbl_shop as i on g.shop_id = i.id  where   i.shop_id = {$shopId}  AND i.create_time >= {$starttime} AND i.create_time <= {$endtime} AND i.sta = 2 AND i.message_status = 30 ) as t";
  $total =  Yii::app()->db->createCommand($countSql)->queryRow();
  $rows =  Yii::app()->db->createCommand($sql)->queryAll();
return [$total['count'], $rows];
}