Doctrine2
EC-CUBE3

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

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