# Working with Databases, Database Migration Full (ok)

## 1. Creating Migrations <a href="#database-migration" id="database-migration"></a>

{% embed url="<https://www.yiiframework.com/doc/guide/2.0/en/db-migrations>" %}

### [Create table](https://www.yiiframework.com/doc/guide/2.0/en/db-migrations)

```php
yii migrate/create create_news_table
```

### 2. [Create Model](https://pdo.gitbook.io/lear-yii/generating-code-with-gii)

<figure><img src="/files/2Tekh4n27RUh8WvIvJ48" alt=""><figcaption></figcaption></figure>

<figure><img src="/files/xepzYFa5sLQRywcG5VI1" alt=""><figcaption></figcaption></figure>

Hoặc chúng ta có thể sử dụng dòng lệnh cmd

```
./yii gii/model --modelClass=Client --tableName=user
```

<figure><img src="/files/Nryci4tWkzLIzjEm0Gnx" alt=""><figcaption></figcaption></figure>

### 3. Create Migrate Database

<figure><img src="/files/MHGFVGk1TbgDgdNFLy5I" alt=""><figcaption></figcaption></figure>

C:\xampp72\htdocs\testcom\console\migrations\m130524\_201442\_init.php

```php
<?php
use common\models\User;
class m130524_201442_init extends \yii\db\Migration
{
  public function up()
  {
    $tableOptions = null;
    if ($this->db->driverName === 'mysql') {
      $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=InnoDB';
    }
    $this->createTable(User::tableName(), [
      'id' => $this->primaryKey(),
      'username' => $this->string()->notNull(),
      'auth_key' => $this->string(32)->notNull(),
      'password_hash' => $this->string()->notNull(),
      'password_reset_token' => $this->string(),
      'email' => $this->string()->notNull(),
      'role' => $this->smallInteger()->notNull()->defaultValue(10),
      'status' => $this->smallInteger()->notNull()->defaultValue(10),
      'created_at' => $this->integer()->notNull(),
      'updated_at' => $this->integer()->notNull(),
    ], $tableOptions);
  }
  public function down()
  {
    $this->dropTable(User::tableName());
  }
}

```

C:\xampp72\htdocs\testcom\console\migrations\m230321\_083107\_default\_user.php

```php
<?php
use common\models\User;
use yii\db\Migration;
/**
 * Class m230321_083107_default_user
 */
class m230321_083107_default_user extends Migration
{
  public function up()
  {
    $this->insert(User::tableName(), [
      'username' => 'demoadmin',
      'auth_key' => 'Jg6O-7Sho1sxY38OgTcx3RTX30VUlXTi',
      'password_hash' => '$2y$13$MKjLOsF/qyONMpwqhOe99ufFNK.3f8amFf5lB27/4zD9F1Xj4EiVy',
      'email' => 'admin@localhost.local',
      'role' => '100',
      'status' => '10',
      'created_at' => time(),
      'updated_at' => time()
    ]);
  }
  public function down()
  {
    $this->delete(User::tableName(), 'username = "demoadmin"');
  }
}

```

Kết quả:

<figure><img src="/files/U8uCeZFVJ2gyrt4lVMXi" alt=""><figcaption></figcaption></figure>

<figure><img src="/files/nY67POVOHR3oYMcwZUDU" alt=""><figcaption></figcaption></figure>

### 4. 1. Create a table post

<figure><img src="/files/igEDb3IMJeLb9GCQJ3Po" alt=""><figcaption></figcaption></figure>

C:\xampp72\htdocs\testvn\common\models\Post.php

