CODEWITHSUNDEEP
phpyii2
Dmitry
Dmitry

Reputation: 83

How to solve problem with column name in Active Record Yii2?

I have a black list of email domains in db (table black_list_domains with fields id and domain) and I need to validate user's email address while registration.

The validation must add error if user's email matches pattern '%@%domain'.

So, working mysql-query is:

SELECT * FROM `black_list_domains` WHERE '$email' LIKE CONCAT('%@%', domain)"

But when I try to make this checking in my Validator's class with Active Record:

BlackListDomains::find()->where(['like', $email, new \yii\db\Expression("CONCAT('%@%', domain)")])->exists();

it returns a MySQL error Column not found: 1054 Unknown column '[email protected]' in 'where clause' ([email protected] is user's email address).

I can make a correct query with createCommand:

\Yii::$app->db->createCommand("SELECT * FROM `black_list_domains` WHERE '$email' LIKE CONCAT('%@%', domain)")->queryAll();

But I think it is possible to make this with Active Record. Or not?

Upvotes: 0

Views: 439

Answers (1)

rob006
rob006

Reputation: 22144

Second argument in like comparison array is intended to be column name by default. You should probably use expression for the whole condition, to avoid unnecessary escaping of %;

BlackListDomains::find()
    ->where(new \yii\db\Expression(":email LIKE CONCAT('%@%', domain)", [
        'email' => $email,
    ]))
    ->exists();

Upvotes: 2

Related Questions