CODEWITHSUNDEEP
phpmysqlperformancedoctrine-ormquery-builder
schumi12345
schumi12345

Reputation: 31

use query builder or sql doctrine

i do some research to answer it but i don't find.

I'd like to know what is the best practice to do select (with join) between :

use query builder ?

$this->getEntityManager()->createQueryBuilder()
        ->select('e')
        ->from('Module\Entity\MyEntity 'e')
        ->innerJoin('Module\Entity\MyEntity2', 'e2', 'WITH', 'e.e2_id = e2.id')...
        ->where("...")

or

use SQL statement ?

$db = $this->getEntityManager()->getConnection();

$sql = "SELECT * FROM myEntity e
        INNER JOIN myEntity2 AS e2 ON e2.id = e.e2_id....
        WHERE ....;"

it is safer, faster,... ?

Upvotes: 3

Views: 2272

Answers (1)

SBH
SBH

Reputation: 1908

Both have advantages and disadvantages, so it depends on what you need.

SQL

  1. a pure SQL statement is a little bit faster, since you don't have to execute the additional logic coming from Doctrine
  2. since Doctrine tries to support a lot of different databases, some database specific functions are not supported, so you can't use them or have to implement them into Doctrine, which can become a lot of work

DQL

  1. Doctrine forces you to use prepared statements, making prevention of injection attacks easier to enforce as long as you insist on named parameters. But this can be done with pure SQL and PDO too, using named parameters.
  2. Working with entities is much easier. Data is automatically bound into objects and they are managed through your application. Of course this causes a hit with some performance overhead
  3. One disadvantage regarding joins is that you always join the whole table instead of maybe only the two columns you need. And you need relationships defined if the result should come in an object-useful way
  4. Biggest benefit is probably also rarest used: if you change your database you don't need to rewrite all your queries to match the new query structure

Sometimes I find myself in a situation where I could solve a problem directly in a SQL query, but Doctrine doesn't support some of the constructs I would have to use. So I have to decide if I want to lose the Doctrine benefits and go for the pure SQL solution or use DQL and add some more php code, maybe even more otherwise unnecessary queries. But this depends strongly on the situation and can not be answered in general.

In the end I would use DQL wherever possible because it's easier to write and maintain and only switch to SQL when I need some query to be high performance.

Upvotes: 3

Related Questions