您是否可以强制学说查询构建器不期望where子句中的操作数？

Question

In SQL it's possible to make a where statement that is true, without actually writing 'column = true'. Instead you can use 'column' and the query will then return all the rows where it's true.

As far as I can figure out, this isn't a possibility in doctrine's query builder and when I try anything similar, the following QueryException is thrown:

[Syntax Error] line 0, col 428: Error: Expected =, <, <=, <>, >, >=, !=, got 'AND'

Are there any way to get around this issue? It has to be a solution involving the query builder.

Edit:

Here's the code in question:

$qb->andWhere("MBRContains(:linestring, location.position) = true")

which I would very much like to be:

$qb->andWhere("MBRContains(:linestring, location.position)")

The issue is that MBRContains doesn't use the spatial index in the first case. MBRContains is from a doctrine extension, but the issue could be simplified down to:

$qb->andWhere("account.email_validated")

which wouldn't work either.

Answer 1

Since I found a solution, I figured I'd post it here, in case others should experience the same issue.

You're gonna need a custom SqlWalker, which you will have to apply to your query. Since I'm using the QueryBuilder, I had to get the query before I could apply the walker. Anyway here's the code:

$query = $qb->getQuery;
$query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, CustomSqlWalker::class);
$query->setHint(UseIndexWalker::HINT_REMOVE_EQUAL_TRUE, true);

With the SqlWalker looking like this:

<?php
namespace YOUR_NAME_SPACE;

use Doctrine\ORM\Query\SqlWalker;

class CustomSqlWalker extends SqlWalker {
    const HINT_REMOVE_EQUAL_TRUE = 'CustomSqlWalker.RemoveEqualTrue';

    public function walkWhereClause($whereClause) {
        $result = parent::walkWhereClause($whereClause);

        if ($this->getQuery()->getHint(self::HINT_REMOVE_EQUAL_TRUE)) {
            $result = preg_replace('/= 1/', '', $result);
        }

        return $result;
    }
}