PHP Query::innerJoin方法代码示例

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