[MGNLREST-198] Provide a case-insensitive LIKE filter Created: 16/Jan/19 Updated: 18/Oct/23 Resolved: 28/Sep/23 |
|
| Status: | Closed |
| Project: | Magnolia REST Framework |
| Component/s: | None |
| Affects Version/s: | 2.1.1 |
| Fix Version/s: | 3.0.0, 2.2.23 |
| Type: | Improvement | Priority: | Neutral |
| Reporter: | Jonathan Ayala | Assignee: | Anh Vu |
| Resolution: | Fixed | Votes: | 3 |
| Labels: | None | ||
| Σ Remaining Estimate: | 0d | Remaining Estimate: | 0d |
| Σ Time Spent: | 4d 3.5h | Time Spent: | 4.25d |
| Σ Original Estimate: | Not Specified | Original Estimate: | Not Specified |
| Attachments: |
|
||||||||||||||||||||||||||||||
| Issue Links: |
|
||||||||||||||||||||||||||||||
| Sub-Tasks: |
|
||||||||||||||||||||||||||||||
| Template: |
|
||||||||||||||||||||||||||||||
| Acceptance criteria: |
Empty
|
||||||||||||||||||||||||||||||
| Task DoD: |
[X]*
Doc/release notes changes? Comment present?
[X]*
Downstream builds green?
[X]*
Solution information and context easily available?
[X]*
Tests
[X]*
FixVersion filled and not yet released
[ ] 
Architecture Decision Record (ADR)
|
||||||||||||||||||||||||||||||
| Release notes required: |
Yes
|
||||||||||||||||||||||||||||||
| Documentation update required: |
Yes
|
||||||||||||||||||||||||||||||
| Date of First Response: | |||||||||||||||||||||||||||||||
| Epic Link: | Support | ||||||||||||||||||||||||||||||
| Sprint: | DevX 47 | ||||||||||||||||||||||||||||||
| Story Points: | 3 | ||||||||||||||||||||||||||||||
| Team: | |||||||||||||||||||||||||||||||
| Work Started: | |||||||||||||||||||||||||||||||
| Approved: |
Yes
|
||||||||||||||||||||||||||||||
| Description |
|
Currently like operator from queryNodes filtering mechanism works only case-sensitively. It would be quite useful to provide a case-insensitive like operator as well. Note: Consider a mode to make the equals operator also case insensitive. |
| Comments |
| Comment by Will Scheidegger [ 24/Sep/19 ] |
|
"Quite useful" is a big understatement. The very slick query feature is currently pretty much useless in many cases, being case sensitive. Example: Get me all companies which contain "ski" in their name. Now you either search for "Ski" and you'll find "Skiers Magazine" or you'll search for "ski" and you'll find "Kananaskis Lodge". Both hits would be interesting for the avid skier. |
| Comment by Will Scheidegger [ 24/Sep/19 ] |
|
An easy (untested!) fix for the problem could be: public FilteringCondition(String propertyString, String valueString) { if (StringUtils.isEmpty(propertyString)) { throw new IllegalArgumentException("Property string is invalid."); } operator = extractOperator(propertyString); if (operator.equalsIgonerCase("caseinsensitivelike")) { // use const instead of literal property = String.format("lower(%s)", StringUtils.substringBeforeLast(propertyString, OPERATOR_OPEN)); } else { property = StringUtils.substringBeforeLast(propertyString, OPERATOR_OPEN); } values = valueString.split(VALUES_SPLITTER); validateValueString(); isDateQuery = isDateQuery(); if (isDateQuery) { rangeValues = extractDateRangeValues(values); } else { rangeValues = extractRangeValues(values); } } |
| Comment by Will Scheidegger [ 24/Sep/19 ] |
|
Hm... yeah, my "easy fix" was indeed untested. Only now I see that operator is an enum. Not only is everything private, has no accessor methods but also enums are used which cannot be extended. It looks like someone wanted to be absolutely certain that nobody can extend the functionality to suit their needs... |
| Comment by Anh Vu [ 15/Sep/23 ] |
|
Discovery output:
The query statement will contain the expression:
AND LOWER(t.description) LIKE '%hello cat%'
I found that currently users can make LIKE query with date and datetime. For datetime, the only "yyyy-MM-dd’T’HH:mm:ss.SSSXXX" format is accepted. For example, with the query: http://localhost:8080/magnoliaAuthor/.rest/delivery/test/book?publish_date%5Blike%5D=2023-09-13T00:00:00.000%2b07:00
... AND t.publish_date LIKE CAST('2023-09-13T00:00:00.000+07:00' AS DATE)
With the ILIKE operator both "yyyy-MM-dd’T’HH:mm:ss.SSSXXX" (with a upper 'T') and "yyyy-MM-dd’t’HH:mm:ss.SSSXXX" (with lowercase 't') will be supported. Because in this case we are comparing date, hence the property and the filter value do not need to be lowercased. Here is the draft PR. |
| Comment by Anh Vu [ 19/Sep/23 ] |
|
I agree with you, in addition the CAST( 'value' AS DATE) function only accepts "T", hence we should expect the correct date time format from clients with "T". The format "yyyy-MM-dd’t’HH:mm:ss.SSSXXX" will not be supported with ILIKE operator. |