SqlDataProvider filter

Question

I'm struggling to work out how to use a filter in my grid when it's generated from an SqlDataProvider.

Currently my model works like so:

 $sql = 'SELECT * FROM my_table';
 $provider = new SqlDataProvider([
       'sql' => $sql,
       'params' => [':start' => $param1, ':end' => $param2],
       'totalCount' => $count,
       'pagination' => [
         'pageSize' => 100
        ],
     ]);
return $provider;

When there isn't an active record how should this be done? The search models seem to all be based of AR and calling in the controller:

$searchModel->search(Yii::$app->request->queryParams);

Where $searchModel function begins like this:

  public function search($params)
 {
    $query = myTable::find();

    // add conditions that should always apply here

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

I have started to change the search() function to be more like this, but not sure I'm going about it the right way:

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

    // add conditions that should always apply here

    $dataProvider = new SqlDataProvider([
      'sql' => $query,
      'pagination' => [
        'pageSize' => 100
       ],
    ]);

As it errors with

preg_match() expects parameter 2 to be string, object given

marche · Accepted Answer · 2016-11-30 19:15:27Z

2

For the SqlDataProvider you need to provide a SQL string. Here you are giving a Query Object, but you can get the SQL like this:

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

// add conditions that should always apply here

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

answered Nov 30, 2016 at 19:15

marche

1,7561 gold badge14 silver badges24 bronze badges

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

1 Comment

Jonnny Over a year ago

Thanks, I've just figured this out

ScaisEdge · Accepted Answer · 2016-11-30 18:52:49Z

For an activeRecord (generated by gii) realetd to a model you have a modelSearch.php tha containt a search function

In this case is a user model serach

 /**
 * @param $params
 * @return ActiveDataProvider
 */
public function search($params)
{
    $query = $this->finder->getUserQuery();

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

    if (!($this->load($params) && $this->validate())) {
        return $dataProvider;
    }

    if ($this->created_at !== null) {
        $date = strtotime($this->created_at);
        $query->andFilterWhere(['between', 'created_at', $date, $date + 3600 * 24]);
    }

    $query->andFilterWhere(['like', 'username', $this->username])
        ->andFilterWhere(['like', 'email', $this->email])
        ->andFilterWhere(['registration_ip' => $this->registration_ip]);

    return $dataProvider;
}

the filter capabilities are performed with a proper combination of

 $query->andFilterWhere(....)

each of this related to the proper column name and param var eg:

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

this if that if you need a similar behavior for you model or you dataProvider you could repeat an similar pattern

$query = new Query;
$query->select('*')
      ->from('my_table');
$query->andFilterWhere('start', $param1);

Collectives™ on Stack Overflow

SqlDataProvider filter

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related