😌Working with Databases, Database Migration Full (ok)

https://www.yiiframework.com/doc/guide/2.0/en/start-databases

1. Creating Migrations

Working with Databases: MigrationsYii Framework

Create table

yii migrate/create create_news_table

2. Create Model

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

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

3. Create Migrate Database

C:\xampp72\htdocs\testcom\console\migrations\m130524_201442_init.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
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' => '[e-mail]',
      'role' => '100',
      'status' => '10',
      'created_at' => time(),
      'updated_at' => time()
    ]);
  }
  public function down()
  {
    $this->delete(User::tableName(), 'username = "demoadmin"');
  }
}

Kết quả:

4. 1. Create a table post

C:\xampp72\htdocs\testvn\common\models\Post.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
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ả:

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

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

C:\xampp72\htdocs\testcom\common\models\Category.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'),
    ];
  }
}

C:\xampp72\htdocs\testcom\console\migrations\m230321_085655_category.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ả:

4. 3. Create a table comments

C:\xampp72\htdocs\testcom\console\migrations\m230321_091626_comments.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
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

4. 4. Create a table tag

Kết quả:

4. 5. Create a table tag_post

C:\xampp72\htdocs\testcom\console\migrations\m230321_093310_tag_post.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ả

C:\xampp72\htdocs\testcom\common\models\TagPost.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

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

<?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

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

Creating an Action: C:\xampp74\htdocs\oectest\controllers\CountryController.php

<?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

<?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