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;
}
