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.

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.

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.

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.

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

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.

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.

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.