yii2 how to use search with sqldataProvider

Question

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()

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,
    ]);
}

How I can make the search work?

SqlDataProvider is not based on a model so the model related function as isAttributeRequired can't work .. could be you are using somewhere some validation function .. but this i not possible .. you should void model validation an eventually perform you validation directly .. — ScaisEdge
– ScaisEdge, Commented Apr 10, 2021 at 17:06
Hi scaisEdge - I am using the default generated _search.php for the search. not using any filter. like for example - <?php $form->field($model, 'gender')->dropDownList(['M'=>'Male','F'=>'Female'],['prompt'=>'Select Gender'])->label(false) ?> will it not work or where I need to make any modificaiton? — Joshi
– Joshi, Commented Apr 10, 2021 at 18:26

harmoxyne · Accepted Answer · 2021-04-10 12:44:10Z

0

I think it's because you are rewriting your $searchModel variable with return of search() method which returns SqlDataProvider.

Try to change your controller to this:

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

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

answered Apr 10, 2021 at 12:44

harmoxyne

715 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joshi · Accepted Answer · 2021-04-11 18:49:47Z

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

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

like below:

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           
            
        ]);

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

Collectives™ on Stack Overflow

yii2 how to use search with sqldataProvider

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related