【Symfony】SymfonyでMySQLに接続してみる！

brew install mysql
sudo mysql.server start
mysql -u root

mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
mysql> SET PASSWORD FOR newuser@localhost='password';

mysql> exit

mysql -u newuser -p

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.33    |
+-----------+
1 row in set (0.00 sec)

# DATABASE_URL="sqlite:///%kernel.project_dir%/var/data.db"
# DATABASE_URL="mysql://app:!ChangeMe!@127.0.0.1:3306/app?serverVersion=8.0.32&charset=utf8mb4"
# DATABASE_URL="mysql://app:!ChangeMe!@127.0.0.1:3306/app?serverVersion=10.11.2-MariaDB&charset=utf8mb4"
DATABASE_URL="mysql://newuser:password@127.0.0.1:3306/symfony_test?serverVersion=8.0.33&charset=utf8mb4"
# DATABASE_URL="postgresql://app:!ChangeMe!@127.0.0.1:5432/app?serverVersion=15&charset=utf8"

DATABASE_URL="mysql://newuser:password@127.0.0.1:3306/symfony_test?serverVersion=8.0.33&charset=utf8mb4"

DATABASE_URL="mysql://{DBのユーザー名}:{DBユーザーのパスワード}@{DBのホスト}/{新規で作成するSymfony用のDB名}?serverVersion={前節で確認したMySQLのバージョン}&charset=utf8mb4"

composer require symfony/orm-pack
composer require --dev symfony/maker-bundle

brew install composer

php bin/console doctrine:database:create

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| symfony_test       |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

taka@Taka my_project % php bin/console make:entity

 Class name of the entity to create or update (e.g. DeliciousGnome):
 > User

 created: src/Entity/User.php
 created: src/Repository/UserRepository.php

 Entity generated! Now let's add some fields!
 You can always add more fields later manually or by re-running this command.

 New property name (press <return> to stop adding fields):
 > name

 Field type (enter ? to see all types) [string]:
 > string

 Field length [255]:
 > 255

 Can this field be null in the database (nullable) (yes/no) [no]:
 > no

 updated: src/Entity/User.php

 Add another property? Enter the property name (or press <return> to stop adding fields):
 > age

 Field type (enter ? to see all types) [string]:
 > integer

 Can this field be null in the database (nullable) (yes/no) [no]:
 > no

 updated: src/Entity/User.php

 Add another property? Enter the property name (or press <return> to stop adding fields):
 >

  Success!

 Next: When you're ready, create a migration with php bin/console make:migration

// 新規作成または更新するEntityの名前
1. Class name of the entity to create or update 

// 新規のプロパティの名前(DBでいうカラム名)
2. New property name (press <return> to stop adding fields):

// 前項で指定したプロパティの型(DBでいうカラムの型)
3.  Field type (enter ? to see all types) [string]: 

// プロパティの文字列長（おそらく文字列の場合のみ聞かれる）
4.  Field length [255]: 

// nullを許容するかどうか
5. Can this field be null in the database (nullable) (yes/no) [no]: 

// 他にもプロパティを追加するかどうか？するならプロパティ名を入力（これ以上追加しない場合は何も入力せず"Enter"）
6. Add another property? Enter the property name (or press <return> to stop adding fields): 

...

php bin/console make:entity

