Doctrine: ORM QueryBuilder or DBAL QueryBuilder

Question

DBAL Query Builder: http://www.doctrine-project.org/api/dbal/2.3/class-Doctrine.DBAL.Query.QueryBuilder.html

ORM Query Builder: http://www.doctrine-project.org/api/orm/2.3/class-Doctrine.ORM.QueryBuilder.html

Inside Doctrine there is 2 QueryBuilder that are quite similar, the DBAL one and the ORM one.

What is the difference between those 2? Is there a big overhead using the ORM queryBuilder compared to the DBAL one ?

(native query are out of question as I need to use dynamic where statement)

Kurtkurth

Answer 1

If you use ORM go with ORM one. Otherwise you should just use DBAL. Below you can find some details about what they actually do.

DBAL stands for Database Abstraction Layer. It tries to abstract as much of the database specific stuff like drivers or query syntax, so they can be interchangeable without the need for altering your code.

ORM stands for Object Relational Mapping this goes even further than DBAL, and attempts to bind the database schema to actual classes within your application.

Both of these come with a query builder. DBAL query builder is more basic, as it only provides a compatibility with several drivers/databases. You still operate on tables, and you still get rows as results. ORM query builder, on the other hand, is designed to work with entities (classes to which the database schema is bound).

For example, a DBAL query:

$builder->select('u.id, e.id, e.email')
    ->from('users', 'u')
    ->leftJoin('u', 'emails', 'e', 'u.id = e.user_id')
    ->where('u.id = :id')
    ->setParameter(':id', $id);

$rows = $builder->execute()->fetchAll();

Here, we create a query which joins the users table with the emails table, to get all email addresses of the user with id $id. The result will be in the form of an associative array. Now let's take a look at ORM:

$builder->select('u','e')
    ->leftJoin('u.emails', 'e')
    ->where('u.id = :id')
    ->setParameter(':id', $id);

$user = $builder->getQuery()->getOneOrNullResult();

Here, we say we want to get the User with id $id with all of his Email email addresses. We don't care what table they come from or what the relations between them are. What you get is an instance of User class with a collection of Email objects. All populated with the right data thanks to ORM mapping.

Yusem

Would also like to note that the ORM also supports native SQL queries and custom result-set mapping. For reporting purposes, aggregate data and similar, you should avoid the use of ORM Entities in favor of using table queries, to reduce the inherent overhead from the business logic that Entities provide when instantiated. – Valet

Answer 2

The first is for SQL queries, the second is for DQL ones.

Most often, get the QueryBuilder instance from the EntityManager::createQueryBuilder() (maybe through a Repository class, see EntityRepository;;createQueryBuilder()), in this case you'll use the Doctrine\ORM\QueryBuilder and so write DQLs.

If you need to run SQL queries, you'll use the Doctrine\DBAL\QueryBuilder, instance often retrieved from Doctrine\DBAL\Connection::createQueryBuilder() through EntityManager::getConnection().

You can read more about the benefits of use DQL over SQL in doctrine (and inverse).

Geier