Building advanced SQL search from a user text input
We are going to build a program that parses user search query and serializes it into a SQL statement. Here is an example of a search query:
firstName:"Thomas" -jefferson birthdate:[1960 TO 1970] (profession:"inventor" OR profession:"engineer")
What this says is:
- Find records with first name “Thomas”
- Exclude mentions of “jefferson”
- Narrow down results to where the birthdate is between 1960 and 1970
- Narrow down results to where the profession is either “inventor” or “engineer”
In SQL, this could look something like:
"firstName" = 'Thomas' AND
"searchName" ILIKE '%jefferson%' AND
"birthdate" BETWEEN 1960 AND 1970 AND
"profession" = 'inventor' OR
"profession" = 'engineer'
It might not be the most succinct form of SQL to do the job, but it is good enough.