Consistent behavior accross DB Engines #9324
Unanswered
stackcoder
asked this question in
Q&A
Replies: 2 comments 4 replies
-
I for one am very interested in a solution that actually works across all engines. |
Beta Was this translation helpful? Give feedback.
0 replies
-
Good News: The perfect solution is already in LaravelSince the shipped Laravel version already contains the new whereLike clause we just need to replace I can confirm applying following patch to my environment improves the search drastically: --- a/app/Helpers/Collector/GroupCollector.php
+++ b/app/Helpers/Collector/GroupCollector.php
@@ -944,7 +944,7 @@
static function (EloquentBuilder $q1) use ($array): void {
foreach ($array as $word) {
$keyword = sprintf('%%%s%%', $word);
- $q1->where('transaction_journals.description', 'LIKE', $keyword);
+ $q1->whereLike('transaction_journals.description', $keyword);
}
}
); Sure, there are some more occurrences which should be replaced. For now my quick code search shows 63 results in 23 files. Sorry for mention Doctrine above - Doctrine is not (anymore) referenced by Composor. |
Beta Was this translation helpful? Give feedback.
4 replies
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
Support guidelines
Description
Sorry for re-opening this controversial topic. I'm pretty sure everyone agrees on this:
A Search that requires matching the exact letter case is unexpected and not the biggest help.
Of course, I'm aware of those many closed and locked issues #2552, #8854, #8187, #7769, #4979, #4961, and the FAQ. Personally, I'm an everyday user of this application, and just migrated to PostgreSQL, but this little detail doesn't satisfy me. Using the same DB engine for all other applications saves me time and valuable resources. All things on my computer are open source, this is definitely something we can fix! And I want to contribute constructively.
Solution
I've looked into some threads, first I tried to "configure it away" using the Postgres V12
collation
feature as pointed out by @chevdor in #4961.Unfortunately, nondeterministic collations are not supported for LIKE and will result in error.
So we have various options still to go:
Patching PostgreSQL
Maintenance is not worth it. Plus, any user-made change to the DB is not covered by automated testing and can cause the next update to fail.
Make Doctrine ORM render
LIKE
asILIKE
on PostgreSQLThis way PostgreSQL would behave more like MariaDB.
Extend Doctrine ORM with a custom
ILIKE FunctionNode
and explicitly define which comparison should be case insensitive.Might be technologically the most correct way to implement, without performance impacts, but requires some effort.
Use
lower()
inside the queryThe most pragmatic approach was already suggested by @arnvald. My candidate for hot fixing my system without fear of feature incompatibility.
Can we agree, that the application is the only thing that knows if a comparison should be case-sensitive or not? Neither the ORM nor the DB Engine can understand what the user expects. In my experience, most applications go with Option 3 or 4. I also had a very brief look in NextCloud one of the most popular PHP applications out there.
What are alternatives?
The worst option ever would be entirely dropping PostgreSQL support and migrating back to MariaDB. Integrating something like ElasticSearch is more an overkill than a simple solution and should be optional. I would be happy to have case-insensitive string contains again.
Additional context
No response
Beta Was this translation helpful? Give feedback.
All reactions