Doctrine QueryBuilder 删除连接

2022-01-03 00:00:00 php mysql doctrine-orm query-builder

我正在尝试使用 Doctrine QueryBuilder 来执行以下 SQL 查询:

I'm trying to use the Doctrine QueryBuilder to perform the following SQL query:

DELETE php FROM product_hole_pattern php
INNER JOIN hole_pattern hp ON php.hole_pattern_id = hp.id
INNER JOIN hole_pattern_type hpt ON hp.hole_pattern_type_id = hpt.id
WHERE php.product_id = 4 AND hpt.slug='universal';

我有这个

$qb = $this->entityManager->createQueryBuilder();
$query = $qb->delete('SANUSEntityProductHolePattern', 'php')
  ->innerJoin('php.holePattern', 'hp')
  ->innerJoin('hp.holePatternType', 'hpt')
  ->where('hpt.slug = :slug AND php.product=:product')
  ->setParameter('slug','universal')
  ->setParameter('product',$this->id)
  ->getQuery();

但我明白了:

[Semantical Error] line 0, col 50 near 'hpt.slug = :slug': Error: 'hpt' is not defined.

错误信息自带的DQL是:

The DQL that comes with the error message is:

DELETE SANUSEntityProductHolePattern php 
WHERE hpt.slug = :slug AND php.product=:product

所以连接似乎被完全省略了.

So the joins seem to be omitted completely.

推荐答案

使用 IN 条件运行查询可能比迭代更好.

It may be better to run a query with IN condition rather than iterating.

$ids = $this->createQueryBuilder('product')
->join('..your joins..')
->where('..your wheres..')
->select('product.id')
->getQuery()->getResult();

$this->createQueryBuilder('product')
    ->where('product.id in (:ids)')
    ->setParameter('ids', $ids)
    ->delete()
    ->getQuery()
    ->execute();

  • 优点:运行速度更快，无需迭代
  • 缺点:你不能挂钩 preRemove

    • 至于把它放在哪里"的激烈争论，如果你愿意，敢于把它放在控制器中.这完全取决于你.但是，如果您将代码放在专用的学说存储库类中，那么将来可能对您更有用.它应该很容易做到，并且易于更改/维护.

    As to heated "where to put it" debate, dare to put it in the controller if you like. That's completely up to you. However, it may be more useful to you in the future if you land the code in the dedicated doctrine repository class. It should be very easy to do and makes it easy to change / maintain.

相关文章