/** * @return $this */ public function withAvgRating() { $this->select('p.*, COALESCE(AVG(rating), 0) as `rating`')->from(['p' => '{{%product}}'])->leftJoin(Comment::tableName(), 'product_id = p.id')->groupBy('p.id'); return $this; }
public function actionComment() { $filePath = __DIR__ . '/ZhongShanGongYuan.txt'; $handle = fopen($filePath, 'r'); $rows = array(); $i = 0; while (($line = fgets($handle)) !== false) { if (preg_match('/\\"shopId\\"/', $line)) { $row = []; preg_match('/\\"(\\d+)\\"/', $line, $matches); $row['shop_id'] = $matches[1]; $rows[$i] = $row; $i++; } if (preg_match('/\\"comments\\"/', $line)) { preg_match('/\\"[^\\"]+\\"[^\\"]+\\"([^\\"]+)\\"/', $line, $matches); $rows[$i - 1]['comment'] = $matches[1]; } if (preg_match('/\\"pic_link\\"/', $line)) { preg_match('/\\"[^\\"]+\\"[^\\"]+\\"([^\\"]+)\\"/', $line, $matches); $rows[$i - 1]['pic_link'] = $matches[1]; } } Yii::$app->db->createCommand()->batchInsert(Comment::tableName(), ['shop_id', 'comment', 'pic_link'], $rows)->execute(); }