```php
<?php
namespace common\models;
use Yii;
use yii\data\ActiveDataProvider;
use yii\db\ActiveQuery;
use yii\db\ActiveRecord;
use yii\helpers\ArrayHelper;
use yii\web\NotFoundHttpException;
/**
 * Post model.
 *
 * @property string $id
 * @property string $title
 * @property string $anons
 * @property string $content
 * @property string $category_id
 * @property string $author_id
 * @property string $publish_status
 * @property string $publish_date
 *
 * @property User $author
 * @property Category $category
 * @property Comment[] $comments
 */
class Post extends ActiveRecord
{
  public const STATUS_PUBLISH = 'publish';
  public const STATUS_DRAFT = 'draft';
  /**
   * Tag list
   * @var array
   */
  protected $tags = [];
  /**
   * @inheritdoc
   */
  public static function tableName(): string
  {
    return '{{%post}}';
  }
  /**
   * @inheritdoc
   */
  public function rules(): array
  {
    return [
      [['title'], 'required'],
      [['category_id', 'author_id'], 'integer'],
      [['anons', 'content', 'publish_status'], 'string'],
      [['publish_date', 'tags'], 'safe'],
      [['title'], 'string', 'max' => 255]
    ];
  }
  /**
   * @inheritdoc
   */
  public function attributeLabels(): array
  {
    return [
      'id' => Yii::t('backend', 'ID'),
      'title' => Yii::t('backend', 'Title'),
      'anons' => Yii::t('backend', 'Announce'),
      'content' => Yii::t('backend', 'Content'),
      'category' => Yii::t('backend', 'Category'),
      'tags' => Yii::t('backend', 'Tags'),
      'category_id' => Yii::t('backend', 'Category ID'),
      'author' => Yii::t('backend', 'Author'),
      'author_id' => Yii::t('backend', 'Author ID'),
      'publish_status' => Yii::t('backend', 'Publish status'),
      'publish_date' => Yii::t('backend', 'Publish date'),
    ];
  }
  public function getAuthor(): ActiveQuery
  {
    return $this->hasOne(User::class, ['id' => 'author_id']);
  }
  public function getCategory(): ActiveQuery
  {
    return $this->hasOne(Category::class, ['id' => 'category_id']);
  }
  public function getComments(): ActiveQuery
  {
    return $this->hasMany(Comment::class, ['post_id' => 'id']);
  }
  public function getPublishedComments(): ActiveDataProvider
  {
    return new ActiveDataProvider([
      'query' => $this->getComments()
        ->where(['publish_status' => Comment::STATUS_PUBLISH])
    ]);
  }
  public function setTags(array $tagsId): void
  {
    $this->tags = $tagsId;
  }
  /**
   * Return tag ids
   */
  public function getTags(): array
  {
    return ArrayHelper::getColumn(
      $this->getTagPost()->all(),
      'tag_id'
    );
  }
  /**
   * Return tags for post
   *
   * @return ActiveQuery
   */
  public function getTagPost(): ActiveQuery
  {
    return $this->hasMany(
      TagPost::class,
      ['post_id' => 'id']
    );
  }
  public static function findPublished(): ActiveDataProvider
  {
    return new ActiveDataProvider([
      'query' => Post::find()
        ->where(['publish_status' => self::STATUS_PUBLISH])
        ->orderBy(['publish_date' => SORT_DESC])
    ]);
  }
  /**
   * @throws NotFoundHttpException
   */
  public static function findById(int $id, bool $ignorePublishStatus = false): Post
  {
    if (($model = Post::findOne($id)) !== null) {
      if ($model->isPublished() || $ignorePublishStatus) {
        return $model;
      }
    }
    throw new NotFoundHttpException('The requested post does not exist.');
  }
  /**
   * @inheritdoc
   */
  public function afterSave($insert, $changedAttributes)
  {
    TagPost::deleteAll(['post_id' => $this->id]);
    if (is_array($this->tags) && !empty($this->tags)) {
      $values = [];
      foreach ($this->tags as $id) {
        $values[] = [$this->id, $id];
      }
      self::getDb()->createCommand()
        ->batchInsert(TagPost::tableName(), ['post_id', 'tag_id'], $values)->execute();
    }
    parent::afterSave($insert, $changedAttributes);
  }
  protected function isPublished(): bool
  {
    return $this->publish_status === self::STATUS_PUBLISH;
  }
}

```

C:\xampp72\htdocs\testcom\console\migrations\m230321\_084018\_post.php

