How do I create an efficient DQL statement, to match my efficient SQL when doing a simple LEFT JOIN?

Question

I can craft a simple SQL that returns 1 when there is an item with requested id and corresponding model that matches FS-%, and 0 otherwise.

But when I try to write it as DQL, I fail in all spectacular ways. See EXPLAIN results below.

Question: How do I write an efficient DQL?

SQL (efficient)

select count(*) 
from item 
left join product on item.product_id = product.id 
where item.id=2222 and product.model like "FS-%";

Using Explain:

+----+-------------+---------+-------+--------------------+---------+---------+-------+------+-------+
| id | select_type | table   | type  | possible_keys      | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+-------+--------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | item    | const | PRIMARY,product_id | PRIMARY | 4       | const |    1 |       |
|  1 | SIMPLE      | product | const | PRIMARY            | PRIMARY | 4       | const |    1 |       |
+----+-------------+---------+-------+--------------------+---------+---------+-------+------+-------+

DQL (NOT efficient)

 $this->getEntityManager()
        ->createQueryBuilder()
        ->select('count(i)')
        ->from(Item::class, 'i')
        ->leftJoin(Product::class, 'p')
        ->where('i.id = :id')
        ->andWhere('p.model like :model')
        ->setParameter('id', 2222)
        ->setParameter('model', 'FS-%')
        ->getQuery()->getSingleResult();

Resulting SQL:

SELECT * FROM item i0_ LEFT JOIN product p1_
        ON (i0_.id = 409264 AND p1_.model LIKE 'FS-%');

Using Explain:

+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | i0_   | ALL  | NULL          | NULL | NULL    | NULL | 276000 |       |
|  1 | SIMPLE      | p1_   | ALL  | NULL          | NULL | NULL    | NULL |    564 |       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
2 rows in set (0.00 sec)            

Note: I used https://stackoverflow.com/a/25887143/2883328 to help me write the DQL.

Answer 1

in your case I would have tried this query:

$this->getEntityManager()
        ->createQueryBuilder()
        ->select('count(i)')
        ->from(Item::class, 'i')
        ->leftJoin(Product::class, 'p', 'WITH', 'i.product = p.id')
        ->where('i.id = :id')
        ->andWhere('p.model like :model')
        ->setParameter('id', 2222)
        ->setParameter('model', 'FS-%')
        ->getQuery()->getSingleScalarResult();

change product in i.product to your property name