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();