Doctrine: ORM QueryBuilder or DBAL QueryBuilder

Question

DBAL Query Builder: https://www.doctrine-project.org/projects/doctrine-dbal/en/4.2/reference/query-builder.html

ORM Query Builder: https://www.doctrine-project.org/projects/doctrine-orm/en/3.3/reference/query-builder.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)

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.