mistic100 / mistic100/jQuery-QueryBuilder

Support of additional ESCAPE option for Oracle and MSSQL support to prevent empty results

Open
#971 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
JavaScript
Stars
1.7k
Forks
544
PR merge metrics
No merged PRs in 30d

Description

First of all I'm new to github and not that experienced with the processes here.
The query builder is a great tool and I would like to address issues this has with the wildcard characters _ and % when the queries formed by the query builder are submitted to an Oracle or MSSQL Server DBMS.
So while the like operators, e.g. "contains" will have the wildcards escaped to _ and % but without the additional specification of the ESCAPE character it will not work in Oracle and MSSQL in my environment.
E.g.: name LIKE '%TEST_%' would have to be converted as follows: name LIKE '%TEST_%' ESCAPE '' to find "TEST_TEST"
This would be also compatible with PostGreSQL.

For building the sql, I would add the following lines at the line 5968 in query-buider.standalone.js:

if (rule.operator == 'begins_with' || rule.operator == 'not_begins_with' || rule.operator == 'contains' || rule.operator == 'not_contains' || rule.operator == 'ends_with' || rule.operator == 'not_ends_with') {
                                value += " ESCAPE '\\'"
}

However, the adjustment in the sqlparser is missing to be able to interpret this ESCAPE '' statement again.
Is there any chance that this will be addressed by you?

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with query-buider.standalone.js around line 5968 to inspect SQL generation for the listed wildcard operators, then trace the SQL parser mentioned in the issue to determine how ESCAPE '' is handled. Done means Oracle and MSSQL queries no longer return empty results for escaped wildcard searches while remaining compatible with PostgreSQL.

Written by the indexing model from the issue text.

Assessment

Tech stack
javascript, jquery, sql
Domain
databases, frontend
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.