yii2 如何使用 sqldataProvider 进行搜索

问题描述

对于我的要求，我需要使用 SqlDataProvider 而不是 ActiveDataProvider.

for my requirement I need to use SqlDataProvider instead of ActiveDataProvider.

但是如果我启用搜索，我会收到如下错误:

but if I enable search I am getting the error like:

Calling unknown method: yii\data\SqlDataProvider::isAttributeRequired()

我的 searchModel - `userProfileSearch 看起来像这样:

my searchModel - `userProfileSearch looks like this:

public function search($params)
    {
     //   $query = UserProfile::find()->where($cond);

     $query = new Query;
        $query->select('*')->from('user_profile');


        // add conditions that should always apply here

        $dataProvider = new SqlDataProvider([
           // 'query' => $query,
           'sql' => $query->createCommand()->sql,
        ]);

        $this->load($params);

        if (!$this->validate()) {
            // uncomment the following line if you do not want to return any records when validation fails
            // $query->where('0=1');
            return $dataProvider;
        }

        // grid filtering conditions
        $query->andFilterWhere([
            'id' => $this['id'],
            'user_id' => $this['user_id'],
            'pincode' => $this['pincode'],
        ]);

        $query->andFilterWhere(['like', 'first_name', $this['first_name']])

            ->andFilterWhere(['like', 'last_name', $this->last_name])
            ->andFilterWhere(['like', 'phone', $this->phone])
            ->andFilterWhere(['like', 'email', $this->email])
            ->andFilterWhere(['like', 'gender', $this->gender])
            ->andFilterWhere(['like', 'skill_level', $this->skill_level])
            ->andFilterWhere(['like', 'play_type', $this->play_type])
            ->andFilterWhere(['like', 'address1', $this->address1])
            ->andFilterWhere(['like', 'address2', $this->address2])
            ->andFilterWhere(['like', 'city', $this->city])
            ->andFilterWhere(['like', 'state', $this->state])

            ->andFilterWhere(['like', 'Country', $this->country]);

        return $dataProvider;
    }

在我的控制器中它看起来像这样:

and in my controller it looks like this:

public function actionIndex()
    {
        $count = Yii::$app->db->createCommand('SELECT COUNT(*) FROM user_profile')->queryScalar();


    $sql= "$sql= "Select user_profile.id as id,
    user_profile.user_id as user_id,
    user_profile.first_name as first_name,
    user_profile.last_name as last_name,
    user_profile.city as city,
    user_profile.pincode as pincode,
    user_profile.profile_image as profile_image,
    user_profile.gender as gender,
    user_profile.play_type as play_type,
    user_profile.skill_level as skill_level,
    ( 3959 * acos
    (cos ( radians($latitude))
    * cos( radians( latitude ))
    * cos( radians( longitude) - radians($longitude))
     + sin ( radians($latitude) )
    * sin( radians( latitude))))
    AS distance
    FROM user_profile
    HAVING distance < 20";";

    $searchModel = new UserProfileSearch();
    $sqlProvider = new SqlDataProvider(['sql'=> $sql]);
    $searchModel = $searchModel->search(Yii::$app->request->queryParams,$sql);

    return $this->render('index', [
        'searchModel' => $searchModel,
        'dataProvider' => $sqlProvider,
    ]);
}

如何使搜索工作?

推荐答案

虽然我用 SqlDataProvider 解决不了，当然想知道如何使用 SqlDataProvider 来实现.

Though I couldn't resolve it using SqlDataProvider and certainly like to know how to implement using SqlDataProvider.

从 Gii 生成的代码中保持所有内容完整，我修改了 UserProfileSearch.php 中的查询

Keeping everything intact from Gii generated code I modified the query in UserProfileSearch.php

如下:

public function search($params)
    {
        $user = UserProfile::findOne(['user_id'=>Yii::$app->user->identity->id]);

        $latitude = $user->latitude;
        $longitude = $user->longitude;
        $query = UserProfile::find()->select(['id','user_id','first_name','last_name','phone','email','pincode','skill_level','play_type',
        'profile_image','latitude','longitude','gender','city',"( 3959 * acos
        (cos ( radians($latitude))
        * cos(radians(latitude))
        * cos( radians(longitude) - radians($longitude))
         + sin (radians($latitude) )
        * sin(radians(latitude)))) as distance"]);
        $query->having(['<','distance', 12.4274]);



        $dataProvider = new ActiveDataProvider([
        'query'=>$query

        ]);

有了这个，我的 20 公里预过滤器和搜索一样工作正常.

with this my pre-filter for 20Km is working fine as well as the search.

这篇关于yii2 如何使用 sqldataProvider 进行搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！