はじめに

集計クエリを含むテンプレート的なsymfonyのつくりをメモする

プロジェクトの作成

戒め: 日本語を含むパスにプロジェクトを作成してはならない（bin/consoleコマンドなどが通らなくなってハマる）

「sf6」というプロジェクトを作ります

dev> symfony new sf6 --webapp
   [OK] Your project is now ready in D:\OneDrive\dev\sf6 
dev> cd sf6 
dev/sf6> symfony check:requirements
dev/sf6> composer require webapp

サーバーを起動する

コマンドめっちゃかんたんになってんじゃん　最高

dev/sf6> symfony server:start
  [OK] Web server listening                                                                                                 
      The Web server is using PHP CGI 8.1.1                                                                                
      https://127.0.0.1:8000

コントローラ

コントローラの作成

語尾に「Controller」とつけたほうがあとからわかりやすいよ。お？どうやら index.html を自動的に作ってくれる仕様になったようだ。ルーティングは /home になっているようだ。

dev\sf6> php bin/console make:controller HomeController

 created: src/Controller/HomeController.php
 created: templates/home/index.html.twig

  Success!

<?php

namespace App\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;

class HomeController extends AbstractController
{
    #[Route('/home', name: 'app_home')]
    public function index(): Response
    {
        return $this->render('home/index.html.twig', [
            'controller_name' => 'HomeController',
        ]);
    }
}

※encoreどうのこうのでコケる

