CODEWITHSUNDEEP
mysqlsqlyii2
pioneer
pioneer

Reputation: 67

How to create & group OR LIKE query with multiple columns in Yii2?

How I can create query in Yii2 like this with query builder?

SELECT * 
FROM blog 
WHERE status = 1 AND (
    author_username LIKE '%Steve%' 
    OR author_first_name LIKE '%Steve%' 
    OR author_last_name LIKE '%Steve%'
)

I'm trying to find right way to use it in the search model.

Upvotes: 2

Views: 1446

Answers (2)

Kshitij Verma
Kshitij Verma

Reputation: 697

In yii2 you can use Yii::$app->db->createCommand to run your mysql query.

1. For SELECT operation:

1.1 Get all records:

Yii::$app->db->createCommand($mysqlquery)->queryAll();

1.2 Get Single record

Yii::$app->db->createCommand($mysqlquery)->queryOne();

2. For UPDATE/DELETE

Yii::$app->db->createCommand($mysqlquery)->execute();

Upvotes: 0

mmta41
mmta41

Reputation: 372

There are many ways to achieve this query:

  1. Using PDO:

    $q='Stev';
Yii::$app->db->createCommand('
    SELECT * 
    FROM blog 
    WHERE status = 1 AND (
        author_username LIKE :Q 
        OR author_first_name LIKE :Q 
        OR author_last_name LIKE :Q
    )
    ',['Q'=>"%$q%"])
    ->queryAll();

  2. Using yii\db\Query:

    $condition = [
    'OR',                               //Operand (C1 OR C2)
    [                                   //Condition1 (C1) which is complex
        'OR',                           //Operand (C1.1 OR C1.2)
        ['like','author_username',$q],  //C1.1
        ['like','author_first_name',$q] //C1.2
    ],
    ['like','author_last_name',$q]      //Condition2(C2)
];

(new yii\db\Query())
    ->from('blog')
    ->andWhere('status = 1')
    ->andWhere($condition)
    ->all();

  3. Using ActiveRecord (ActiveQuery):

    Blog::find()
->active()
->andWhere($condition)
->all();

you can test your query using QueryBuilder:

var_dump(Yii::$app->db->queryBuilder->build($your_query));

Upvotes: 5

Related Questions