```php
<?php
use common\models\Post;
use common\models\User;
use yii\db\Migration;
/**
 * Class m230321_084018_post
 */
class m230321_084018_post extends Migration
{
  /**
   * {@inheritdoc}
   */
  public function safeUp()
  {
    $tableOptions = null;
    if ($this->db->driverName === 'mysql') {
      $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=InnoDB';
    }
    $this->createTable(Post::tableName(), [
      'id' => $this->primaryKey(),
      'title' => $this->string()->notNull(),
      'anons' => $this->text()->notNull(),
      'content' => $this->text()->notNull(),
      'category_id' => $this->integer(),
      'author_id' => $this->integer(),
      'publish_status' => "enum('" . Post::STATUS_DRAFT . "','" . Post::STATUS_PUBLISH . "') NOT NULL DEFAULT '" . Post::STATUS_DRAFT . "'",
      'publish_date' => $this->timestamp()->notNull(),
    ], $tableOptions);
  }
  /**
   * {@inheritdoc}
   */
  public function safeDown()
  {
    $this->dropTable(Post::tableName());
  }
  /*
    // Use up()/down() to run migration code without a transaction.
    public function up()
    {
    }
    public function down()
    {
        echo "m230321_084018_post cannot be reverted.\n";
        return false;
    }
    */
}

```

Kết quả:

<figure><img src="/files/4mgLCj6dbduw8wzVkFUp" alt=""><figcaption></figcaption></figure>

<figure><img src="/files/dKZuwEEhrYKHVmzaBZfX" alt=""><figcaption></figcaption></figure>

### 4. 2. Create a table category

Lợi dụng bảng đã có sẵn use ta khai báo gii như sau để tạo 1 model

<figure><img src="/files/1piepGxyh1E9qKCLLNWu" alt=""><figcaption></figcaption></figure>

Rồi sau đó thay đổi attribute theo ý mình

C:\xampp72\htdocs\testcom\common\models\Category.php

```php
<?php
namespace common\models;
use Yii;
/**
 * This is the model class for table "user".
 *
 * @property int $id
 * @property string $username
 * @property string $auth_key
 * @property string $password_hash
 * @property string|null $password_reset_token
 * @property string $email
 * @property int $role
 * @property int $status
 * @property int $created_at
 * @property int $updated_at
 */
class Category extends \yii\db\ActiveRecord
{
  /**
   * {@inheritdoc}
   */
  public static function tableName()
  {
    return '{{%category}}';
  }
  /**
   * {@inheritdoc}
   */
  public function rules()
  {
    return [
      [['title'], 'required'],
      [['title'], 'string', 'max' => 255]
    ];
  }
  /**
   * {@inheritdoc}
   */
  public function attributeLabels()
  {
    return [
      'id' => Yii::t('backend', 'ID'),
      'title' => Yii::t('backend', 'Title'),
    ];
  }
}

```

<figure><img src="/files/baqgBGhPoQFObFDooaYx" alt=""><figcaption></figcaption></figure>

C:\xampp72\htdocs\testcom\console\migrations\m230321\_085655\_category.php

```php
<?php
use common\models\Category;
use yii\db\Migration;
/**
 * Class m230321_085655_category
 */
class m230321_085655_category extends Migration
{
  /**
   * {@inheritdoc}
   */
  public function safeUp()
  {
    $tableOptions = null;
    if ($this->db->driverName === 'mysql') {
      $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=InnoDB';
    }
    $this->createTable(Category::tableName(), [
      'id' => $this->primaryKey(),
      'title' => $this->string()->notNull(),
    ], $tableOptions);
  }
  /**
   * {@inheritdoc}
   */
  public function safeDown()
  {
    $this->dropTable(Category::tableName());
  }
  /*
    // Use up()/down() to run migration code without a transaction.
    public function up()
    {
    }
    public function down()
    {
        echo "m230321_085655_category cannot be reverted.\n";
        return false;
    }
    */
}

```

Kết quả:

<figure><img src="/files/S3z8rFAgzNwm2yWjD3x3" alt=""><figcaption></figcaption></figure>

### 4. 3. Create a table comments

<figure><img src="/files/VltRBNdogkUisD0hSgln" alt=""><figcaption></figcaption></figure>

C:\xampp72\htdocs\testcom\console\migrations\m230321\_091626\_comments.php

