How to use strings in the where clause of createQuery in Doctrine?

Question

I am trying to query the number of entries from a database using Doctrine.

    $query = $em->createQuery('SELECT COUNT(p.id) FROM AppBundle:Product p where p.live = 1');
    $count = $query->getSingleScalarResult();

I would like to extend the where clause and use "p.supplier == adidas".

How to add this to the query?

I could use findBy(array("live" => 1, "supplier" => "adidas")) but I surmise that findBy is much slower than the direct Mysql query. Am I right?

The difference with findBy has nothing to do with the fact this is a direct query (which is not, still DQL). A count just performs faster than a collection retrieving. — Bloops, Aug 30 '19 at 04:59
Possible duplicate of https://stackoverflow.com/questions/19103699/doctrine-counting-an-entitys-items-with-a-condition ? — Manzolo, Aug 30 '19 at 07:01
When using DQL, make sure that your whole query is double quoted, and strings in query are single quoted. DQL won't works with double quoted strings in query. — Preciel, Aug 30 '19 at 08:02

score 2 · Answer 1 · answered Aug 30 '19 at 04:51

2

Ideally you should use parameters:

$query = $em->createQuery('SELECT COUNT(p.id) FROM AppBundle:Product p WHERE p.live = :live AND p.supplier = :supplier')
    ->setParameters([
        'live' => 1,
        'supplier' => 'adidas'
    ]);

answered Aug 30 '19 at 04:51

Bloops

744
1
11
15

This is a better answer, do not hardcode or inject parameters in your query string, use parameters – Florent Destremau Aug 30 '19 at 10:29
Yes this is a better way, for a dynamic query to properly sanitize / escape user provided parameters and prevent sql injection, but if the parameters are static, safe, datum why add the cost of calling additional methods? – Arleigh Hix Aug 30 '19 at 18:55
Extra cost really? We're talking nanoseconds here... It's just much cleaner to separate the query logic from the parameters, static or not. – Bloops Aug 31 '19 at 08:29
Also don't forget that every query can be cached as every query results. Always choose an evolutive code over a hardcoded code. Speed optimization at language level is never a good sign of good software architecture. This should never be a developer's concern until the app is running, working, bug free and already making money. – Bloops Aug 31 '19 at 08:36

Arleigh Hix · Answer 2 · 2019-08-30T18:59:13.080

You should just have to enclose the multiple conditions inside parenthesis as below:

$query = $em->createQuery("SELECT COUNT(p.id) FROM AppBundle:Product p WHERE (p.live = 1 AND p.supplier = 'adidas')");

This should be faster than the repository findBy() method because you are defining a hard-coded query instead of having doctrine build the query, that being said, it is also much less flexible. This should be fine if the parameters of the query will never need to be changed.

How to use strings in the where clause of createQuery in Doctrine?

2 Answers2