doctrine テクニック集

はじめに

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

CASEを切り替えて読んで

<?php

/**
 * Class DefaultController
 *
 * @author "Yoshitaka Okada <[e-mail]>"
 */
class DefaultController extends AbstractController
{

    /**
     * @Route("/", name="default")
     * @param GiftsService $gifts
     * @param MyService $myService
     * @return Response
     */
    public function index(GiftsService $gifts, MyService $myService)
    {
        // CASE1: InnerJoin先のprice>0を抽出
        // $users = $this->getDoctrine()->getRepository(User::class)->findByOverThan(0);

        // CASE2: 指定されたidをサブクエリを使用して抽出
        $users = $this->getDoctrine()->getRepository(User::class)->findUsingSubQuery([1, 2]);

        // debug出力
        VarDumper::dump(
            [
                '$users' => $users,
                '$gifts' => $gifts,
                '$results' => $myService->getMessagesOver0(),
            ]
        );

        // templateへ出力
        return $this->render(
            'default/index.html.twig',
            [
                'controller_name' => 'DefaultController',
                'users' => $users,
                'random_gift' => $gifts,
                'results' => $myService->getMessagesOver0(),
            ]
        );
    }
}

agg_project/src/Repository/UserRepository.php（いわゆるクエリ部分）

<?php

namespace App\Repository;

use App\Entity\User;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use Symfony\Component\VarDumper\VarDumper;

/**
 * @method User|null find($id, $lockMode = null, $lockVersion = null)
 * @method User|null findOneBy(array $criteria, array $orderBy = null)
 * @method User[]    findAll()
 * @method User[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class UserRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, User::class);
    }

    public function findByOverThan($value)
    {
        return $this
            ->createQueryBuilder('u')
            ->addSelect('m')
            ->InnerJoin('u.messages', 'm')
            ->andWhere('m.price > :val')
            ->setParameter('val', $value)
            ->orderBy('u.id', 'ASC')
            ->getQuery()
            ->getResult()
            ;
    }

    /**
     * @param int[] $idList
     * @return int|mixed|string|null
     */
    public function findUsingSubQuery(array $idList)
    {
        $retValue = null;
        $subQuery = $this
            ->createQueryBuilder("m_sub")
            ->select("m_sub.id")
            ->where("m_sub.id IN (:idList)")
            ->setParameter("idList", $idList);
        $temp = $subQuery->getQuery()->getResult();
        VarDumper::dump($temp);
        if (count($temp) >0){
            $retValue = $this
                ->createQueryBuilder("m")
                ->where("m.id IN (:subQueryIdList)")
                ->setParameter("subQueryIdList", $temp)
                ->getQuery()
                ->getResult();
        }
        return $retValue;
    }
}