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? — Reborn, Feb 12 '18 at 13:08
Yes sir i am using the Modelsearch to try load the grid with initial values depending on the user information — user5469526, Feb 12 '18 at 14:56

scaisEdge · Accepted Answer · 2018-02-12T15:42:22.867

1

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 . "'");

edited Feb 12 '18 at 15:42

answered Feb 12 '18 at 12:41

scaisEdge

124,973
10
73
87

I tried the code above and it gives this error SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') `c`' at line 1 The SQL being executed was: SELECT COUNT(*) FROM () `c` Error Info: Array ( [0] => 42000 [1] => 1064 [2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') `c`' at line 1 ) ↵ – user5469526 Feb 12 '18 at 12:55
anyway .. in the query provided . there is not .. count. ... so check better if the error is releated to others part of your code ... – scaisEdge Feb 12 '18 at 13:13
I changed the code to >where(["dept'"=>Yii::$app->user->identity->department."'"]) and thats when the error start to appear – user5469526 Feb 12 '18 at 14:55
I tried it uisng $sqlcommand = Yii::$app->db->createCommand("SELECT * FROM bahavehicle where dept ='".Yii::$app->user->identity->department . "'"); $query= Bahavehicle::findBySql($sqlcommand); as you said. The error given was Object of class yii\db\Command could not be converted to string – user5469526 Feb 12 '18 at 15:12
I tried the code which doesn't give any errors but a blank grid. See picture https://ibb.co/g5AcRS – user5469526 Feb 12 '18 at 15:28
are you sure that the Yii::$app->user->identity->department containt the correct value for select rows????? check with var_dump(Yii::$app->user->identity->department) the real content – scaisEdge Feb 12 '18 at 15:32
var_dump information is as follows: C:\wamp64\www\vehicles\backend\views\bahavehicle\index.php:13:string 'Quarantine ' (length=12) – user5469526 Feb 12 '18 at 15:34
seems you have and adding space in 'Quarantine ' check for trimmed values – scaisEdge Feb 12 '18 at 15:35
Thanks million and Thanks for your patience. – user5469526 Feb 12 '18 at 15:42

Yii2 initial Gridview Search in ModelSearch

1 Answers1