The inSet (FIND_IN_SET) function generates invalid SQL
When building a custom query with getQueryBuilderForTable using the The inSet function in ExpressionBuilder.php, the SQL is wrong because the function returns the pair of fieldName - value in reverse order.
The solution is to change the line 364 of ExpressionBuilder.php to
return sprintf( 'FIND_IN_SET(%s, %s)', $this->connection->quoteIdentifier($fieldName), $value );
or to use plain sql to the query builder: 'FIND_IN_SET(el.element_type, '. $allowedTypes . ')', but with the addition single quotes in the argument string : "'x,c,z'"
Updated by Morton Jonuschat over 4 years ago
- Category set to Database API (Doctrine DBAL)
- Status changed from New to Needs Feedback
- Priority changed from Must have to -- undefined --
The inSet function as provided by the ExpressionBuilder is meant to find database rows which have a certain value $value in a column $fieldName which contains a list of comma separated values. InSet() is used quite frequently in the core and is almost certainly working as expected.
If you are trying to find all rows which have a single value in a column which is a member of a set of values you should b using the ->in() expression.
Otherwise a comprehensive code example/a complete example of what you are trying to accomplish might be helpful.
Updated by Thanos no-lastname-given over 4 years ago
- TYPO3 Version changed from 9 to 8
- PHP Version set to 7.1
I made an custom sql that looks like this:
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('table'); $queryBuilder->getRestrictions()->removeAll(); $queryBuilder->select('el.uid') ->from('table', 'el') ->where($queryBuilder->expr()->inSet('el.element_type', $queryBuilder->createNamedParameter($allowedTypes,\PDO::PARAM_STR)));
SELECT `el`.`uid` FROM `table` `el` WHERE FIND_IN_ SET(:dcValue1, `el`.`element_type`)
which is an incorrect sql statement.
If i reverse the order of the return arguments inside the inSet function then i get:
SELECT `el`.`uid` FROM `table` `el` WHERE FIND_IN_ SET(`el`.`element_type`, :dcValue1)
which is a valid sql query for the MySql that returns the correct values.
Updated by Alexander Opitz over 4 years ago
As Morton already wrote, you are using the function wrong.
The two parameters of inSet which you are using inSet(string $fieldName, string $value, ...) are defined:
$fieldName ... The name of the field, which contains a comma separated string/set which will be searched for.
$value ... The element which is searched inside this set (This string SHOULD NOT contain commas).
As you are using a NamedParameter you do not get an exception while calling the inSet method as the NamedParameter can't be quantified at that moment.
You should use the in() method which is defined as:
$fieldName The field to search in
$value ... The comma separated string/set( or array which will be imploded) for which will be searched inside the given field.