[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: |
|
||||||||||||||||||||||||||||||
| 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: | |||||||||||||||||||||||||||||||
| Visible to: |
Matt Demusz
|
||||||||||||||||||||||||||||||
| Epic Link: | GraphQL Phase 2 | ||||||||||||||||||||||||||||||
| Sprint: | DevX 46 | ||||||||||||||||||||||||||||||
| Story Points: | 3 | ||||||||||||||||||||||||||||||
| Team: | |||||||||||||||||||||||||||||||
| 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:
Cons:
Here is the draft PR for this approach, and bellow are examples for this: Ex1: with LIKE operator SELECT * FROM [lib:book] AS t WHERE LOWER(t.title) LIKE '%hello%' AND t.ed = true
Ex2: with = operator 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. Pros and Cons:
Cons:
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
|
| Comment by Robert Šiška [ 30/Aug/23 ] |
|
Agree to go with option #2. |