How to Use Yii 2'S ActiveRecord For Database Interactions?

12 minutes read

Yii 2's ActiveRecord is a powerful feature that facilitates easy database interactions in your application. Here's an overview of how you can use Yii 2's ActiveRecord for database operations:

  1. Define a Model: ActiveRecord uses models to represent database tables. Create a model class that extends the yii\db\ActiveRecord base class. Specify the table name, primary key, and attributes in the model class.
  2. Configure Database Connection: Yii 2 provides a configuration file (config/db.php) where you can define your database connection details. Configure the database connection by specifying the necessary parameters like database type, host, username, and password.
  3. Querying Data: ActiveRecord allows you to perform various types of queries on the database. For example, to fetch all records from a table, you can use:
1
$records = YourModel::find()->all();


  1. Building Queries: Yii 2 provides a fluent query building interface to construct complex queries. You can use methods like where(), andWhere(), and orderBy() to add conditions and ordering to your queries. For example:
1
2
$query = YourModel::find()->where(['column1' => $value])->orderBy('column2');
$records = $query->all();


  1. Inserting Data: To insert a new record into the database, create a new instance of your model class, assign values to its attributes, and call the save() method. For example:
1
2
3
4
$model = new YourModel();
$model->attribute1 = $value1;
$model->attribute2 = $value2;
$model->save();


  1. Updating Data: To update an existing record, fetch it from the database using find() or similar methods, modify its attributes, and call the save() method. For example:
1
2
3
$model = YourModel::findOne($id);
$model->attribute1 = $newValue;
$model->save();


  1. Deleting Data: To delete records, you can use the delete() method. For example, to delete a single record by its ID:
1
2
$model = YourModel::findOne($id);
$model->delete();


  1. Validations: Yii 2's ActiveRecord includes powerful validation capabilities. You can define validation rules in your model class, and ActiveRecord will automatically validate your data before saving it to the database.


These are just some of the basic concepts of using Yii 2's ActiveRecord for database interactions. The framework provides many more advanced features and options to streamline your database operations and make your development process efficient.

Best Yii 2 Frameworks Books to Read in 2024

1
Yii 2 Development: Bring A Map Through The Halls Of Yii 2 Development

Rating is 5 out of 5

Yii 2 Development: Bring A Map Through The Halls Of Yii 2 Development

2
Yii2 Quick Start Guide - Mastering Yii 2

Rating is 4.9 out of 5

Yii2 Quick Start Guide - Mastering Yii 2

3
Yii 2 Speed: Getting Up To Speed With Yii 2

Rating is 4.8 out of 5

Yii 2 Speed: Getting Up To Speed With Yii 2


How to delete a record using Yii 2's ActiveRecord?

To delete a record using Yii 2's ActiveRecord, you can follow these steps:

  1. Fetch the record you want to delete using a query or other methods.
  2. Call the delete() function on the fetched record to delete it.


Here is an example of how to delete a record using ActiveRecord:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
// Find the record by its primary key or by other criteria
$record = YourModel::findOne($id);

// Check if the record exists
if ($record !== null) {
    // Delete the record
    $record->delete();
} else {
    echo "Record not found.";
}


Make sure to replace YourModel with the actual name of your ActiveRecord model class.


You can also delete records using a query directly without fetching them first. Here's an example:

1
2
// Delete records that match certain conditions using a query
YourModel::deleteAll(['condition' => 'value']);


Replace 'condition' and 'value' with the actual conditions you want to use for deleting records.


Note: Deleting a record using delete() just flags the record as deleted, but does not remove it from the database immediately. The record will be permanently deleted when you call the save() function after calling delete().


How to create a new record using Yii 2's ActiveRecord?

To create a new record using Yii 2's ActiveRecord, follow these steps:

  1. Create a new instance of your ActiveRecord model class:
1
$model = new YourModel();


  1. Assign values to the attributes of the model that you want to create:
1
2
3
$model->attribute1 = $value1;
$model->attribute2 = $value2;
// Assign values to other attributes


  1. Call the save() method on the model to save it to the database:
1
2
3
4
5
if ($model->save()) {
    // Record saved successfully, perform any additional actions
} else {
    // Error occurred while saving the record, handle the error
}


The save() method will return true if the record is saved successfully. If there are any errors during the saving process, it will return false. You can access the errors using the errors property of the model:

1
$errors = $model->errors;


You can also use the create() method, which creates a new record and saves it to the database in a single step. The create() method accepts an array of attribute values as its parameter:

1
2
3
4
5
$model = YourModel::create([
    'attribute1' => $value1,
    'attribute2' => $value2,
    // Assign values to other attributes
]);


Note that the create method requires the attributes to be included in the model's safeAttributes() method or be marked as safe in your model's validation rules.


Make sure that you have properly defined the database connection in your Yii 2 application's configuration file.


How to perform a raw SQL query using Yii 2's ActiveRecord?

