The first parameter specifies what type of join it is.
public join ( string $type, string | array $table, string | array $on = '', array $params = [] ) | ||
$type | string | the type of join, such as INNER JOIN, LEFT JOIN. |
$table | string | array | the table to be joined. Use a string to represent the name of the table to be joined. The table name can contain a schema prefix (e.g. 'public.user') and/or table alias (e.g. 'user u'). The method will automatically quote the table name unless it contains some parenthesis (which means the table is given as a sub-query or DB expression). Use an array to represent joining with a sub-query. The array must contain only one element. The value must be a [[Query]] object representing the sub-query while the corresponding key represents the alias for the sub-query. |
$on | string | array | the join condition that should appear in the ON part. Please refer to [[where()]] on how to specify this parameter. Note that the array format of [[where()]] is designed to match columns to values instead of columns to columns, so the following would **not** work as expected: `['post.author_id' => 'user.id']`, it would match the `post.author_id` column value against the string `'user.id'`. It is recommended to use the string syntax here which is more suited for a join: ```php 'post.author_id = user.id' ``` |
$params | array | the parameters (name => value) to be bound to the query. |
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; }