[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: PNG File image-2023-09-15-16-53-22-246.png    
Issue Links:
Relates
relates to MGNLGQL-147 Add a possibility to make case insens... Closed
causality
documentation
to be documented by MGNLREST-754 DOC: Example for ILIKE REST operator Closed
Sub-Tasks:
Key
Summary
Type
Status
Assignee
MGNLREST-749 Implement Sub-task Closed Anh Vu  
MGNLREST-750 Review Sub-task Closed Oanh Thai Hoang  
MGNLREST-751 piQA Sub-task Closed Dai Ha  
MGNLREST-752 QA Sub-task Closed Oanh Thai Hoang  
MGNLREST-753 DOCSub: Update Delivery docs for ILIKE Sub-task Closed Martin Drápela  
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: DeveloperX
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:
A new operator 'ilike' can be added to support a case-insensitive like filter.
The solution is that we will lowercase both filter properties and filter values while building filter expressions with the 'ilike' operator.
For example with the query: http://localhost:8080/magnoliaAuthor/.rest/delivery/test/book?description%5Bilike%5D=%25HELLO CAT%25

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
Behind the scenes the query statement is: 

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

Generated at Mon Feb 12 06:57:36 CET 2024 using Jira 9.4.2#940002-sha1:46d1a51de284217efdcb32434eab47a99af2938b.