```php
<?php
use common\models\Comment;
use yii\db\Migration;
/**
 * Class m230321_091626_comments
 */
class m230321_091626_comments extends Migration
{
  /**
   * {@inheritdoc}
   */
  public function safeUp()
  {
    $tableOptions = null;
    if ($this->db->driverName === 'mysql') {
      $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=InnoDB';
    }
    $this->createTable(Comment::tableName(), [
      'id' => $this->primaryKey(),
      'pid' => $this->integer(),
      'title' => $this->string()->notNull(),
      'content' => $this->string()->notNull(),
      'publish_status' => "enum('" . Comment::STATUS_MODERATE . "','" . Comment::STATUS_PUBLISH . "') NOT NULL DEFAULT '" . Comment::STATUS_MODERATE . "'",
      'post_id' => $this->integer(),
      'author_id' => $this->integer()
    ], $tableOptions);
  }
  /**
   * {@inheritdoc}
   */
  public function safeDown()
  {
    $this->dropTable(Comment::tableName());
  }
  /*
    // Use up()/down() to run migration code without a transaction.
    public function up()
    {
    }
    public function down()
    {
        echo "m230321_091626_comments cannot be reverted.\n";
        return false;
    }
    */
}

```

C:\xampp72\htdocs\testcom\common\models\Comment.php

```php
<?php
namespace common\models;
use Yii;
/**
 * This is the model class for table "user".
 *
 * @property int $id
 * @property string $username
 * @property string $auth_key
 * @property string $password_hash
 * @property string|null $password_reset_token
 * @property string $email
 * @property int $role
 * @property int $status
 * @property int $created_at
 * @property int $updated_at
 */
class Comment extends \yii\db\ActiveRecord
{
  public const STATUS_MODERATE = 'moderate';
  public const STATUS_PUBLISH = 'publish';
  /**
   * @inheritdoc
   */
  public static function tableName(): string
  {
    return '{{%comment}}';
  }
  /**
   * {@inheritdoc}
   */
  public function rules(): array
  {
    return [
      [['pid', 'post_id', 'author_id'], 'integer'],
      [['title', 'content'], 'required'],
      [['publish_status'], 'string'],
      [['title', 'content'], 'string', 'max' => 255]
    ];
  }
  /**
   * {@inheritdoc}
   */
  public function attributeLabels(): array
  {
    return [
      'id' => Yii::t('backend', 'ID'),
      'pid' => Yii::t('backend', 'Pid'),
      'title' => Yii::t('backend', 'Title'),
      'content' => Yii::t('backend', 'Content'),
      'publish_status' => Yii::t('backend', 'Publish status'),
      'post_id' => Yii::t('backend', 'Post ID'),
      'author_id' => Yii::t('backend', 'Author ID'),
    ];
  }
}

```

Kết quả đạt được

<figure><img src="/files/8LNdITW5X11uomDSOfhP" alt=""><figcaption></figcaption></figure>

<figure><img src="/files/pdb5B4iG5Jk2m16aDisF" alt=""><figcaption></figcaption></figure>

### 4. 4. Create a table tag

<figure><img src="/files/vf1pIbgdudWoLmR9JZbF" alt=""><figcaption></figcaption></figure>

<figure><img src="/files/hPPyREq8L42GOTOqJZrG" alt=""><figcaption></figcaption></figure>

<figure><img src="/files/JvY3vPQ02Cju8LYx3h0u" alt=""><figcaption></figcaption></figure>

<figure><img src="/files/FLluewnkMFLRp048nhbq" alt=""><figcaption></figcaption></figure>

Kết quả:

<figure><img src="/files/TVQh3L1HMIkdAixn03RR" alt=""><figcaption></figcaption></figure>

<figure><img src="/files/Gvb7WCOZ3NtQE6rOwkJ1" alt=""><figcaption></figcaption></figure>

<figure><img src="/files/vZ8ooHkSRVS65A9RClpv" alt=""><figcaption></figcaption></figure>

### 4. 5. Create a table tag\_post

<figure><img src="/files/GZyUUAGc8oCPzaCQkRtB" alt=""><figcaption></figcaption></figure>

<figure><img src="/files/5fuwirWwpyBHoG9pctUv" alt=""><figcaption></figcaption></figure>

C:\xampp72\htdocs\testcom\console\migrations\m230321\_093310\_tag\_post.php

