Question

In Doctrine you can create DQL in 2 ways:

EntityManager::createQuery:

$query = $em->createQuery( SELECT u FROM MyProjectModelUser 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?

Answer 1

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.

Answer 2

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( ProjectEntitiesItem ,  i )
            ->select("i, e")
            ->join("i.entity",  e )
            ->where("i.lang = :lang AND e.album = :album")
            ->setParameter( lang , $lang)
            ->setParameter( album , $album);

Answer 3

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.

Answer 4

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.

Answer 5

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.

友情链接