Doctrine QueryBuilder使い方メモ

doctrineの検索方法メモ。よく使いそうなやつ。

# 参照
http://doctrine-orm.readthedocs.org/en/latest/reference/query-builder.html

# 場所ごと呼び出し方の違い

EC-CUBE2系でいう。SC_Query_Ex::getSingletonInstance()的な基本的な使い方。

+ Controller ( Eccube\Controller\Entry\Controller など）
   - QueryBuilderの生成： $app['orm.em']->createQueryBuilder()
  - from：明示が必要。
  - 別名の付け方：fromの2番目のパラメーター
  
```php
$query = $app['orm.em']->createQueryBuilder() 
            ->select("c")
            ->from("Eccube\\Entity\\Customer", "c")
            ->where('c.email = :email OR c.email_mobile = :email_mobile')
            ->setParameter('email', $username)
            ->setParameter('email_mobile', $username)
            ->getQuery();
$customer = $query->getResult();
```

+ Repository (Eccube\Repository\CustomerRepository 等）
  - QueryBuilderの生成： $this->createQueryBuilder() 
  - from：付けなくても自分自身が設定される（Eccube\\Entity\\Customerなど）。
  - 別名の付け方：createQueryBuilderの1番目のパラメーター

```php
$query = $this->createQueryBuilder('c')
            ->where('c.email = :email OR c.email_mobile = :email_mobile')
            ->setParameter('email', $username)
            ->setParameter('email_mobile', $username)
            ->getQuery();
$customer = $query->getResult();
```

# select
```php
$qb = $app['orm.em']->createQueryBuilder();
$qb->select("c")
   ->from("Eccube\\Entity\\Customer", "c");
$query = $qb->getQuery();
$customers = $query->getResult();

foreach ($customers as $c) {
    var_dump($c->getId());
    var_dump($c->getName01());
    var_dump($c->getName02());
}
```
# select(1件取得)
```php
$qb = $app['orm.em']->createQueryBuilder();
$qb->select("c")
   ->from("Eccube\\Entity\\Customer", "c")
   ->where("c.id = :id")
   ->setParameter("id", 1);
$query = $qb->getQuery();
$customer = $query->getSingleResult();

var_dump($customer->getId());
var_dump($customer->getName01());
var_dump($customer->getName02());
```
# count
```php
$qb = $app['orm.em']->createQueryBuilder();
$qb->select("count(c.id)")
   ->from("Eccube\\Entity\\Customer", "c");
$query = $qb->getQuery();
$count = $query->getSingleScalarResult();

var_dump($count);
```
# where
```php
$qb = $app['orm.em']->createQueryBuilder();
$qb->select("c")
   ->from("Eccube\\Entity\\Customer", "c")
   ->where("c.name01 = :name01")
   ->setParameter("name01", "足立");
$query = $qb->getQuery();
$customers = $query->getResult();

foreach ($customers as $c) {
    var_dump($c->getId());
    var_dump($c->getName01());
    var_dump($c->getName02());
}
```
# where(like)
```php
$qb = $app['orm.em']->createQueryBuilder();
$qb->select("c")
   ->from("Eccube\\Entity\\Customer", "c")
   ->where("c.email like :email")
   ->setParameter("email", "%@lockon.co.jp");
$query = $qb->getQuery();
$customers = $query->getResult();
foreach ($customers as $c) {
    var_dump($c->getId());
    var_dump($c->getName01());
    var_dump($c->getName02());
}
```
# order by
```php
$qb = $app['orm.em']->createQueryBuilder();
$qb->select("c")
   ->from("Eccube\\Entity\\Customer", "c")
   ->orderBy("c.create_date",  "DESC")
$query = $qb->getQuery();
$customers = $query->getResult();
foreach ($customers as $c) {
    var_dump($c->getId());
    var_dump($c->getName01());
    var_dump($c->getName02());
}
```
# limit
```php
$qb = $app['orm.em']->createQueryBuilder();
$qb->select("c")
   ->from("Eccube\\Entity\\Customer", "c")
   ->setMaxResults(10)
$query = $qb->getQuery();
$customers = $query->getResult();
foreach ($customers as $c) {
    var_dump($c->getId());
    var_dump($c->getName01());
    var_dump($c->getName02());
}
```