taka@Taka my_project % php bin/console make:migration
 created: migrations/Version20230729141041.php


  Success!


 Review the new migration then run it with php bin/console doctrine:migrations:migrate
 See https://symfony.com/doc/current/bundles/DoctrineMigrationsBundle/index.html

    ...
    public function up(Schema $schema): void
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->addSql('CREATE TABLE comment (id INT AUTO_INCREMENT NOT NULL, user_id INT NOT NULL, message VARCHAR(255) NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
        $this->addSql('CREATE TABLE user (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, age INT NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
        $this->addSql('CREATE TABLE messenger_messages (id BIGINT AUTO_INCREMENT NOT NULL, body LONGTEXT NOT NULL, headers LONGTEXT NOT NULL, queue_name VARCHAR(190) NOT NULL, created_at DATETIME NOT NULL COMMENT \'(DC2Type:datetime_immutable)\', available_at DATETIME NOT NULL COMMENT \'(DC2Type:datetime_immutable)\', delivered_at DATETIME DEFAULT NULL COMMENT \'(DC2Type:datetime_immutable)\', INDEX IDX_75EA56E0FB7336F0 (queue_name), INDEX IDX_75EA56E0E3BD61CE (available_at), INDEX IDX_75EA56E016BA31DB (delivered_at), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
    }

    public function down(Schema $schema): void
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->addSql('DROP TABLE comment');
        $this->addSql('DROP TABLE user');
        $this->addSql('DROP TABLE messenger_messages');
    }
    ...

taka@Taka my_project % php bin/console doctrine:migrations:migrate

 WARNING! You are about to execute a migration in database "symfony_test" that could result in schema changes and data loss. Are you sure you wish to continue? (yes/no) [yes]:
 > yes

[notice] Migrating up to DoctrineMigrations\Version20230729141041
[notice] finished in 18.7ms, used 22M memory, 1 migrations executed, 3 sql queries

 [OK] Successfully migrated to version : DoctrineMigrations\Version20230729141041

mysql> show tables;
+-----------------------------+
| Tables_in_symfony_test      |
+-----------------------------+
| comment                     |
| doctrine_migration_versions |
| messenger_messages          |
| user                        |
+-----------------------------+
4 rows in set (0.00 sec)

mysql> DESCRIBE user;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
| age   | int          | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> DESCRIBE comment;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| user_id | int          | NO   |     | NULL    |                |
| message | varchar(255) | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

INSERT INTO user (name, age) VALUES
('User1', 20),
('User2', 25),
('User3', 30),
('User4', 35),
('User5', 40),
('User6', 45),
('User7', 50),
('User8', 55),
('User9', 60),
('User10', 65);

INSERT INTO comment (user_id, message) VALUES
(1, 'Message from User1'),
(2, 'Message from User2'),
(3, 'Message from User3'),
(4, 'Message from User4'),
(5, 'Message from User5'),
(6, 'Message from User6'),
(7, 'Message from User7'),
(8, 'Message from User8'),
(9, 'Message from User9'),
(10, 'Message from User10');

    // CommentRepository.phpに下記メソッドを追加

    ...
    /**
    * @return Comment[] Returns an array of Comment objects
    */
    public function findByUserId($userId): array
    {
        return $this->createQueryBuilder('c')
            ->andWhere('c.user_id = :user_id')
            ->setParameter('user_id', $userId)
            ->orderBy('c.id', 'ASC')
            ->setMaxResults(10)
            ->getQuery()
            ->getResult()
        ;
    }
    ...

# This file is the entry point to configure your own services.
# Files in the packages/ subdirectory configure your dependencies.

# Put parameters here that don't need to change on each machine where the app is deployed
# https://symfony.com/doc/current/best_practices.html#use-parameters-for-application-configuration
parameters:

services:
  # default configuration for services in *this* file
  _defaults:
    autowire: true # Automatically injects dependencies in your services.
    autoconfigure: true # Automatically registers your services as commands, event subscribers, etc.

  # makes classes in src/ available to be used as services
  # this creates a service per class whose id is the fully-qualified class name
  App\:
    resource: "../src/"
    exclude:
      - "../src/DependencyInjection/"
      - "../src/Entity/"
      - "../src/Kernel.php"

  # ↓ここからを追加
  App\Controller\:
    resource: "../src/Controller"
    tags: ["controller.service_arguments"]
  # ↑ここまでを追加

  # add more service definitions when explicit configuration is needed
  # please note that last definitions always *replace* previous ones

<?php
namespace App\Controller;

use App\Entity\User;
use App\Entity\Comment;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;

class LuckyController
{
    #[Route('/lucky/number')]
    public function number(EntityManagerInterface $entityManager): Response
    {
        $userId = 1; // テスト用に決め打ちで仮置き

        // userIdの情報をそれぞれ取得
        $user = $entityManager->getRepository(User::class)->find($userId);
        $comments = $entityManager->getRepository(Comment::class)->findByUserId($user->getId());

        // コメントは表示できる形に整形
        $commentStrings = array_map(function($comment) {
            return $comment->getMessage();
        }, $comments);
        $commentsOutput = implode('<br>', $commentStrings);

        return new Response(
            '<html>
                <body>
                    User: '.$user->getName() . '<br>Comment: '. $commentsOutput.'
                </body>
            </html>'
        );
    }
}

http://127.0.0.1:8001/lucky/number