我可以制作一个简单的 SQL,当存在具有请求id
的项目和与 FS-%
匹配的相应model
时,它会返回1
,否则0
。
但是当我试图把它写成DQL时,我以所有惊人的方式失败了。 请参阅下面的EXPLAIN
结果。
问:如何编写高效的 DQL?
SQL(高效)
select count(*)
from item
left join product on item.product_id = product.id
where item.id=2222 and product.model like "FS-%";
使用解释:
+----+-------------+---------+-------+--------------------+---------+---------+-------+------+-------+
| 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 (效率不高)
$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:
SELECT * FROM item i0_ LEFT JOIN product p1_
ON (i0_.id = 409264 AND p1_.model LIKE 'FS-%');
使用解释:
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| 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)
注意:我使用 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')
->where('i.id = :id')
->andWhere('p.model like :model')
->setParameter('id', 2222)
->setParameter('model', 'FS-%')
->getQuery()->getSingleScalarResult();
将产品更改为您的媒体资源名称i.product