Doctrine query builder – where IN with non string column names can’t be generated

Tags: , , , ,



I can’t sleep because of this, I either must be missing something really obvious or it can’t be done like that.

I have this Doctrine Query Builder thingy:

    $this->queryBuilder
        ->where('entity.id != ' . $id)
        ->andWhere(
            $this->queryBuilder->expr()->andX(
                $this->queryBuilder->expr()->in(":validatedValue", ['slug', 'title', 'content'])
            )
       )
       ->setParameter('validatedValue', $value);

Now it produces something like this:

SELECT
    p0_.id AS id_0,
    p0_.title AS title_1,
    p0_.teaser AS teaser_2,
    p0_.content AS content_3,
    p0_.slug AS slug_4
FROM
    posts p0_
WHERE
    p0_.id <> 1
    AND 'my-string-value-something something' IN('slug', 'title', 'content')

I have a problem with this particular line:

AND 'my-string-value-something something' IN('slug', 'title', 'content')

I want to be able to check actual columns so I have to produce something like this:

AND 'my-string-value-something something' IN(slug, title, content)

As you have noticed the correct version I want to have, it doesn’t have any quotes around field names if that makes sense. If they will be there it will be just treated as a string and not as an actual column in the table.

I can’t seem to produce that with this query builder. I have tried all kinds of tricks and nested expr() and nothing I tried worked.

Does anyone have any idea at all how I can do that with a builder? Can I do that with the builder or should I just use DQL or plain SQL for that?

Answer

In terms of doing what I wanted to do, I have discovered that it is simply not possible in a way a wanted to do that.

I think that by the looks of it doctrine will always wrap my elements in the array into quotes. I don’t think it can be switched off. Even if there is some clever different way of dealing with that it seems like too much effort for such a simple thing.

/**
     * Creates an IN() expression with the given arguments.
     *
     * @param string $x Field in string format to be restricted by IN() function.
     * @param mixed  $y Argument to be used in IN() function.
     *
     * @return ExprFunc
     */
    public function in($x, $y)
    {
        if (is_array($y)) {
            foreach ($y as &$literal) {
                if ( ! ($literal instanceof ExprLiteral)) {
                    $literal = $this->_quoteLiteral($literal);
                }
            }
        }

        return new ExprFunc($x . ' IN', (array) $y);
    }

This is exactly how I get the unwanted bunch of quotes from Doctrine.

$literal = $this->_quoteLiteral($literal);

A for how I solved my problem, I did exactly what @Bananaapple suggested in the comment in my post. So now my code looks like this:

// build conditions to determine which fields should be checked for the value
foreach ($constraint->fields as $field) {
    $fieldsConditions[] = $this->queryBuilder->expr()->eq('entity.' . $field, ':value');
}


// we should always have fields as this is taken care of before we even get to this point
if (!empty($fieldsConditions)) {
    $this->queryBuilder->andWhere(
        $this->queryBuilder->expr()->orX(...$fieldsConditions)
    )->setParameter('value', $value);
 }

I hope this will help someone.



Source: stackoverflow