[MGNLGQL-147] Add a possibility to make case insensitive query Created: 24/Jul/23  Updated: 18/Oct/23  Resolved: 12/Sep/23

Status: Closed
Project: Magnolia GraphQL
Component/s: None
Affects Version/s: None
Fix Version/s: 2.0.0, 1.1.5

Type: Improvement Priority: Neutral
Reporter: Ondrej Chytil Assignee: Anh Vu
Resolution: Done Votes: 0
Labels: None
Σ Remaining Estimate: 0d Remaining Estimate: 0d
Σ Time Spent: 9d 6.5h Time Spent: 9d 5.5h
Σ Original Estimate: Not Specified Original Estimate: Not Specified

Attachments: PNG File image-2023-08-25-11-09-58-086.png     PNG File image-2023-08-25-13-33-40-227.png     PNG File image-2023-08-25-13-46-55-116.png     PNG File image-2023-08-25-13-48-44-345.png    
Issue Links:
Problem/Incident
Relates
relates to MGNLREST-198 Provide a case-insensitive LIKE filter Closed
causality
is causing MGNLGQL-165 DOC: Example for the Case-insensitive... Closed
Sub-Tasks:
Key
Summary
Type
Status
Assignee
MGNLGQL-158 Implement Sub-task Closed Anh Vu  
MGNLGQL-159 Review Sub-task Closed Oanh Thai Hoang  
MGNLGQL-160 piQA Sub-task Closed Phong Le Quoc  
MGNLGQL-161 QA Sub-task Closed Oanh Thai Hoang  
MGNLGQL-162 DOC: Document the Case-insensitive im... 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:
Visible to:
Matt Demusz
Epic Link: GraphQL Phase 2
Sprint: DevX 46
Story Points: 3
Team: DeveloperX
Work Started:
Approved:
Yes

 Description   

Right now both = and LIKE operators are respecting the case sensitivity. It should be possible to optionally switch the case sensitivity off.



 Comments   
Comment by Anh Vu [ 25/Aug/23 ]

Discovery output:

I found two approaches to support case insensitive queries for String filter:

Approach 1:

The current syntax for String comparison is "property operator STRING", the "CI" flag can be added to the end of the syntax to specify the query is a case insensitive query or not, it would be "property operator STRING CI?". If the "CI" flag exists,  it is case insensitive, otherwise it is case sensitive.

To build a case insensitive comparison, both property and string query will be lowercased. 

Pros and Cons:
Pros:

  • Work with all operators: =, <, ><=, >=, <>, LIKE

Cons:

  • The syntax is quite not friendly

 

Here is the draft PR for this approach, and bellow are examples for this:

Ex1: with LIKE operator

The query statement is 

SELECT * FROM [lib:book] AS t WHERE LOWER(t.title) LIKE '%hello%' AND t.ed = true 

 

Ex2: with = operator

The query statement is 

JCR-SQL2 query to be executed: SELECT * FROM [lib:book] AS t WHERE LOWER(t.title) = 'hello x1' AND t.ed = true

 

Approach 2:

A new operator 'ILIKE' will be supported, it is a case insensitive 'LIKE' operator.
To build a case insensitive comparison, both property and string query will be lowercased the same way as the Approach 1.

Pros and Cons:
Pros:

  • The syntax is friendly, ILIKE is popularly used

Cons:

  • The approach only supports case insensitive for LIKE operator. For the = operator users can work around with ILIKE but case sensitive queries for the rest of operators <, ><=, >=, <> will not be supported

 

Here is the draft PR for this approach, and bellow is an example for this:

The query statement is 

JCR-SQL2 query to be executed: SELECT * FROM [lib:book] AS t WHERE LOWER(t.title) LIKE 'hello%' AND t.ed = true

 

Conclusion
Since 'ILIKE' syntax is commonly used and more user friendly, from my point of view the Approach 2 would be a good option.

 

Comment by Robert Šiška [ 30/Aug/23 ]

Agree to go with option #2.

Generated at Mon Feb 12 05:53:02 CET 2024 using Jira 9.4.2#940002-sha1:46d1a51de284217efdcb32434eab47a99af2938b.