CODEWITHSUNDEEP
mysqlyii2
Vadim
Vadim

Reputation: 73

How to put SQL request in QueryBuilder and avoid ``?

I have this SQL request in MySQL 

SELECT * FROM pt WHERE id=98 ORDER BY FIELD (position, 4, 3, 2, 1, 5)

and I need to make a query in Yii2. When I write

 'query' => Pt::find()->where(['id' => $model->id])
        ->OrderBy('FIELD (`position`, 4, 3, 2, 1, 5)')

I receive

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 'LIMIT 20' at line 1
The SQL being executed was: SELECT * FROM `pt` WHERE `id`=98 ORDER BY FIELD (`position`, `4`, `3`, `2`, `1`, `5)` LIMIT 20

How to avoid `` in request?

Upvotes: 0

Views: 50

Answers (1)

Yerke
Yerke

Reputation: 2235

Use yii\db\Expression class as it'll help you to insert RAW code (without formatting it in generated queries), but be cautious! Do not insert user input as RAW in DB queries, being that it leads to severe vulnerabilities.

The working code looks as follow:

'query' => Pt::find()->where(['id' => $model->id])
        ->orderBy(new \yii\db\Expression('FIELD (`position`, 4, 3, 2, 1, 5)'))

Upvotes: 2

Related Questions