QueryBuilderでの生成に苦戦したときのメモ書きです。
それでもよければ参考程度に。。。

##Doctrine
ORM（Object Relational Mapping）ライブラリの1つで、
SQLを書かずオブジェクト指向でデータベースの操作ができる優れものです！

##データを取得してみる

シンプルにやるとこんなの

customer.sql

SELECT *
FROM dtb_customer
WHERE age = 20;

CustomerRepository.php

$query = $this->createQueryBuilder('c')
            ->where('c.age = :adult')
            ->setParameter('adult', 20)
            ->getQuery();
$customer = $query->getResult();

抽出条件を追加

customer.sql

SELECT *
FROM dtb_customer
WHERE age = 20
AND id >= 10;

CustomerRepository.php

$query = $this->createQueryBuilder('c')
            ->where('c.age = :adult')
            ->andWhere('c.id >= :userid')
            ->setParameter('adult', 20)
            ->setParameter('userid', 10)
            ->getQuery();
$customer = $query->getResult();

IN句

customer.sql

SELECT *
FROM dtb_customer
WHERE id IN(2, 5);

CustomerRepository.php

$query = $this->createQueryBuilder('c')
            ->where('c.age IN(:adult1, :adult2'))
            ->setParameter('adult1', 2)
            ->setParameter('adult2', 5)
            ->getQuery();
$customer = $query->getResult();

変数にパラメータを格納したいとき

customer.sql

SELECT *
FROM dtb_customer
WHERE age = 20;

CustomerRepository.php

$adult_age = 20;
$query = $this->createQueryBuilder('c')
            ->where('c.age = :adult')
            ->setParameter('adult', $adult_age)
            ->setParameter('email_mobile', $username)
            ->getQuery();
$customer = $query->getResult();

ORDER BY

customer.sql

SELECT *
FROM dtb_customer
ORDER BY age ASC;

CustomerRepository.php

$adult_age = 20;
$query = $this->createQueryBuilder('c')
            ->orderBy('c.age', 'ASC');
            ->getQuery();
$customer = $query->getResult();

JOIN part1

customer.sql

SELECT *
FROM dtb_customer AS c
INNER JOIN dtb_order AS o
ON c.id = o.customer_id;

CustomerRepository.php

$query = $this->createQueryBuilder('c')
            ->leftJoin('c.dtb_order o');
            ->getQuery();
$customer = $query->getResult();

Doctrineくんはdtb_customerとdtb_orderが関連していることを知っているため、
JOINの条件を自動的に追加してくれます。
同様の使い方でrightJoin()やinnerJoin()なんかもできます。

JOIN part2

customer.sql

SELECT *
FROM dtb_customer
INNER JOIN dtb_order
ON c.id = 10;

CustomerRepository.php

$query = $this->createQueryBuilder('c')
            ->leftJoin('c.dtb_order o ON c.id = 10');
            ->getQuery();
$customer = $query->getResult();

独自の結合条件を使いたいならこう。

LIMIT

customer.sql

SELECT *
FROM dtb_customer
ORDER BY age ASC
LIMIT 5;

CustomerRepository.php

$adult_age = 20;
$query = $this->createQueryBuilder('c')
            ->orderBy('c.age', 'ASC')
            ->limit(5);
            ->getQuery();
$customer = $query->getResult();