Appends an INNER JOIN part to the query.
public innerJoin ( string | array $table, string | array $on = '', array $params = [] ) | ||
$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 [[join()]] on how to specify this parameter. |
$params | array | the parameters (name => value) to be bound to the query. |
/** * Lists all Product models. * @return mixed */ public function actionIndex() { $products = new Query(); $products->select('*')->from('product'); $products->innerJoin('store_product', ['store_product.product_id' => 'product.id']); $dataProvider = new ActiveDataProvider(['query' => $products]); return $this->render('index', ['dataProvider' => $dataProvider]); }
protected function getProperties() { $result = []; $query = new Query(); $query->select(Property::tableName() . '.key, ' . Property::tableName() . '.name')->from(Property::tableName()); $query->innerJoin(PropertyGroup::tableName(), PropertyGroup::tableName() . '.id = ' . Property::tableName() . '.property_group_id'); $query->andWhere([PropertyGroup::tableName() . '.object_id' => $this->object->id]); $command = $query->createCommand(); return ArrayHelper::map($command->queryAll(), 'key', 'name'); }
/** * Validate a client * * @param string $clientId The client's ID * @param string $clientSecret The client's secret (default = "null") * @param string $redirectUri The client's redirect URI (default = "null") * @param string $grantType The grant type used (default = "null") * * @return \League\OAuth2\Server\Entity\ClientEntity */ public function get($clientId, $clientSecret = null, $redirectUri = null, $grantType = null) { $client = null; $selectTargets = "{{%oauth_clients}}.id, {{%oauth_clients}}.name"; if ($redirectUri !== null && is_string($redirectUri) && strlen($redirectUri) > 0) { $selectTargets = "{{%oauth_clients}}.*, {{%oauth_client_redirect_uris}}.*"; } $clientStorageQueryBuilder = new Query(); $clientStorageQueryBuilder->select($selectTargets)->from("{{%oauth_clients}}")->where("{{%oauth_clients}}.id=:oauth_client_id AND {{%oauth_clients}}.secret=:oauth_client_secret_where", [":oauth_client_id" => $clientId, ":oauth_client_secret_where" => $clientSecret]); /** * Regarding oauth_client_redirect_uris: * You may or may not have this table. If you don't, and don't need it , as described in the league docs for each grant, * you won't really pass this condition, and that's why it's implemented this way */ if ($redirectUri !== null && is_string($redirectUri) && strlen($redirectUri) > 0) { $clientStorageQueryBuilder->innerJoin("{{%oauth_client_redirect_uris}}", "{{%oauth_clients}}.id = {{%oauth_client_redirect_uris}}.client_id")->where(["{{%oauth_client_redirect_uris}}.redirect_uri" => $redirectUri]); } $clientStorageResult = $clientStorageQueryBuilder->one(); if (isset($clientStorageResult['id']) && isset($clientStorageResult['name'])) { $client = (new ClientEntity($this->getServer()))->hydrate(["id" => $clientStorageResult["id"], "name" => $clientStorageResult["name"]]); } return $client; }
/** *Отчеты по препаратам в XML * */ public function ListAptekiAnsverXml($region_id, $date = null) { $query = new Query(); $query->select(['preparats_ansver.nal', 'preparats_ansver.zakupka', 'preparats_ansver.prodaja', 'preparats.name', 'preparats.manuf', 'ur_l.name as uname', 'users.username', 'region.name as rname']); $query->from('preparats_ansver'); $query->innerJoin('preparats', 'preparats.id = preparats_ansver.id_o'); $query->innerJoin('ur_l', 'ur_l.id = preparats_ansver.id_apteka'); $query->LeftJoin('users', 'users.id = ur_l.pi_id'); // $query->LeftJoin('users as pi', 'users.id = ur_l.pi_id'); $query->LeftJoin('region', 'region.id = preparats.region_id'); // $query->innerJoin('region', 'region.id = ur_l.region_id'); $query->where(['=', 'preparats.region_id', $region_id]); if (Yii::$app->user->identity->status == 2) { //Регионалы $query->andFilterWhere(['=', 'ur_l.regional_id', Yii::$app->user->identity->id]); } if (Yii::$app->user->identity->status == 3) { //Провизоры $query->andFilterWhere(['=', 'ur_l.pi_id', Yii::$app->user->identity->id]); } if ($date) { $query->andFilterWhere(['like', 'preparats_ansver.date', $date]); } // $query->andFilterWhere(['like', 'preparats_ansver.date', '2016-%']); return $query->all(); /** * $date_like=date("Y-m"); * $date_like2=" and pl_pi_o_ansver.date like '".$date_like."%' "; * * SELECT * pl_region.name AS rname, * pl_apteka.name AS aname, * pl_apteka.address, * pl_apteka.site_name, * pl_pi_o.name, * pl_pi_o.manuf, * pl_pi_o_ansver.nal, * pl_pi_o_ansver.zakupka, * pl_pi_o_ansver.prodaja, * pl_pi_o_ansver.date, * pl_pi.`fio` * FROM * pl_pi_o_ansver * INNER JOIN pl_pi_o ON * pl_pi_o_ansver.id_o = pl_pi_o.id * INNER JOIN pl_apteka ON * pl_pi_o_ansver.id_apteka = pl_apteka.wabc * INNER JOIN pl_region ON * pl_region.id = pl_apteka.region * LEFT JOIN pl_pi_apteki ON * pl_pi_apteki.apt_id = pl_apteka.id * LEFT JOIN pl_pi ON * pl_pi_apteki.pi_id = pl_pi.id * WHERE * pl_pi_o.is_active = 1 * $and_pr * $date_like2 * $order * * */ }
/** * Пулечение всех юо.лиц в регионе плательщиков взносов * @param null $regional_id * @param $region_id * @return array */ public static function getUr_l_in_region($regional_id, $region_id) { $regional_id = (int) $regional_id; $region_id = (int) $region_id; $db = new Query(); $db->from(Ur::tableName()); $db->select(['ur_l.name', 'ur_l.id']); $db->innerJoin('region_ur_l', "region_ur_l.id_ur = ur_l.id"); $db->Where(['=', 'region_ur_l.id_reg', $region_id]); $db->andWhere(['=', 'ur_l.plat', '1']); $db->andwhere(['=', 'ur_l.regional_id', $regional_id]); $db->orderBy('ur_l.name ASC'); $return = $db->all(); return $return; }
/** * 得到查询条件的字符串值 * @param array $diseaseQuery 查询条件 * @param Query $queryObj Query 查询对象 * @return string 查询条件的字符串值 */ private function getQueryCondition($diseaseQuery, $queryObj) { //判断是否有部位方面的查询条件 if (isset($diseaseQuery['part']) && !empty($diseaseQuery['part'])) { $part = $diseaseQuery['part']; //print_r($part);exit; $queryObj->innerJoin("9939_part_disease_rel ddr", "de.id = ddr.diseaseid"); $queryObj->andWhere($part); } elseif (isset($diseaseQuery['disease']) && !empty($diseaseQuery['disease'])) { $disease = $diseaseQuery['disease']; $queryObj->andWhere($disease); } elseif (isset($diseaseQuery['name'])) { $name = $diseaseQuery['name']; $queryObj->andWhere(["like", "de.name", $name]); } elseif (isset($diseaseQuery['typical_symptom'])) { $symptom = $diseaseQuery['typical_symptom']; $queryObj->andWhere(["like", "de.typical_symptom", $symptom]); } return $queryObj; }
public function actionTable() { if (\Yii::$app->user->identity->status == 1) { $users = Users::find()->where(['status' => 2])->orderBy('username')->all(); } else { $users = Users::find()->where(['id' => \Yii::$app->user->identity->id])->orderBy('username')->all(); } foreach ($users as $user) { $id = $user['id']; $db = new Query(); $db->from(Ur::tableName()); $db->select(['COUNT(*) AS count', 'id_reg', 'region.name']); $db->where(['=', 'ur_l.regional_id', $id]); $db->andWhere(['=', 'ur_l.plat', '1']); $db->innerJoin('region_ur_l', "region_ur_l.id_ur = ur_l.id"); $db->innerJoin('region', "region.id = region_ur_l.id_reg"); $db->groupBy('region_ur_l.id_reg'); $db->orderBy('region.name ASC'); $ur_region_array[$id] = $db->all(); $db = new Query(); $db->from(Apteki::tableName()); $db->select(['COUNT(*) AS count', 'region.id', 'region.name']); $db->where(['=', 'apteki.regional_id', $id]); $db->innerJoin('region', "region.id = apteki.region_id"); $db->groupBy('region.id'); $db->orderBy('region.name ASC'); $apteki_region_array[$id] = $db->all(); } return $this->render('table', compact(['ur_region_array', 'apteki_region_array', 'users'])); }
/** * 得到查询条件的字符串值 * @author gaoqing * 2016年1月14日 * @param array $diseaseQuery 查询条件 * @param Query $queryObj Query 查询对象 * @return string 查询条件的字符串值 */ private function getQueryCondition($diseaseQuery, $queryObj) { //判断是否有科室方面的查询条件 if (isset($diseaseQuery['department']) && !empty($diseaseQuery['department'])) { $department = $diseaseQuery['department']; $queryObj->innerJoin("9939_disease_department_rel ddr", "de.id = ddr.diseaseid"); $queryObj->andWhere($department); } //判断是否有疾病表中的查询条件 if (isset($diseaseQuery['disease']) && !empty($diseaseQuery['disease'])) { $disease = $diseaseQuery['disease']; $queryObj->andWhere($disease); } //判断是否根据疾病名称,模糊查询 if (isset($diseaseQuery['name'])) { $name = $diseaseQuery['name']; $queryObj->andWhere(["like", "de.name", $name]); } //判断是否根据症状信息,模糊查询 if (isset($diseaseQuery['typical_symptom'])) { $symptom = $diseaseQuery['typical_symptom']; $queryObj->andWhere(["like", "de.typical_symptom", $symptom]); } return $queryObj; }