Tutorials & ManualsSQL YogaSQL Yoga To-Do ApplicationAdding Search FunctionalityDefining Scopes To Make Aggregating Search Filters Easier

Defining Scopes To Make Aggregating Search Filters Easier

To finish up this tutorial we are going to take a look at how SQL Yoga makes managing search filters really easy.

Search Filters To Take Into Account

When displaying to-do items the application has to take three possible search filters into account.

1) Are to-do items being filtered by project or person?

2) Are there any search terms the user wants to filter the list by?

3) Should all to-do items be shown or just those that aren't completed?

The normal approach would be to have a number of conditional statements that built up a SQL WHERE clause. These tend to be hard to read and hard to debug as the number of search conditions grows.

Scopes to the Rescue

SQL Yoga provides a Scope object that makes working with search filters much simpler.

A Scope object allows you to define a very narrow search condition that can be used with a SQL Query object. Scopes are useful when you have a number of search conditions that you want to dynamically apply to a search. You can easily add in the scopes you need to the SQL Query object based on user input and SQL Yoga will generate a complete search condition for you based on all added Scopes.

Let's look at how to create Scope objects that will help us display to-do items based on the 3 criteria.

Defining A Scope Object

You define Scope objects in the table objects behavior script where you created Table and Relationship objects earlier. Edit the behavior script by viewing the card controls of the application stack (1) and editing the script of the SQL Yoga Table Objects Behavior button.

Add _CreateScopes Call

The first thing you need to do is add a call to _CreateScopes in the dbobject.createTables message. You can add it just after the _CreateRelationships call.

Update Behavior Script

Paste the following RevTalk code at the end of the behavior script.


Copy & Paste The Following Code


private command _CreateScopes

## Define scopes to help filter to-do results

## to-dos linked to a project

tblscope_createObject "todo_items", "of project"

put it into theScopeA

tblscope_set theScopeA, "related table joins", "LEFT OUTER JOIN people"

tblscope_set theScopeA, "conditions", "todo_items.project_id is :1"

## to-dos linked to a person

tblscope_createObject "todo_items", "of person"

put it into theScopeA

tblscope_set theScopeA, "related table joins", "people"

tblscope_set theScopeA, "conditions", "people.id is :1"

## to-dos that are not completed

tblscope_createObject "todo_items", "not completed"

put it into theScopeA

tblscope_set theScopeA, "conditions", "todo_items.completed is 0"

## to-dos where to-do name contains user provided string

tblscope_createObject "todo_items", "name contains"

put it into theScopeA

tblscope_set theScopeA, "conditions", "todo_items.name contains ':1'"

end _CreateScopes

Defining Scope Objects for a Table Object

A Scope object is attached to a Table object. You create a Scope object by calling tblscope_createObject and passing in the name of the table (1) and a unique name (unique for that particular table) for the scope (2).

tblscope_createObject returns a reference to the Scope object that was created (3). You use this reference when calling tblscope_set.

After creating the Scope object you define the conditions of the scope (4). Four Scopes have been created above and notice that each one has a very specific search condition. By combining various Scope objects the exact search the user has requested can be performed.

Notice how most of the Scope object conditions using binding variables (the :1 in the conditions string) (5). The actual value that will be searched for is usually specified when the Scope is added to the SQL Query object.

Call tableobjects_reload

Since you are modifying the objects in the Database object you need to call tableobjects_reload so that the dbobject.createTables message is called.

Make sure and call dbobject_save and then save the application stack to disk as well to make the changes permanent.

Confirm Your Work

To confirm that you updated the behavior script correctly check the printout of the Database object array. You should see an entry for scopes under the todo_items table object.


Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.