Yii2 Gii write own sql to filter data

Question

I used gii in yii2 to generate a crud system. I need to write this sql to filter correct data in table.

$date_now = date('Y-m-d');

$query = "SELECT * FROM reservation_db WHERE STR_TO_DATE(date,'%d-%m-%Y') > '$date_now'";

I know this is wrong, I save date as type text in database. So, I need using STR_TO_DATE to convert from string to date and make sql only select data greater then today.

Any idea where I do this sql?

Here is my ReservationSearch Model

 <?php

 namespace app\models;

 use Yii;
 use yii\base\Model;
use yii\data\ActiveDataProvider;
use app\models\ReservationDb;

/** * ReservationSearch represents the model behind the search form about app\models\ReservationDb. */ class ReservationSearch extends ReservationDb { /** * @inheritdoc */ public function rules() { return [ [['reservation_id', 'adult', 'child', 'infat', 'phone_num'], 'integer'], [['name', 'date', 'time', 'session', 'seat_area', 'remark', 'payment_status', 'reservation_date'], 'safe'], ]; }

/**
 * @inheritdoc
 */
public function scenarios()
{
    // bypass scenarios() implementation in the parent class
    return Model::scenarios();
}

/**
 * Creates data provider instance with search query applied
 *
 * @param array $params
 *
 * @return ActiveDataProvider
 */
public function search($params)
{
    $date_now = date('Y-m-d');

    $query = ReservationDb::find();
    $query->andFilterCompare( 'STR_TO_DATE(date)', '$date_now', '>');





    $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([
        'reservation_id' => $this->reservation_id,
        'adult' => $this->adult,
        'child' => $this->child,
        'infat' => $this->infat,
        'phone_num' => $this->phone_num,
    ]);

    $query->andFilterWhere(['like', 'name', $this->name])
        ->andFilterWhere(['like', 'date', $this->date])
        ->andFilterWhere(['like', 'time', $this->time])
        ->andFilterWhere(['like', 'session', $this->session])
        ->andFilterWhere(['like', 'seat_area', $this->seat_area])
        ->andFilterWhere(['like', 'remark', $this->remark])
        ->andFilterWhere(['like', 'payment_status', $this->payment_status])
        ->andFilterWhere(['like', 'reservation_date', $this-
      >reservation_date]);

    return $dataProvider;
}

}

yes, I tried a few way in ReservationSearch. like `$query->andFilterCompare('STR_TO_DATE(date)', '>$date_now');` — Chee Seng Teo, Jun 29 '17 at 04:28
or ` $query = (new \yii\db\Query()) ->select('*') ->from('reservation_db') ->where(['reservation_id' => '457']) ->all(); */` — Chee Seng Teo, Jun 29 '17 at 04:28
even I only try to select ID its return me this error `Call to a member function andFilterWhere() on a non-object` — Chee Seng Teo, Jun 29 '17 at 04:31
Try `$query->andFilterCompare([ 'STR_TO_DATE(date)', $date_now, '>' ])` — Insane Skull, Jun 29 '17 at 04:46
@Insane Skull Thank for your help, but I got this error when I using that code `Missing argument 2 for yii\db\Query::andFilterCompare()` from this function `public function andFilterCompare($name, $value, $defaultOperator = '=')` — Chee Seng Teo, Jun 29 '17 at 05:09
@Insane Skull yes, I got a model call ResercationSearch model and I write these code inside — Chee Seng Teo, Jun 29 '17 at 05:54
I success to run `$query->andFilterCompare([ STR_TO_DATE(date), $date_now, '>' ])` but I got this error `Incorrect parameter count in the call to native function 'STR_TO_DATE' The SQL being executed was: SELECT COUNT(*) FROM reservation_db WHERE STR_TO_DATE(date) > '$date_now'` — Chee Seng Teo, Jun 29 '17 at 05:56

score 0 · Answer 1 · answered Jun 29 '17 at 08:15

0

Try this :

public function search($params)
{
   $date_now = date('Y-m-d');

   $query = ReservationDb::find();
   $query->andFilterCompare('STR_TO_DATE(date)', $date_now, '>');
   .
   .
   .
}

answered Jun 29 '17 at 08:15

Insane Skull

9,220
9
44
63

Hi @Insane Skull here is the error `Incorrect parameter count in the call to native function 'STR_TO_DATE' The SQL being executed was: SELECT COUNT(*) FROM reservation_db WHERE STR_TO_DATE(date) > '2017-06-29'` – Chee Seng Teo Jun 29 '17 at 08:33
I think STR_TO_DATE is not working for select count(*) ? – Chee Seng Teo Jun 29 '17 at 08:35

Yii2 Gii write own sql to filter data

1 Answers1