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

i can craft simple sql returns 1 when there item requested id , corresponding model matches fs-%, , 0 otherwise.

but when try write dql, fail in spectacular ways. see explain results below.

question: how write efficient dql?

sql (efficient)

select count(*)  item  left join product on item.product_id = product.id  item.id=2222 , product.model "fs-%"; 

using explain:

+----+-------------+---------+-------+--------------------+---------+---------+-------+------+-------+ | id | select_type | table   | type  | possible_keys      | key     | key_len | ref   | rows | | +----+-------------+---------+-------+--------------------+---------+---------+-------+------+-------+ |  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 :model')         ->setparameter('id', 2222)         ->setparameter('model', 'fs-%')         ->getquery()->getsingleresult(); 

resulting sql:

select * item i0_ left join product p1_         on (i0_.id = 409264 , p1_.model 'fs-%'); 

using explain:

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

note: used https://stackoverflow.com/a/25887143/2883328 me write dql.

in case have tried 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 :model')         ->setparameter('id', 2222)         ->setparameter('model', 'fs-%')         ->getquery()->getsinglescalarresult(); 

change product in i.product property name