PHP Query::join方法代码示例

 public function getQuiz($id)
 {
     $query = new Query();
     $query->select('quiz_questions.id as qid,quiz_questions.*,questions_answers.*')->from('quiz_questions');
     $query->join('join', 'questions_answers', 'quiz_questions.id=question_id');
     $query->where('quiz_questions.quiz_id=' . $id);
     return $this->_group_by($query->all(), 'qid');
 }

 public static function getSingleCategoryWithPosts($categoryId)
 {
     $query = new Query();
     $query->select('discussion_posts.*,discussion_categories.*,discussion_posts.id as dpId');
     $query->from('discussion_categories');
     $dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => 8]]);
     $query->andFilterWhere(['discussion_categories.id' => $categoryId]);
     $query->join('left join', 'discussion_posts', 'discussion_posts.discussion_category_id=discussion_categories.id');
     $query->orderBy('discussion_posts.id DESC');
     return $dataProvider;
 }

 public static function getSalesData($duration)
 {
     $dates = [];
     $transactions = [];
     $transactionskv = [];
     $amount = [];
     $statusPaid = [Order::STATUS_PAID, Order::STATUS_DELIVERED];
     $statusPaid = join(",", $statusPaid);
     $orderTable = CartTables::TABLE_ORDER;
     $txnTable = CartTables::TABLE_ORDER_TRANSACTION;
     $query = new Query();
     $query->select(["date(`{$txnTable}`.`createdAt`) as date", 'sum( amount ) as amount']);
     $query->from($txnTable);
     $query->join('LEFT JOIN', $orderTable, "orderId = `{$orderTable}`.`id`");
     $query->where(" {$orderTable}.status in ( {$statusPaid} )");
     switch ($duration) {
         case 0:
             // Current Week - Starting with Sun
             $dates = DateUtil::getCurrentWeekDates();
             $transactions = $query->andWhere("YEARWEEK( `{$txnTable}`.`createdAt` ) = YEARWEEK( CURRENT_DATE ) ")->groupBy(['date'])->all();
             break;
         case 1:
             // Last Week - Starting with Sun
             $dates = DateUtil::getLastWeekDates();
             $transactions = $query->andWhere("YEARWEEK( `{$txnTable}`.`createdAt` ) = YEARWEEK( CURRENT_DATE - INTERVAL 7 DAY ) ")->groupBy(['date'])->all();
             break;
         case 2:
             // This Month
             $dates = DateUtil::getCurrentMonthDates();
             $transactions = $query->andWhere("MONTH( `{$txnTable}`.`createdAt` ) = ( MONTH( NOW() ) ) AND YEAR( `{$txnTable}`.`createdAt` ) = YEAR( NOW() ) ")->groupBy(['date'])->all();
             break;
         case 3:
             // Last Month
             $dates = DateUtil::getLastMonthDates();
             $transactions = $query->andWhere("MONTH( `{$txnTable}`.`createdAt` ) = ( MONTH( NOW() ) - 1 ) AND YEAR( `{$txnTable}`.`createdAt` ) = YEAR( NOW() ) ")->groupBy(['date'])->all();
             break;
     }
     foreach ($transactions as $transaction) {
         $transactionskv[$transaction['date']] = $transaction['amount'];
     }
     foreach ($dates as $date) {
         if (isset($transactionskv[$date])) {
             $amount[] = $transactionskv[$date];
         } else {
             $amount[] = 0;
         }
     }
     return $amount;
 }

 public static function getList($page = 1, $rows = 10, $condition = '', $conditionParams = [])
 {
     $return = [];
     $query = new Query();
     $query->select('COUNT(1)');
     $query->from(self::tableName() . ' t0');
     $query->where($condition, $conditionParams);
     $result['total'] = $query->scalar();
     $query->select(['t0.id', 't0.username', 't0.email', 't1.firstname', 't1.lastname']);
     $query->join('LEFT JOIN', 'user_profile t1', 't0.id = t1.user_id');
     $query->offset($page * $rows - $rows);
     $query->limit($rows);
     $result['rows'] = $query->All();
     foreach ($result['rows'] as $k => $v) {
         $myRoles = Yii::$app->authManager->getRolesByUser($v['id']);
         if (count($myRoles)) {
             foreach ($myRoles as $kk => $vv) {
                 $result['rows'][$k]['role'] = $kk;
                 break;
             }
         }
     }
     return $result;
 }

 /**
  * Adds join statement to basic query from relations declared in config files
  *
  * @param \yii\db\Query $query
  * @param string $table
  * @return array $columns to select statement
  */
 public function getRelations(&$query, $table)
 {
     $relations = [];
     $columns = [];
     /** @var Module $module */
     $module = $this->module;
     if (isset($module->tables[$table]['relations'])) {
         $relations = $module->tables[$table]['relations'];
     }
     foreach ($relations as $relation => $relationParams) {
         $query->join($relationParams['type'], $relationParams['table'] . ' ' . $relationParams['alias'], $relationParams['on']);
         $columns[] = $relationParams['alias'] . '.' . $relationParams['representive_columns'] . ' ' . $relation;
     }
     return $columns;
 }

 /**
  * Find the models for a foreign data source.
  *
  * @param array $params the query parameters
  *
  * @return Query the foreign data query
  */
 protected function find($params)
 {
     $debug = false;
     $q = new Query();
     $q->select('*');
     $q->from($this->_tableName);
     foreach ($params as $k => $v) {
         if ($k === 'join') {
             foreach ($v as $join) {
                 if (!isset($join['type'])) {
                     $join['type'] = 'INNER JOIN';
                 }
                 if (!isset($join['params'])) {
                     $join['params'] = [];
                 }
                 $q->join($join['type'], $join['table'], $join['on'], $join['params']);
             }
             $debug = true;
         } elseif (in_array($k, ['where'])) {
             $q->{$k}($v);
         } else {
             $q->{$k} = $v;
         }
     }
     if ($debug) {
         //var_dump($q->createCommand()->rawSql);exit;
     }
     return $q;
 }