To perform a raw SQL query using Yii 2's ActiveRecord, you can use the yii\db\Query class or the yii\db\Command class. Here are the steps to do it:

  1. Create an instance of the Query class or the Command class:
1
2
3
4
5
6
7
use yii\db\Query;
// or
use yii\db\Command;

$query = new Query();
// or
$command = Yii::$app->db->createCommand();


  1. Compose your raw SQL query and assign it to the sql property of the Query or Command object:
1
2
3
$query->sql = 'SELECT * FROM your_table WHERE condition = :value';
// or
$command->sql = 'SELECT * FROM your_table WHERE condition = :value';


  1. If your query requires user input, bind the parameter(s) using the bindValue method:
1
2
3
$query->bindValue(':value', $value);
// or
$command->bindValue(':value', $value);


  1. Execute the query by calling the one, all, or execute method:
1
2
3
4
5
$result = $query->one(); // fetches a single row
// or
$result = $query->all(); // fetches all rows
// or
$result = $command->execute(); // executes the query


  1. Process and use the result(s) as desired.


Note: If you need to perform complex SQL operations, it is recommended to use the Query class, as it provides more advanced querying capabilities. However, if you only need to execute a simple SQL statement, the Command class is sufficient.

Best Yii 2 Cloud Hosting Providers in 2024

1
DigitalOcean

Rating is 5 out of 5

DigitalOcean

2
AWS

Rating is 4.9 out of 5

AWS

3
Vultr

Rating is 4.8 out of 5

Vultr

4
Cloudways

Rating is 4.7 out of 5

Cloudways


What is the use of scopes in Yii 2's ActiveRecord?

Scopes in Yii 2's ActiveRecord are used to define reusable query conditions that can be easily applied to a model's database query.


A scope is defined as a public static method in a model class, and it returns a query object that can be further modified or executed. Scopes can include conditions, such as WHERE clauses, and can also include additional joins or orderings.


By using scopes, developers can define query conditions once and reuse them in different parts of the application, which promotes reusability and reduces code duplication. Scopes can also be chained together to build more complex queries. For example, if a model has a "published" scope and a "recent" scope, these can be combined to retrieve only the recent published records.


Here is an example of how a scope is defined in a Yii 2's ActiveRecord model class:

1
2
3
4
5
6
7
8
9
class Post extends \yii\db\ActiveRecord
{
    // ...
    
    public static function published()
    {
        return static::find()->where(['status' => 'published']);
    }
}


And here is an example of how a scope can be used:

1
$recentPublishedPosts = Post::published()->andWhere(['>', 'published_at', time() - 7 * 24 * 60 * 60])->all();


In this example, the "published" scope is used to retrieve all the recently published posts by adding an additional condition to the scope.


Scopes provide a powerful and convenient way to encapsulate reusable query conditions in Yii 2's ActiveRecord, enhancing code organization and reusability.


What is the purpose of the createCommand() method in Yii 2's ActiveRecord?

The createCommand() method in Yii 2's ActiveRecord is used to create a database command that can be executed to perform various database operations. This method allows you to programmatically create custom SQL queries and execute them on the database associated with the ActiveRecord model.


The purpose of createCommand() is to provide a flexible way of executing SQL statements that may not be easily achievable using the built-in methods in the ActiveRecord class. It allows you to directly interact with the database and execute complex queries or perform database operations that are not supported by the ActiveRecord methods.


For example, you can use the createCommand() method to execute raw SQL queries, perform database updates, deletions, insertions, or execute stored procedures.


Here's an example of how createCommand() can be used:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
use yii\db\Connection;
use yii\db\Command;

// Get the database connection
$db = new Connection([
    'dsn' => 'mysql:host=localhost;dbname=mydatabase',
    'username' => 'root',
    'password' => '',
]);

// Create a command object
$command = $db->createCommand('SELECT * FROM mytable');

// Execute the command and fetch the results
$results = $command->queryAll();

// Perform other operations using the command object
$command->insert('mytable', ['column1' => 'value1', 'column2' => 'value2']);


In this example, the createCommand() method is used to create a command object that executes a SELECT query on the 'mytable' table. The queryAll() method is then used to fetch the results of the query. Additionally, the command object is used to insert new records into the 'mytable' table using the insert() method.


Overall, the createCommand() method provides a way to execute custom SQL queries and perform database operations directly on the database associated with the ActiveRecord model.

Facebook Twitter LinkedIn Telegram

Related Posts:

In Yii 2, handling forms and validations is made easy and efficient with the help of built-in features and components. Here is an overview of how you can handle forms and validations in Yii 2:Model Creation: Begin by creating a model that represents the data y...
To install Yii 2 framework, follow these steps:Ensure that your system meets the minimum requirements for Yii 2. These include PHP 5.4 or later and various PHP extensions such as PDO, OpenSSL, and Mbstring. Download the latest version of Yii 2 from the officia...
To set up and configure a database connection in Yii 2, follow these steps:Open the config/db.php file in your Yii 2 application's root directory. Inside this file, define a new array with the database connection configuration. The basic structure of the a...