はじめに
集計クエリを含むテンプレート的なsymfonyのつくりをメモする
プロジェクトの作成
戒め: 日本語を含むパスにプロジェクトを作成してはならない(bin/consoleコマンドなどが通らなくなってハマる)
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自体はすでに入ってると思うので...)以下のコマンドを実行してないとエラー画面になる
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というフォルダ自体がないので作成してください)。処理内容は足し算をして返却するとしましょう。
<?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
<?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)
]);
}
}
{% 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
という名前でサービスのロードができるようになる
# 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
<?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)
]);
}
}
{% 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に置き換えろ、スコープを小さくしろ!みたいなことが書いてある。
↓この辺になにかいいことが書いてあるような気もするけどあとで研究
データベース
初期設定
- 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"
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
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!
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!
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
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
<?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();
}
}
<?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();
}
}
<?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
/**
* 顧客ごとの、ある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の検証
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を実行して結果配列を取得してテンプレートで表示
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,
]);
}
{% 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の現場が初めてで覚えることがスパークしてた)だったけどやってやったぜ!