Building a "LIKE" query in Yii2

Question

This code makes an SQL query. The last andWhere() method adds a LIKE condition with one percentage sign at the right side of the string.

I'm wondering if there is a better way to do it.

public function prepareDataProvider()
{
    /* @var $modelClass \yii\db\ActiveRecord */
    $modelClass = $this->modelClass;

    $query = $modelClass::find()
        ->where('published=1')
        ->select([
            'id',
            'city_id',
            'city_district_id',
            'seller_net_id',
            'name',
            'address',
            'type',
        ]);

    // filters
    if ($city_id = \Yii::$app->request->getQueryParam('city_id'))
        $query->andWhere('city_id = :city_id',[':city_id'=>$city_id]);
    if ($city_district_id = \Yii::$app->request->getQueryParam('city_district_id'))
        $query->andWhere('city_district_id = :city_district_id',[':city_district_id'=>$city_district_id]);
    $city_name = \Yii::$app->request->getQueryParam('name');
    if ($city_name && mb_strlen($city_name) >= \Yii::$app->params['minSearchChars'])
        $query->andWhere(['LIKE' ,'name',strtr($city_name,['%'=>'\%', '_'=>'\_', '\\'=>'\\\\']).'%', false]);

    return new ActiveDataProvider([
        'query' => $query
    ]);
}

Answer 1

The question

The last andWhere() method adds a LIKE condition with one percentage sign at the right. I'm wondering if there a better way to do it.

Hmm... let us look at that last addWhere() line:

$query->andWhere(['LIKE' ,'name',strtr($city_name,['%'=>'\%', '_'=>'\_', '\\'=>'\\\\']).'%', false]);

That is quite a long line - and appears to have manual character escaping, which isn't a bad thing but not something you want to handle manually if you can help it. Since you are using bound parameters for the first two parameters you likely know that they can help prevent SQL injection attacks and it would be wise to find a way to bind a parameter for that last where condition.

As this StackOverflow answer suggests, a bound parameter could still be used with the LIKE condition - then you might not need to escape as many characters:

$query->andWhere(new \yii\db\Expression('name LIKE :city_name', [':city_name' => $city_name . '%']));

But if the presence of certain characters like % in the city name still present a problem then continue to escape them

Other feedback

The code is very easy to read. Perhaps it was written to be compliant with PSR-2, which is now deprecated¹, though it mostly is compliant with PSR-12. I like the fact that the short array syntax is used- I wish I had started doing that back then!

I see it contains assignment within the conditionals - e.g.

    if ($city_id = \Yii::$app->request->getQueryParam('city_id'))

While it doesn't take much to notice that the variables are later used within the statement that follows, it might be nice to help readers with a comment above the group that says something like intentional assignment.