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