Feature #23087
closed
FIND_IN_SET should be used instead of complicated LIKE where clause
0%
Description
MySQL supports the FIND_IN_SET operator to efficiently find records having a given value in a set of values (comma-separated list of values). This is typically used with usergroups. This method should be used instead of doing some complicated LIKE query.
Solution:
Enhance the SQL query to support this operator and rewrite it if needed for other DBMS. Use a dedicated DBMS function whenever possible (PostgreSQL / MSSQL). For instance:
MSSQL: http://mschat.net/blog/index.php?topic=8
PostgreSQL: http://archives.postgresql.org/pgsql-sql/2009-12/msg00033.php
(issue imported from #M14985)
Files
Updated by Xavier Perseguers over 14 years ago
PostgreSQL compatibility function was provided by R. van Twisk over Skype during T3DD10. Thank you!
Updated by Xavier Perseguers over 14 years ago
For Oracle the big LIKE clause should be replaced by:
WHERE ',' || field || ',' LIKE '%,11,%'
Updated by Xavier Perseguers over 14 years ago
Compatibility function for MSSQL does not work great and furthermore function call in MSSQL needs to be "user"-prefixed, that's not yet supported by DBAL => use same method as my comment #40348, with "+" instead of "||"
Updated by Xavier Perseguers over 14 years ago
Committed to:
- Core trunk (rev. 8196)
- DBAL trunk (rev. 35742)