如何创建一个高效的DQL语句,以便在执行简单的LEFT JOIN时匹配我的高效SQL? [英] How do I create an efficient DQL statement, to match my efficient SQL when doing a simple LEFT JOIN?
问题描述
我可以制作一个简单的SQL,当有一个需要 id
的项目和相应的$ $时,返回 1
c $ c>型号匹配 FS - %
,而 0
否则。 p>
但是当我尝试将其写成DQL时,我失败了所有壮观的方式。请参阅 EXPLAIN
结果。
问题:如何编写高效的DQL?
SQL(高效)
select count(*)
从项目
left join product on item.product_id = product.id
其中item.id = 2222和product.model,如FS-%;
使用说明:
+ ---- + ------------- + --------- + ------- + ------ -------------- + --------- + --------- + ------- + ------ + ------- +
| id | select_type |表|类型| possible_keys |关键| key_len |参考|行|额外|
+ ---- + ------------- + --------- + ------- + -------- ------------ + --------- + --------- + ------- + ------ + - ----- +
| 1 | SIMPLE |项目| const | PRIMARY,product_id |主要| 4 | const | 1 | |
| 1 | SIMPLE |产品| const |主要|主要| 4 | const | 1 | |
+ ---- + ------------- + --------- + ------- + -------- ------------ + --------- + --------- + ------- + ------ + - ----- +
DQL(not efficient)
$ this-> getEntityManager()
- > createQueryBuilder()
- > select('count(i)')
- > from(Item :: class,'i')
- > leftJoin(Product :: class,'p')
- > where('i.id = ')
- > andWhere('p.model like:model')
- > setParameter('id',2222)
- > setParameter('model','FS - %')
- > getQuery() - > getSingleResult();
结果SQL:
SELECT * FROM item i0_ LEFT JOIN product p1_
ON(i0_.id = 409264 AND p1_.model LIKE'FS-%');
使用说明:
+ ---- + ------------- + ------- + ------ + --------- ------ + ------ + --------- + ------ + -------- + ------- +
| id | select_type |表|类型| possible_keys |关键| key_len |参考|行|额外|
+ ---- + ------------- + ------- + ------ + ----------- ---- + ------ + --------- + ------ + -------- + ------- +
| 1 | SIMPLE | i0_ |全部| NULL | NULL | NULL | NULL | 276000 | |
| 1 | SIMPLE | p1_ |全部| NULL | NULL | NULL | NULL | 564 | |
+ ---- + ------------- + ------- + ------ + ----------- ---- + ------ + --------- + ------ + -------- + ------- +
2行(0.00秒)
注意:我使用 https://stackoverflow.com/a/25887143/2883328 以帮助我编写DQL。
在你的情况下,我会尝试这个查询:
$ this-> getEntityManager ()
- > createQueryBuilder()
- > select('count(i)')
- > from(Item :: class,'i')
- > leftJoin(Product :: class,'p','WITH','i.product = p.id')
- >其中('i.id =:id')
- > andWhere('p.model like:model')
- > setParameter('id',2222)
- > setParameter('model','FS-%')
- > getQuery() - > getSingleScalarResult();
将 i.product
中的产品更改为您的属性名称
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.
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
这篇关于如何创建一个高效的DQL语句,以便在执行简单的LEFT JOIN时匹配我的高效SQL?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!