Doctrine2 DBAL Exists 查询 [英] Doctrine2 DBAL Exists query

问题描述

我想就使用 QueryBuilder 构建的 Doctrine2 DBAL 查询寻求您的帮助.我已经习惯了 ORM，但我认为对于在侦听器中调用的此类查询来说，这是一种矫枉过正.

我需要使用 SELECT EXISTS 进行查询，但我不知道如何使用 DBAL QueryBuilder 构建它.

我已经创建了一个子查询:

$subQuery = $connection->createQueryBuilder();$subQuery->select('o.id')->from('order', 'o')->leftJoin('o', 'payment', 'p')->where($subQuery->expr()->isNull('p.id'));

我基本上是想检查是否有任何未付款的订单.我现在不知道如何构建 SELECT EXISTS 查询?任何人都可以指出我正确的方向吗?我在想这样的事情:

$qb->select('EXISTS(?)')->setParameter($subQuery->getDQL())

这是正确的解决方案吗?

@编辑

经过一段时间的思考，我决定改用 ORM.不幸的是，这也不起作用，我收到一个错误:

第 0 行，第 7 列:错误:预期已知函数，得到 'EXISTS'

DQL 是:SELECT EXISTS()

考虑到它是使用 QueryBuilder 构建的，这有点奇怪:

/* @var $qb QueryBuilder */$qb = $this->em->createQueryBuilder();$qb->select($qb->expr()->exists($subQuery->getDQL()));

解决方案

晚了几年，但您需要在 SELECT 或 EXISTS 子查询 SQL 中指定code>WHERE QueryBuilder 的语句部分，而不是使用参数.

此外，由于 order 是 MySQL 中的保留字，您需要使用标识符引号 `(反引号)来转义表名.

使用ORM时；您必须指定引用实体的 FROM 语句，因此您需要更改方法.

$connection = $this->em->getConnection();$expr = $connection->getExpressionBuilder();$qbSub = $connection->createQueryBuilder()-> 选择(['1'])->from('`顺序`', 'o')->leftJoin('o', '`payment`', 'p', $exor->eq('p.order_id', 'o.id'))-> where($expr->isNull('p.id'));/*** @return 字符串1"；如果记录存在，则0"除此以外*/$connection->createQueryBuilder()->select('EXISTS('.$qbSub->getSQL().')')-> 执行()-> fetchColumn();

<块引用>

注意:如果您有任何参数，则必须使用顶层的 QueryBuilder::setParameter() 绑定占位符的值查询，而不是子查询.

$qb->setParameter('name', $value)-> 执行();

结果 SQL

SELECT EXISTS(选择 1FROM `order` AS oLEFT JOIN `payment` AS pON p.order_id = o.id哪里 p.id 为空);

---

但是，我建议将您的查询从排除联接更改为带有 NOT EXISTS 的包含联接.这样做将从您的结果集中过滤已支付的订单.而不是尝试在每次付款时加入每个订单并检索返回 null 的付款.显着提高查询性能.

示例 db-fiddle

SELECT EXISTS (选择 1FROM `order` AS o2不存在的地方(选择空FROM `order` AS oINNER JOIN `payment` AS pON p.order_id = o.id哪里 o2.id = o.id))

I would like to ask for your help with Doctrine2 DBAL query built with QueryBuilder. I'm used to ORM, but I think it's an overkill for such query which is being called in a listener.

I need a query with SELECT EXISTS and I don't know how I can construct it using DBAL QueryBuilder.

I have a subquery already created:

$subQuery = $connection->createQueryBuilder();
$subQuery
    ->select('o.id')
    ->from('order', 'o')
    ->leftJoin('o', 'payment', 'p')
    ->where($subQuery->expr()->isNull('p.id'))
;

I basically want to check if there are any unpaid orders. I now have no idea how to build the SELECT EXISTS query? Can anyone point me in the right direction? I was thinking about something like this:

$qb->select('EXISTS(?)')->setParameter($subQuery->getDQL())

Will that be the correct solution?

@EDIT

After a while of thinking I decided to use ORM instead. Unfortunately that did not work either, I'm getting an error:

line 0, col 7: Error: Expected known function, got 'EXISTS'

The DQL is: SELECT EXISTS(<subquery here>)

It is a bit weird considering that It has been build with QueryBuilder:

/* @var $qb QueryBuilder */
$qb = $this->em->createQueryBuilder();
$qb
        ->select($qb->expr()->exists($subQuery->getDQL()));

解决方案

A few years late, but you need to specify your EXISTS subquery SQL within the SELECT or WHERE statement portion of the QueryBuilder, as opposed to using a parameter.

Additionally since order is a reserved word in MySQL, you will need to use identifier quotes ` (back-tick) to escape the table name.

When using the ORM; you must specify a FROM statement that references an entity, so you would need to change your approach.

$connection = $this->em->getConnection();
$expr = $connection->getExpressionBuilder();
$qbSub = $connection->createQueryBuilder()
    ->select(['1'])
    ->from('`order`', 'o')
    ->leftJoin('o', '`payment`', 'p', $exor->eq('p.order_id', 'o.id'))
    ->where($expr->isNull('p.id'));

/**
 * @return string "1" if a record exists, "0" otherwise
 */
$connection->createQueryBuilder()
    ->select('EXISTS(' . $qbSub->getSQL() . ')')
    ->execute()
    ->fetchColumn();

Note: If you have any parameters, the values for the placeholders must be bound using QueryBuilder::setParameter() on the top-level query, not the sub-queries.

$qb
    ->setParameter('name', $value)
    ->execute();

Resulting SQL

SELECT EXISTS(
   SELECT 1
   FROM `order` AS o
   LEFT JOIN `payment` AS p
   ON p.order_id = o.id
   WHERE p.id IS NULL
);

---

However, I suggest changing your query from an exclusion join to an inclusion join with NOT EXISTS. Doing so will filter orders that have been paid, out of your result-set. Instead of attempting to join every order on every payment and retrieve the payments that return null. Dramatically improving the performance of the query.

Example db-fiddle

SELECT EXISTS (
    SELECT 1
    FROM `order` AS o2
    WHERE NOT EXISTS(
        SELECT NULL
        FROM `order` AS o
        INNER JOIN `payment` AS p
        ON p.order_id = o.id
        WHERE o2.id = o.id
    )
)

这篇关于Doctrine2 DBAL Exists 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！