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?
Advertisement
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.