もともと templates フォルダにある base.html.twig に「{# Run composer require symfony/webpack-encore-bundle to start using Symfony UX #}」と書いてあって、（※symfony/webpack-encore-bundle自体はすでに入ってると思うので...）以下のコマンドを実行してないとエラー画面になる

public/buildにファイルが増える

dev\sf6> npm run watch
  DONE  Compiled successfully in 8612ms
  5 files written to public\build

確認

https://127.0.0.1:8000/home でページが映ることを確認

サービス

サービスを作成する

MyService.php を新規作成（最初はServiceというフォルダ自体がないので作成してください）。処理内容は足し算をして返却するとしましょう。

src/Service/MyService.php（新規）

<?php

namespace App\Service;

class MyService
{
    /**
     * 足し算の結果を返します
     * @param int $operandA
     * @param int $operandB
     * @return int
     */
    public function calcAdd(int $operandA, int $operandB): int {
        return $operandA + $operandB;
    }
}

Dependency Injection

インジェクションそのものは、呼び出すルーチン（＝さっき作ったservice）に、呼び出し側が依存しないための（＝疎結合に保つための）仕組み

コンストラクタからサービスを使用する

コンストラクタ・インジェクションと呼ばれる。symfonyの世界では、このコンストラクタからのインジェクションが「dependency injection」というデカい主語になっているようだ。なお、コントローラ内の index() にセットすることもできる。

CodeIgniterなど、ほかのPHPフレームワークを触っていると、ユーザが引数を渡してないのになぜ急に index() に引数を渡すのか？といった疑問が沸くが、まぁ autowire という仕組みで service も入り口を共有してるってことね。まぁ通常は interface で置き換えろ、とか言われる。https://symfony.com/doc/current/service_container/autowiring.html#working-with-interfaces

HomeController.php（コンストラクタにある MyService $myServiceに注目）

<?php

namespace App\Controller;

use App\Service\MyService;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;

class HomeController extends AbstractController
{
+   private MyService $service;
    
+   public function __construct(MyService $myService)
+   {
+       $this->service = $myService;
+   }
    
    #[Route('/home', name: 'app_home')]
    public function index(): Response
    {
        return $this->render('home/index.html.twig', [
            'controller_name' => 'HomeController',
+           'calculated_value' => $this->service->calcAdd(1, 1)
        ]);
    }
}

templates/home/index.html.twig

{% extends 'base.html.twig' %}

{% block title %}Hello HomeController!{% endblock %}

{% block body %}
<style>
    .example-wrapper { margin: 1em auto; max-width: 800px; width: 95%; font: 18px/1.5 sans-serif; }
    .example-wrapper code { background: #F5F5F5; padding: 2px 6px; }
</style>

<div class="example-wrapper">
    <h1>Hello {{ controller_name }}! ✅</h1>

    This friendly message is coming from:
    <ul>
        <li>Your controller at <code><a href="{{ 'D:/OneDrive/dev/sf6/src/Controller/HomeController.php'|file_link(0) }}">src/Controller/HomeController.php</a></code></li>
        <li>Your template at <code><a href="{{ 'D:/OneDrive/dev/sf6/templates/home/index.html.twig'|file_link(0) }}">templates/home/index.html.twig</a></code></li>
    </ul>

+   <h2>加算された値</h2>
+   <p>{{ calculated_value }}</p>
    
</div>
{% endblock %}

services.yaml を経由して、コントローラからサービスを使用する

symfony3の現場に入ってたときはこっちだったなー。
calc.sample という名前でサービスのロードができるようになる

config/services.yaml

# 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'

    # add more service definitions when explicit configuration is needed
    # please note that last definitions always *replace* previous ones
+   calc.sample:
+       class: App\Service\MyService
+       autowire: true

src/Controller/HomeController.php

<?php

namespace App\Controller;

use App\Service\MyService;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;

class HomeController extends AbstractController
{
    private MyService $service;
    
    public function __construct(MyService $myService)
    {
        $this->service = $myService;
    }
    
    #[Route('/home', name: 'app_home')]
    public function index(): Response
    {
+       $serviceFromYaml = $this->container->get('calc.sample');
        return $this->render('home/index.html.twig', [
            'controller_name' => 'HomeController',
            'calculated_value' => $this->service->calcAdd(1, 1),
+           'calculated_value_via_yaml' => $serviceFromYaml->calcAdd(2, 2)
        ]);
    }
}

templates/home/index.html.twig

{% extends 'base.html.twig' %}

{% block title %}Hello HomeController!{% endblock %}

{% block body %}
<style>
    .example-wrapper { margin: 1em auto; max-width: 800px; width: 95%; font: 18px/1.5 sans-serif; }
    .example-wrapper code { background: #F5F5F5; padding: 2px 6px; }
</style>

<div class="example-wrapper">
    <h1>Hello {{ controller_name }}! ✅</h1>

    This friendly message is coming from:
    <ul>
        <li>Your controller at <code><a href="{{ 'D:/OneDrive/dev/sf6/src/Controller/HomeController.php'|file_link(0) }}">src/Controller/HomeController.php</a></code></li>
        <li>Your template at <code><a href="{{ 'D:/OneDrive/dev/sf6/templates/home/index.html.twig'|file_link(0) }}">templates/home/index.html.twig</a></code></li>
    </ul>

    <h2>加算された値</h2>
    <p>{{ calculated_value }}</p>

+   <h2>加算された値（services.yaml経由）</h2>
+   <p>{{ calculated_value_via_yaml }}</p>
</div>
{% endblock %}

※どうやら「台帳式」はすでに推奨されないらしい

symfony4以降はInjectionに置き換えろ、スコープを小さくしろ！みたいなことが書いてある。

↓この辺になにかいいことが書いてあるような気もするけどあとで研究

データベース

初期設定

.env

- DATABASE_URL="postgresql://symfony:ChangeMe@127.0.0.1:5432/app?serverVersion=13&charset=utf8"
+ DATABASE_URL="mysql://USERNAME:COOL_PASSWORD@127.0.0.1:3306/sf6db?serverVersion=8.0"

「sf6db」というdbをmysqlに作成

dev/sf6> php bin/console doctrine:database:create

php.ini の extension=pdo_mysql のコメントアウトを外してないと An exception occurred in the driver: could not find driver エラーが起きる

テーブルの設計

こんなもんでいいでしょ？Webアプリの作成に慣れてくるほどに、ペン書きでガリガリ設計から始めることの大切さを実感するよな

顧客がいる
顧客が1回分の買いを入れる
顧客が1回分で買った内容がある

entity

customerテーブル

dev\sf6> php bin/console make:entity

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

 created: src/Entity/Customer.php
 created: src/Repository/CustomerRepository.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/Customer.php

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

           
  Success!

customer_orderテーブル

dev\sf6> php bin/console make:entity

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

 created: src/Entity/CustomerOrder.php
 created: src/Repository/CustomerOrderRepository.php
 
 Entity generated! Now let's add some fields!
 You can always add more fields later manually or by re-running this command.

（※語尾に勝手に「_id」がつきます）
 New property name (press <return> to stop adding fields):
 > customer

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

 What class should this entity be related to?:
 > Customer

What type of relationship is this?
 ------------ ----------------------------------------------------------------------------- 
  Type         Description                                                                  
 ------------ -----------------------------------------------------------------------------
  ManyToOne    Each CustomerOrder relates to (has) one Customer.
               Each Customer can relate to (can have) many CustomerOrder objects

  OneToMany    Each CustomerOrder can relate to (can have) many Customer objects.
               Each Customer relates to (has) one CustomerOrder

  ManyToMany   Each CustomerOrder can relate to (can have) many Customer objects.
               Each Customer can also relate to (can also have) many CustomerOrder objects

  OneToOne     Each CustomerOrder relates to (has) exactly one Customer.
               Each Customer also relates to (has) exactly one CustomerOrder.
 ------------ -----------------------------------------------------------------------------

 Relation type? [ManyToOne, OneToMany, ManyToMany, OneToOne]:
 > ManyToOne

 Is the CustomerOrder.customer property allowed to be null (nullable)? (yes/no) [yes]:
 >

 Do you want to add a new property to Customer so that you can access/update CustomerOrder objects from it - e.g. $customer
->getCustomerOrders()? (yes/no) [yes]:
 > 

 A new property will also be added to the Customer class so that you can access the related CustomerOrder objects from it. 

 New field name inside Customer [customerOrders]:
 >

 updated: src/Entity/CustomerOrder.php
 updated: src/Entity/Customer.php

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

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

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

 updated: src/Entity/CustomerOrder.php

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


           
  Success!

order_itemsテーブル

dev\sf6> php bin/console make:entity

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

 created: src/Entity/OrderItems.php
 created: src/Repository/OrderItemsRepository.php
 
 Entity generated! Now let's add some fields!
 You can always add more fields later manually or by re-running this command.

（※語尾に勝手に「_id」がつきます）
 New property name (press <return> to stop adding fields):
 > customer_order

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

 What class should this entity be related to?:
 > CustomerOrder      

What type of relationship is this?
 ------------ -------------------------------------------------------------------------------
  Type         Description                                                                   
 ------------ -------------------------------------------------------------------------------
  ManyToOne    Each OrderItems relates to (has) one CustomerOrder.
               Each CustomerOrder can relate to (can have) many OrderItems objects

  OneToMany    Each OrderItems can relate to (can have) many CustomerOrder objects.
               Each CustomerOrder relates to (has) one OrderItems

  ManyToMany   Each OrderItems can relate to (can have) many CustomerOrder objects.
               Each CustomerOrder can also relate to (can also have) many OrderItems objects

  OneToOne     Each OrderItems relates to (has) exactly one CustomerOrder.
               Each CustomerOrder also relates to (has) exactly one OrderItems.
 ------------ -------------------------------------------------------------------------------

 Relation type? [ManyToOne, OneToMany, ManyToMany, OneToOne]:
 > ManyToOne

 Is the OrderItems.customer_order property allowed to be null (nullable)? (yes/no) [yes]:
 >

 Do you want to add a new property to CustomerOrder so that you can access/update OrderItems objects from it - e.g. $custom
erOrder->getOrderItems()? (yes/no) [yes]:
 >

 A new property will also be added to the CustomerOrder class so that you can access the related OrderItems objects from it
.

 New field name inside CustomerOrder [orderItems]:
 >

 updated: src/Entity/OrderItems.php
 updated: src/Entity/CustomerOrder.php

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

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

 Field length [255]:
 >

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

 updated: src/Entity/OrderItems.php

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

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

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

 updated: src/Entity/OrderItems.php

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

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

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

 updated: src/Entity/OrderItems.php

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


           
  Success!

migration and migrate

ほかのフレームワークでもよくあるよね。
開発メンバーがテーブル定義を共有できるように台帳を作成する

dev\sf6> php bin/console make:migration


  Success! 
           

（※マイグレーションファイルつくったからレビューしてから migrate コマンドを実行しろ！）
 Next: Review the new migration "migrations/Version20220515044812.php"
 Then: Run the migration with php bin/console doctrine:migrations:migrate
 See https://symfony.com/doc/current/bundles/DoctrineMigrationsBundle/index.html
dev\sf6> php bin/console doctrine:migrations:migrate   

（※mysqlに実際にmigrateを実行するぞ？いいな？）
 WARNING! You are about to execute a migration in database "sf6db" that could result in schema changes and data loss. Are y
ou sure you wish to continue? (yes/no) [yes]:
 >

[notice] Migrating up to DoctrineMigrations\Version20220515044812
[notice] finished in 601.9ms, used 20M memory, 1 migrations executed, 6 sql queries

確認

初期データを整備する

データを整備しよう

インストール

dev\sf6> composer require --dev orm-fixtures

フィクスチャ（Seeder）の作成

CustomerFixtures
CustomerOrderFixtures
OrderItemsFixtures

上記の名前で3種類作ってください

dev\sf6> php bin/console make:fixtures

 The class name of the fixtures to create (e.g. AppFixtures):
 > CustomerFixtures

 created: src/DataFixtures/CustomerFixtures.php

           
  Success!

dev\sf6> composer require fzaninotto/faker --dev

src/DataFixtures/CustomerFixtures.php

<?php

namespace App\DataFixtures;

use App\Entity\Customer;
use Doctrine\Bundle\FixturesBundle\Fixture;
use Doctrine\Persistence\ObjectManager;
use Faker\Factory;

class CustomerFixtures extends Fixture
{
    public function load(ObjectManager $manager): void
    {
        $faker = Factory::create();
        
        for ($i = 0; $i < 20; $i++) {
            $entity = new Customer();
            $entity->setName($faker->name);
            $manager->persist($entity);
        }

        $manager->flush();
    }
}

src/DataFixtures/CustomerOrderFixtures.php

<?php

namespace App\DataFixtures;

use App\Entity\Customer;
use App\Entity\CustomerOrder;
use DateTime;
use Doctrine\Bundle\FixturesBundle\Fixture;
use Doctrine\Persistence\ObjectManager;

class CustomerOrderFixtures extends Fixture
{
    public function load(ObjectManager $manager): void
    {
        $repository = $manager->getRepository(Customer::class);
        $customers = $repository->findAll();
    
        $orderDate = new DateTime('now');
        foreach ($customers as $customer) {
            $entity = new CustomerOrder();
            $entity->setCustomer($customer);
            $entity->setOrderDate($orderDate);
            $manager->persist($entity);
        }
    
        $manager->flush();
    }
}

src/DataFixtures/OrderItemsFixtures.php

<?php

namespace App\DataFixtures;

use App\Entity\CustomerOrder;
use App\Entity\OrderItems;
use Doctrine\Bundle\FixturesBundle\Fixture;
use Doctrine\Persistence\ObjectManager;
use Faker\Factory;

class OrderItemsFixtures extends Fixture
{
    public function load(ObjectManager $manager): void
    {
        $faker = Factory::create();
        $itemList = ['バナナ', 'りんご', 'メロン', 'レタス', 'キャベツ', 'にんじん'];
        
        $repository = $manager->getRepository(CustomerOrder::class);
        $orders = $repository->findAll();
    
        foreach ($orders as $order) {
            $entity = new OrderItems();
            $entity->setCustomerOrder($order);
            $entity->setName($faker->randomElement($itemList));
            $entity->setPrice($faker->numberBetween(min: 1000, max: 3000));
            $entity->setAmount($faker->numberBetween(min: 1, max: 10));
            $manager->persist($entity);
        }
        foreach ($orders as $order) {
            $entity = new OrderItems();
            $entity->setCustomerOrder($order);
            $entity->setName($faker->randomElement($itemList));
            $entity->setPrice($faker->numberBetween(min: 1000, max: 3000));
            $entity->setAmount($faker->numberBetween(min: 1, max: 10));
            $manager->persist($entity);
        }
    
        $manager->flush();
    }
}

フィクスチャのロード（初期化）

なんべんでもコマンドを実行して、最初の状態にリセットできるが pk は増え続ける

dev\sf6> php bin/console doctrine:fixtures:load 

 Careful, database "sf6db" will be purged. Do you want to continue? (yes/no) [no]:
 > yes

   > purging database
   > loading App\DataFixtures\CustomerFixtures
   > loading App\DataFixtures\CustomerOrderFixtures
   > loading App\DataFixtures\OrderItemsFixtures

集計クエリをつくる

doctrineはキャメルケース coolClass MySQLはスネークケース cool_class ってよくあると思うけど、そこをきちんと理解しないとカオスに突入する。特に現場入りしてdbのテーブル同士が整理できてないときな
https://qiita.com/YoshitakaOkada/items/ab7004106832c450289a

DQL

src/Repository/CustomerOrderRepository.php

    /**
     * 顧客ごとの、あるprice以上のアイテムの合計priceを返します
     * @param $specifiedPrice
     * @return array
     */
   public function totallingThePriceOrMore($specifiedPrice): array
   {
       $query = $this->createQueryBuilder('c')
           ->select('c.name as customer_name')
           ->addSelect('co.order_date')
           ->addSelect('oi.name as item_name')
           ->addSelect('SUM(oi.price) as item_price')
           ->addSelect('SUM(oi.amount) as item_amount')
           ->innerJoin('c.customerOrders', 'co')
           ->innerJoin('co.orderItems', 'oi')
           ->andWhere('oi.price >= :specifiedPrice')
           ->setParameter('specifiedPrice', $specifiedPrice)
           ->groupBy('c.name')
           ->addGroupBy('co.order_date')
           ->addGroupBy('oi.name')
           ->orderBy('c.id', 'ASC')
           ->getQuery();
       
       return $query->getResult();
   }

できあがったDQLの検証

totallingThePriceOrMoreのSQL

SELECT 
	c0_.name AS name_0, 
	c1_.order_date AS order_date_1, 
	o2_.name AS name_2, 
	SUM(o2_.price) AS sclr_3, 
	SUM(o2_.amount) AS sclr_4 
FROM customer c0_ 
	INNER JOIN customer_order c1_ ON c0_.id = c1_.customer_id 
	INNER JOIN order_items o2_ ON c1_.id = o2_.customer_order_id 
WHERE o2_.price >= ?
GROUP BY c0_.name, c1_.order_date, o2_.name 
ORDER BY c0_.id ASC

$query->getQuery()->getSQL()でSQLが出せる。name_0 とか勝手にas名がつくけど、取得後の配列へアクセスするときはきちんとDQLで作り込んだas名で取得できる

↓customer_order_id 50 の「バナナ」「レタス」は独立したまま、customer_order_id 53 の「にんじん」は、whereの足切り（＝1,500円以上）の結果として1レコードになる。whereの足切りを合格してアイテム名が同じものどうしは合計されていることを確認できた

(↓集計前 order_items のレコード)

（↓DQL集計後のSQL）

コントローラでDQLを実行して結果配列を取得してテンプレートで表示

src/Controller/HomeController.php

    public function index(): Response
    {
+       $aggregateData = $this->customerRepository->totallingThePriceOrMore(1500);
        
        return $this->render('home/index.html.twig', [
            'controller_name' => 'HomeController',
            'calculated_value' => $this->service->calcAdd(1, 1),
+           'aggregated_value' => $aggregateData,
        ]);
    }

templates/home/index.html.twig

{% extends 'base.html.twig' %}

{% block title %}Hello HomeController!{% endblock %}

{% block body %}
<style>
    .example-wrapper { margin: 1em auto; max-width: 800px; width: 95%; font: 18px/1.5 sans-serif; }
    .example-wrapper code { background: #F5F5F5; padding: 2px 6px; }
</style>

<div class="example-wrapper">
    <h1>Hello {{ controller_name }}! ✅</h1>

    This friendly message is coming from:
    <ul>
        <li>Your controller at <code><a href="{{ 'D:/OneDrive/dev/sf6/src/Controller/HomeController.php'|file_link(0) }}">src/Controller/HomeController.php</a></code></li>
        <li>Your template at <code><a href="{{ 'D:/OneDrive/dev/sf6/templates/home/index.html.twig'|file_link(0) }}">templates/home/index.html.twig</a></code></li>
    </ul>

    <h2>加算された値</h2>
    <p>{{ calculated_value }}</p>

+   <h2>集計された値</h2>
+   <table>
+       <thead>
+           <tr>
+               <th>顧客名</th>
+               <th>受注日</th>
+               <th>アイテム名</th>
+               <th>単価</th>
+               <th>量</th>
+           </tr>
+       </thead>
+       <tbody>
+           {% for row in aggregated_value %}
+               <tr>
+                   <td>{{ row.customer_name }}</td>
+                   <td>{{ row.order_date|date('Y/m/d') }}</td>
+                   <td>{{ row.item_name }}</td>
+                   <td>{{ row.item_price }}</td>
+                   <td>{{ row.item_amount }}</td>
+               </tr>
+           {% endfor %}
+       </tbody>
+   </table>

</div>
{% endblock %}

あのとき現場でsymfonyはほぼトラウマ（symfonyのトラウマというよりはそもそもWebの現場が初めてで覚えることがスパークしてた）だったけどやってやったぜ！

はじめに

プロジェクトの作成

サーバーを起動する

コントローラ

コントローラの作成

※encoreどうのこうのでコケる

確認

サービス

サービスを作成する

Dependency Injection

コンストラクタからサービスを使用する

services.yaml を経由して、コントローラからサービスを使用する

※どうやら「台帳式」はすでに推奨されないらしい

データベース

初期設定

テーブルの設計

entity

migration and migrate

確認

初期データを整備する

インストール

フィクスチャ（Seeder） の作成

フィクスチャのロード（初期化）

集計クエリをつくる

DQL

できあがったDQLの検証

コントローラでDQLを実行して結果配列を取得してテンプレートで表示

フィクスチャ（Seeder）の作成