Yii2 initial Gridview Search in ModelSearch

Question

Currently, I am having problems trying to search using SQL where clause in yii2. I have in my create user a column for the department, where the admin will insert which department the user belongs to. Depending on the department the user is given, he/she will only be able to view vehicles that belong to his/her department. Unfortunately, I'm receiving this error. Sorry for my English

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Quarantine' in 'where clause' The SQL being executed was: SELECT * FROM vehicle where dept =Quarantine Error Info: Array ( [0] => 42S22 [1] => 1054 [2] => Unknown column 'Quarantine' in 'where clause' ) ↵ Caused by: PDOException SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Quarantine' in 'where clause'

The code is as follows:

In my model vehicle search

    public function search($params)
    {
          // $departmentinfo=Yii::$app->user->identity->department;
         //  $dept=$departmentinfo;
         // $department=$departmentinfo;
      // $query = Vehicle::find();
      //$loginiddept=Yii::$app->user->getId();

       //$command = Yii::$app->db->createCommand("SELECT department FROM loginusers where login_id=".Yii::$app->user->getId())->queryScalar();

           //$query = Vehicle::find()->where(['dept'=>$loginiddept]);
              $sqlcommand = Yii::$app->db->createCommand("SELECT * FROM bahavehicle where dept =". Yii::$app->user->identity->department)->queryScalar();

             $query=  Vehicle::findBySql($sqlcommand);

        // add conditions that should always apply here

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);

        $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,

        ]);

        $query->andFilterWhere(['like', 'driver', $this->driver])
         //  ->andFilterWhere(['like', 'dept', $this->dept])
            ->andFilterWhere(['like', 'lp', $this->lp])
            ->andFilterWhere(['like', 'make', $this->make])
            ->andFilterWhere(['like', 'year', $this->year])
            ->andFilterWhere(['like', 'colour', $this->colour])
            ->andFilterWhere(['like', 'vin', $this->vin])
            ->andFilterWhere(['like', 'license_expire', $this->license_expire])
            ->andFilterWhere(['like', 'insurance_expire', $this->insurance_expire]);


        return $dataProvider;
    }

Any assistance is highly appreciated.

Ok and you are using it with the GridView search if i am not wrong? — Muhammad Omer Aslam
Yes sir i am using the Modelsearch to try load the grid with initial values depending on the user information — user5469526

ScaisEdge ScaisEdge · Accepted Answer · 2018-02-12T12:41:05

You should wrap you dept inside single quote otherwise the value is assumed as column name

and You are using the result of a command object inside a findbysql. try using the sql code directly in findBySql

  $query=  Vehicle::findBySql("SELECT * FROM bahavehicle where dept ='". 
    Yii::$app->user->identity->department . "'");

Yii2 initial Gridview Search in ModelSearch

1 Answers