Doctrine QueryBuilder使い方メモ

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

参照

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

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

• Controller ( Eccube\Controller\Entry\Controller など）
  • QueryBuilderの生成： $app['orm.em']->createQueryBuilder()
  • from：明示が必要。
  • 別名の付け方：fromの2番目のパラメーター

$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番目のパラメーター

$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

$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件取得)

$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

$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

$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)

$qb = $app['orm.em']->createQueryBuilder();
$qb->select("c")
   ->from("Eccube\\Entity\\Customer", "c")
   ->where("c.email like :email")
   ->setParameter("email", "[e-mail]");
$query = $qb->getQuery();
$customers = $query->getResult();
foreach ($customers as $c) {
    var_dump($c->getId());
    var_dump($c->getName01());
    var_dump($c->getName02());
}

order by

$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

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