yii2 关联查询总结

1，join 方式 直接关联查询

$goodsId = BargainGoods::find()->alias('bg')
                        ->where(['bg.mall_id' => $this->mall->id, 'bg.is_delete' => 0])
                        ->leftJoin(['g' => Goods::tableName()], 'g.id = bg.goods_id')
                        ->andWhere(['g.goods_warehouse_id' => $goodsWarehouseId])
                        ->select(['bg.id']);

2，with方式 ，with方式也有多种写法

$detail = Mch::find()->where([
                'id' => \Yii::$app->user->identity->mch_id ?: $this->id,
                'mall_id' => \Yii::$app->mall->id,
                'is_delete' => 0,
            ])->with('user.userInfo', 'mchUser', 'store', 'category','store.tradeArea','store.tradeAreaList')->asArray()->one();

这里的with关联了许多表，这里要注意字母大小写，使用with关联时，需要在Mch的模型model中添加对应关系，如我在Mch中加入以下代码

 public function getUser()
    {
        return $this->hasOne(User::className(), ['id' => 'user_id']);
    }
    public function getMchUser()
    {
        return $this->hasOne(User::className(), ['mch_id' => 'id']);
    }
    public function getStore()
    {
        return $this->hasOne(Store::className(), ['mch_id' => 'id']);
    }
    public function getCategory()
    {
        return $this->hasOne(MchCommonCat::className(), ['id' => 'mch_common_cat_id']);
    }

store.tradeArea这种用法，则是在store的模型model文件中也创建关联关系。注意这里[]中，箭头左边的是关联表的字段，右边的是当前表的字段

public function getTradeArea()
    {
        return $this->hasOne(TradeArea::className(), ['id' => 'trade_area_id']);
    }
    public function getTradeAreaList()
    {
        return $this->hasMany(TradeArea::className(), ['parent_id' => 'district_id']);
    }

with关联的另一种写法

->with(['goodsSku' => function(ActivityQuery $query) {
	$query->with(['category']);
}]);