```php
<?php
use common\models\TagPost;
use yii\db\Migration;
/**
 * Class m230321_093310_tag_post
 */
class m230321_093310_tag_post extends Migration
{
  /**
   * {@inheritdoc}
   */
  public function safeUp()
  {
    $tableOptions = null;
    if ($this->db->driverName === 'mysql') {
      $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=InnoDB';
    }
    $this->createTable(TagPost::tableName(), [
      'tag_id' => $this->integer(),
      'post_id' => $this->integer()
    ], $tableOptions);
  }
  /**
   * {@inheritdoc}
   */
  public function safeDown()
  {
    $this->dropTable(TagPost::tableName());
  }
  /*
    // Use up()/down() to run migration code without a transaction.
    public function up()
    {
    }
    public function down()
    {
        echo "m230321_093310_tag_post cannot be reverted.\n";
        return false;
    }
    */
}

```

Kết quả&#x20;

C:\xampp72\htdocs\testcom\common\models\TagPost.php

```php
<?php
namespace common\models;
use Yii;
/**
 * This is the model class for table "tag_post".
 *
 * @property int|null $tag_id
 * @property int|null $post_id
 */
class TagPost extends \yii\db\ActiveRecord
{
  /**
   * {@inheritdoc}
   */
  public static function tableName()
  {
    return 'tag_post';
  }
  /**
   * {@inheritdoc}
   */
  public function rules()
  {
    return [
      [['tag_id', 'post_id'], 'integer'],
    ];
  }
  /**
   * {@inheritdoc}
   */
  public function attributeLabels()
  {
    return [
      'tag_id' => 'Tag ID',
      'post_id' => 'Post ID',
    ];
  }
}

```

### Preparing the Database <a href="#preparing-database" id="preparing-database"></a>

```
CREATE TABLE `country` (
  `code` CHAR(2) NOT NULL PRIMARY KEY,
  `name` CHAR(52) NOT NULL,
  `population` INT(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `country` VALUES ('AU','Australia',24016400);
INSERT INTO `country` VALUES ('BR','Brazil',205722000);
INSERT INTO `country` VALUES ('CA','Canada',35985751);
INSERT INTO `country` VALUES ('CN','China',1375210000);
INSERT INTO `country` VALUES ('DE','Germany',81459000);
INSERT INTO `country` VALUES ('FR','France',64513242);
INSERT INTO `country` VALUES ('GB','United Kingdom',65097000);
INSERT INTO `country` VALUES ('IN','India',1285400000);
INSERT INTO `country` VALUES ('RU','Russia',146519759);
INSERT INTO `country` VALUES ('US','United States',322976000);
```

### Configuring a DB Connection <a href="#configuring-db-connection" id="configuring-db-connection"></a>

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

### Creating an Active Record: C:\xampp74\htdocs\oectest\models\Country.php <a href="#creating-active-record" id="creating-active-record"></a>

```
<?php
namespace app\models;
use yii\db\ActiveRecord;
class Country extends ActiveRecord
{
}
```

### Creating an Action: C:\xampp74\htdocs\oectest\controllers\CountryController.php <a href="#creating-action" id="creating-action"></a>

```
<?php
namespace app\controllers;
use yii\web\Controller;
use yii\data\Pagination;
use app\models\Country;
class CountryController extends Controller
{
    public function actionIndex()
    {
        $query = Country::find();

        $pagination = new Pagination([
            'defaultPageSize' => 5,
            'totalCount' => $query->count(),
        ]);
        $countries = $query->orderBy('name')
            ->offset($pagination->offset)
            ->limit($pagination->limit)
            ->all();
        return $this->render('index', [
            'countries' => $countries,
            'pagination' => $pagination,
        ]);
    }
}
```

### Creating a View: C:\xampp74\htdocs\oectest\views\country\index.php <a href="#creating-view" id="creating-view"></a>

```
<?php
use yii\helpers\Html;
use yii\widgets\LinkPager;
?>
<h1>Countries</h1>
<ul>
<?php foreach ($countries as $country): ?>
  <li>
    <?=Html::encode("{$country->code} ({$country->name})")?>:
    <?=$country->population?>
  </li>
<?php endforeach;?>
</ul>
<?=LinkPager::widget(['pagination' => $pagination])?>
```

### Trying it Out: <https://oec.test/?r=country%2Findex> or <https://oec.test/?r=country> <a href="#trying-it-out" id="trying-it-out"></a>

<figure><img src="/files/vd8mSfOHZ9JOoPz0ufjf" alt=""><figcaption></figcaption></figure>

![](/files/sN8tZnl7kCf2kR7tW5ny)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://pdo.gitbook.io/lear-yii/working-with-databases-database-migration-full-ok.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.