doctrine: QueryBuilder vs createQuery?

Question

In Doctrine you can create DQL in 2 ways:

EntityManager::createQuery:

$query = $em->createQuery('SELECT u FROM MyProject\Model\User u WHERE u.id = ?1');

QueryBuilder:

$qb->add('select', 'u')
   ->add('from', 'User u')
   ->add('where', 'u.id = ?1')
   ->add('orderBy', 'u.name ASC');

I wonder what the difference is and which should I use?

score 70 · Answer 1 · edited Jan 05 '12 at 14:56

70

DQL is easier to read as it is very similar to SQL. If you don't need to change the query depending on a set of parameters this is probably the best choice.
Query Builder is an api to construct queries, so it's easier if you need to build a query dynamically like iterating over a set of parameters or filters. You don't need to do any string operations to build your query like join, split or whatever.

edited Jan 05 '12 at 14:56

Joe Lencioni

10,231
18
55
66

answered Dec 09 '10 at 00:23

jackbravo

1,329
1
12
17

1

But isn't there an overhead on parsing DQL string in the first case? Or the builder makes the same DQL string as a result as well? – Alexey Kosov Dec 03 '14 at 08:34
4

Yes, QueryBuilder creates the DQL string for you. After that, DQL is parsed anyways. – Dennis Oct 07 '15 at 15:33
correct me if I'm wrong: but basically you should not use it for delete or update? because it doesn't update the doctrine cache, nor calls events etc. What is the difference in DQL and query builder in terms of events emitted? is it the same? – Toskan Apr 27 '22 at 08:54

Martin Kočička · Answer 2 · 2012-05-26T08:30:25.190

Query builder is just, lets say, interface to create query... It should be more comfortable to use, it does not have just add() method, but also methods like where(), andWhere(), from(), etc. But in the end, it just composes query like the one you use in the createQuery() method.

Example of more advanced use of query builder:

$em->createQueryBuilder()
            ->from('Project\Entities\Item', 'i')
            ->select("i, e")
            ->join("i.entity", 'e')
            ->where("i.lang = :lang AND e.album = :album")
            ->setParameter('lang', $lang)
            ->setParameter('album', $album);

you can add ->setParameters(array('x' => 'y', 'z' => 'w', ...)) — Herr Nentu', Sep 15 '15 at 08:19

score 16 · Answer 3 · answered Aug 16 '12 at 17:07

16

They have different purposes:

DQL is easier to use when you know your full query.
Query builder is smarter when you have to build your query based on some conditions, loops etc.

answered Aug 16 '12 at 17:07

Vincent Pazeller

1,448
18
28

score 4 · Answer 4 · answered Jun 05 '14 at 18:28

The main difference is the overhead of calling the methods. Your first code sample (createQuery) just for simplicity makes one method call, while the the queryBuilder makes 4. At the end of everything, they come down to a string that has to be executed, first example you are giving it the string, and the other you are building it with multiple chained method calls.

If you are looking for a reason to use one over the other, that is a question of style, and what looks more readable. For me, I like the queryBuider most of the time, it provides well defined sections for the query. Also, in the past it makes it easier to add in conditional logic when you need it.

A small observation - I would say that almost any time one spends in any number of PHP function calls, related to SQLing, will always be less critical than the time spent talking to, waiting for and pulling the actual result from the DB (not to mention hydrating those, in case of ORMs). — userfuser, Jan 30 '18 at 09:03

score 2 · Answer 5 · answered Apr 07 '15 at 17:12

It might be easier to unit test when using the query builder. Let's say you have a repository that queries for some data basing on the complicated list of conditions. And you want to assure that if a particular condition is passed into the repository, some other conditions are added into the query. In case of DQL you have two options:

1) To use fixtures and test the real interaction with DB. Which I find somewhat troublesome and ununitestish.

2) To check the generated DQL code. Which can make your test too fragile.

With QueryBuilder, you can substitute it with mock and verify that "andWhere" method with needed parameter is called. Of course such considerations are not applicable if your query is simple and not depended on any parameters.

doctrine: QueryBuilder vs createQuery?

5 Answers5

Linked