How to create an efficient DQL statement to match my efficient SQL while executing a simple LEFT JOIN?

Question

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

But when I try to write it as DQL, I fail in all impressive ways. The following are the results of EXPLAIN .

Question: How to write effective 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-%"; 

Explanation Use:

 +----+-------------+---------+-------+--------------------+---------+---------+-------+------+-------+ | 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 effective)

  $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(); 

SQL result:

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

Explanation Use:

 +----+-------------+-------+------+---------------+------+---------+------+--------+-------+ | 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 have used https://stackoverflow.com/a/3126268/ to help me write DQL.

Answer 1

in your case, I would try 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 the product in i.product to your property name