Tutorials & ManualsSQL YogaSQL Yoga SQL Query ObjectsHow Can I Convert a User Search String Into An AND/OR Search When Generating a Query?

How Can I Convert a User Search String Into An AND/OR Search When Generating a Query?

Allowing a user to search a database for certain strings is a common operation. Creating a SQL query based on a string entered by the user can be a bit of work, however. SQL Yoga comes to the rescue with some helper commands that can parse strings for you automatically and convert them into search conditions for a SQL Query object.

sqlquery_setConditionsFromUserSearchString

When working with SQL Query objects you can use sqlquery_setConditionsFromUserSearchString to set the "conditions" property of a SQL Query based on text a user enters.

In the following examples you will see results based on various search strings that were entered. The code to perform the search looks like this:

put sqlquery_createObject("todo_items") into theQueryA

put the text of field "Search" into theSearchString

put "todo_items.name contains :1" into theSearchCondition

sqlquery_setConditionsFromUserSearchString theQueryA, theSearchString, theSearchCondition

Basically the text that the user enters in the "Search" field will be parsed by SQL Yoga and inserted into the search condition that has been defined.

Results With No Search Term

Results With No Search Term

Here is what the results look like when no search term has been entered in the Search field.

Single Word Search

Single Word Search

If a single word is entered then any record that contains that word will be returned. Here you can see that two to-do items contain the word install.

Multiple Word Search

Multiple Word Search

If more than one word is used then the words are split up and results that contain all of the words are returned. Here you can see that no records contain both the word gnome AND the word windows.

User Defined Boolean Searches

User Defined Boolean Searches

If the search term enters the words AND or OR then the search takes that into account. Here you can see that records that contain the word gnome OR the word windows are returned.

Exact Matches

Exact Matches

If the search term is wrapped in quotes then an exact match is searched for rather than both words. Here you can see that there are no records that contain the phrase install gnome.

If the quotes are removed then a result is returned as there is one record that contains the word install and the word gnome.

0 Comments

Add your comment

E-Mail me when someone replies to this comment