Symfony2 DBAL QueryBuilder

Featured Image © ba1969@sxc.hu

Sometimes, when we have some relatively complex queries to write, it’s so long and inflexible statement that it’s pretty hard to understand few days after we wrote it, or we have to add some dynamic parameters or conditions that writing plain DBAL statement could be real nightmare …

Luckily, in Symfony2, for Doctrine2 DBAL statements like for ORM we also can use integrated QueryBuilder to build those complex queries and make our life easier.

So, instead of this kind of writing queries:

//...
        $stmt = $conn->executeQuery(
                "SELECT items.id as item_id,
                 items.item_name as item_name,
                 it.mobile_type as item_type,
                 st.status_symbol as status,
                 items.date_modified as date_modified,
                 fav.sort_order favorite_sort_order
                 FROM items
                 INNER JOIN item_types it on it.id = items.item_type_id
                 INNER JOIN statuses st on st.id = items.status_id
                 INNER JOIN favorites fav on fav.item_id = items.id
                 WHERE fav.user_id = ?
                 AND fav.appname= ?
                 AND
                 st.status_symbol = 'st_live'
                 ORDER BY items.id ASC", array($user_id, $appname), array(\PDO::PARAM_INT, \PDO::PARAM_STR)
        );
 
        $items = $stmt->fetchAll(\PDO::FETCH_ASSOC);
//...

we always could use this approach to make the same thing:

//...
        $qb = $this->conn->createQueryBuilder();
 
        $qb->select('DISTINCT itc.item_id');
        $qb->from('items_to_collections', 'itc');
        $qb->innerJoin('itc', 'statuses', 's', 's.id = itc.status_id');
        $qb->innerJoin('itc', 'tags_to_items', 'tti', 'tti.item_id = itc.item_id');
 
        $and_cond = $qb->expr()->andx();
        $and_cond->add($qb->expr()->eq('s.status_symbol', $qb->expr()->literal('st_live')));
        $and_cond->add($qb->expr()->eq('tti.tag_id', $tag_id));
        $and_cond->add($qb->expr()->like('itc.path', $qb->expr()->literal($parent_path)));
 
        $qb->andWhere($and_cond);
        $qb->orderBy('itc.' . $sort_order, 'ASC');
 
        if ($limit > 0) {
            //$qb->setFirstResult(0);
            $qb->setMaxResults($limit);
        }
 
        if ($existing != null) {
            $qb->andWhere('itc.item_id NOT IN (?)');
            $q = $qb->getSql();
            $stmt = $this->conn->executeQuery($q, array($existing), array(\Doctrine\DBAL\Connection::PARAM_INT_ARRAY));
        } else {
            $q = $qb->getSql();
            $stmt = $this->conn->executeQuery($q);
        }
        $items_ids = $stmt->fetchAll(\PDO::FETCH_COLUMN);
// ...

Also in above example it’s shown how to pass array as query parameter inside query builder, considering that I wrote about array parameters in DBAL statements in my last article ….

That’s it for now.

Cheers.


6 comments

  1. I prefer to simplify the querybuilder’s usage by storing expr in a variable and use method chaining. I also prefer and highly suggest safe-guarding against SQL injection by using parameters whenever possible.

    Lastly suggest changing the $existing array conversion to utilize array type casting which will convert a scalar value into an array with a single element but use an array if it is supplied.

    $dc = $this->conn;
    $qb = $dc->createQueryBuilder();
    $expr = $qb->expr();
    $qb->select('DISTINCT itc.item_id')
        ->from('items_to_collections', 'itc')
        ->innerJoin('itc', 'statuses', 's', 's.id = itc.status_id')
        ->innerJoin('itc', 'tags_to_items', 'tti', 'tti.item_id = itc.item_id')
        ->where($expr->andX(
            $expr->eq('s.status_symbol', ':status_symbol'),
            $expr->eq('tti.tag_id', ':tag_id'),
            $expr->like('itc.path', ':path')
        ))
        ->setParameters([
            'status_symbol' => 'st_live',
            'tag_id' => $tag_id,
            'path' => $parent_path
        ])
        ->orderBy('itc.' . $sort_order, 'ASC');
    if ($limit > 0) {
        $qb->setMaxResults($limit);
    }
    if($offset > 0){
        $qb->setFirstResult($offset);   
    }
    if (false === empty($existing)) {
        $qb->andWhere($expr->notIn('itc.item_id', ':item_id'))
            ->setParameter('item_id', ((array) $existing), $dc::PARAM_STR_ARRAY);
    }
    $items_ids = $qb->execute()->fetchAll(\PDO::FETCH_COLUMN);
  2. Hey, nice article as it is very hard to find example usage for dbal’s querybuilder – although the main class itself is well documented.

    I have one issue with your examples though: the raw sql query is *not* equivalent to the querybuilder example. For instance, you rename parameters in the first, but do not in the second.

    It would be more useful if you directly replicated the sql query in your querybuilder example.

    Thanks for the article!

    Nic

  3. Luckily ? How many programmers know clear SQL and how many of them using doctrine ? I’m not talking only about PHP. I think using a dql instead of sql for complex queries is horrible.

  4. hi,

    Just to let you know, you may use:

    $queryBuilder->setParamaters(array()); 
    $stmt = $queryBuilder->execute();

    The QueryBuilder wraps the Connection and is able to execute itself as well.

    I find it clearer and shorter.

    KISS (Keep it Short and Simple ;))

  5. Do you know how to get a multidimensional array in result of a DBAL query (having a join) ? For example if I want to get a book with his author, how to get : array(‘name’ => ‘A book name’, ‘author’ => array(‘name’ => ‘A author name’))

    1. A join, joins the two tables together acting as a single table, so it wouldn’t be possible using just a DBAL. It sounds like you would be more interested in utilizing Doctrine ORM which would allow you to do.

      $author = $em->getRepository('/path/to/entities/Author')->findOneBy(['name' => 'MyAuthor']);
      $books = $author->getBooks();
      foreach ($books as $book) {
         echo $book->getName() . '  - Author: ' . $book->getAuthor()->getName();
      }

      Otherwise you would need to create a Model to format the output of the database record set as desired.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.