Blog

Yii provides a set of data provider classes that encapsulate pagination and sorting. A data provider implements yii\data\DataProviderInterface. It supports retrieving sorted and paginated data. Data providers usually work with data widgets.

Yii includes −

• ActiveDataProvider − Uses yii\db\ActiveQuery or yii\db\Query to query data from databases.
• SqlDataProvider − Executes SQL and returns data as arrays.
• ArrayDataProvider − Takes a big array and returns a slice of it.

You define the sorting and pagination behaviors of a data-provider by configuring its pagination and sort properties. Data widgets, such as yii\grid\GridView, have a property called dataProvider, which takes a data provider instance and displays the data on the screen.

Preparing the DB

Step 1 − Create a new database. Database can be prepared in the following two ways.

• In the terminal run mysql -u root –p.
• Create a new database via CREATE DATABASE helloworld CHARACTER SET utf8 COLLATE utf8_general_ci;

Step 2 − Configure the database connection in the config/db.php file. The following configuration is for the system used currently.

<?php
   return [
      'class' => 'yii\db\Connection',
      'dsn' => 'mysql:host = localhost;dbname = helloworld',
      'username' => 'vladimir',
      'password' => '12345',
      'charset' => 'utf8',
   ];
?>

Step 3 − Inside the root folder run ./yii migrate/create test_table. This command will create a database migration for managing our DB. The migration file should appear in the migrations folder of the project root.

Step 4 − Modify the migration file (m160106_163154_test_table.php in this case) this way.

<?php
   use yii\db\Schema;
   use yii\db\Migration;
   class m160106_163154_test_table extends Migration {
      public function safeUp() {
         $this->createTable("user", [
            "id" => Schema::TYPE_PK,
            "name" => Schema::TYPE_STRING,
            "email" => Schema::TYPE_STRING,
         ]);
         $this->batchInsert("user", ["name", "email"], [
            ["User1", "user1@gmail.com"],
            ["User2", "user2@gmail.com"],
            ["User3", "user3@gmail.com"],
            ["User4", "user4@gmail.com"],
            ["User5", "user5@gmail.com"],
            ["User6", "user6@gmail.com"],
            ["User7", "user7@gmail.com"],
            ["User8", "user8@gmail.com"],
            ["User9", "user9@gmail.com"],
            ["User10", "user10@gmail.com"],
            ["User11", "user11@gmail.com"],
         ]);
      }
      public function safeDown() {
         $this->dropTable('user');
      }
   }
?>

The above migration creates a user table with these fields: id, name, and email. It also adds a few demo users.

Step 5 − Inside the project root run ./yii migrate to apply the migration to the database.

Step 6 − Now, we need to create a model for our user table. For the sake of simplicity, we are going to use the Gii code generation tool. Open up this url: http://localhost:8080/index.php?r=gii. Then, click the “Start” button under the “Model generator” header. Fill in the Table Name (“user”) and the Model Class (“MyUser”), click the “Preview” button and finally, click the “Generate” button.

The MyUser model should appear in the models directory.

Active Data Provider

Step 1 − Create a function called actionDataProvider inside the SiteController.

public function actionDataProvider(){
   $query = MyUser::find();
   $provider = new ActiveDataProvider([
      'query' => $query,
      'pagination' => [
         'pageSize' => 2,
      ],
   ]);
   // returns an array of users objects
   $users = $provider->getModels();
   var_dump($users);
}

In the code above, we define an instance of the ActiveDataProvider class and display users from the first page. The yii\data\ActiveDataProvider class uses the DB application component as the DB connection.

Step 2 − If you enter the local host address http://localhost:8080/index.php?r=site/dataprovider, you will see the following output.

SQL Data Provider

The yii\data\SqlDataProvider class works with raw SQL statements.

Step 1 − Modify the actionDataProvider method this way.

public function actionDataProvider() {
   $count = Yii::$app->db->createCommand('SELECT COUNT(*) FROM user')->queryScalar();
   $provider = new SqlDataProvider([
      'sql' => 'SELECT * FROM user',
      'totalCount' => $count,
      'pagination' => [
         'pageSize' => 5,
      ],
      'sort' => [
         'attributes' => [
            'id',
            'name',
            'email',
         ],
      ],
   ]);
   // returns an array of data rows
   $users = $provider->getModels();
   var_dump($users);
}

Step 2 − Type http://localhost:8080/index.php?r=site/data-provider in the address bar of the web browser, you will see the following output.

Array Data Provider

The yii\data\ArrayDataProvider class is best for working with big arrays. Elements in this array can be either query results of DAO or Active Record instances.

Step 1 − Modify the actionDataProvider method this way.

public function actionDataProvider() {
   $data = MyUser::find()->asArray()->all();
   $provider = new ArrayDataProvider([
      'allModels' => $data,
      'pagination' => [
         'pageSize' => 3,
      ],
      'sort' => [
         'attributes' => ['id', 'name'],
      ],
   ]);
   // get the rows in the currently requested page
   $users = $provider->getModels();
   var_dump($users);
}

Step 2 − If you go to the address http://localhost:8080/index.php?r=site/data-provider through the web browser, you will see the following output.

Notice, that unlike SQL Data Provider and Active Data Provider, Array Data Provider loads all data into the memory, so it is less efficient.