Monday, 29 August 2016

How to perform database operations in yii2.

Build a yii\db\Query object to represent different parts (e.g. SELECT, FROM) of a SELECT SQL statement.Execute a query method (e.g. all()) of yii\db\Query to retrieve data from the database. This is used for directly using a query without any model relation.



$rows = (new \yii\db\Query())
    ->select(['id', 'email'])
    ->from('user')
    ->where(['last_name' => 'sharma'])
    ->limit(10)
    ->all();

The Yii Query Builder provides an object-oriented way of writing SQL statements. It allows developers to use class methods and properties to specify individual parts of a SQL statement.

Creating a object of query builer like:

$connection = Yii::$app->db;

Raw SQL:

$connection->createCommand('update user set status = 1 where age > 30')->execute();

Raw SQL with placeholders (to prevent SQL injection):

$connection->createCommand('update user set status = :status where age > 30')->bindValue(':status','1')->execute();

update() method:

// update user set status = 1 where age > 30
$connection->createCommand()->update('user', ['status' => 1], 'age > 30')->execute();

batchInsert() records:


$connection->createCommand()->batchInsert('tbl_user', ['name', 'status'], [['A', 1], ['B', 0], ['C', 1], ])->execute();

This is the direct method of accessing the query using query builer but apart from this you can create per table model for more superior way to manage your relation with the table and query are also simple. links for db query using model click here.

Featured post

How to create dynamic schema in mongodb

Sometime what we need in our application is to create mongodb schema for collection and some of our collection schema are same but names are...

Popular Posts