Scopes

Scope Objects

Scope objects define search conditions that can be added to a SQL Query object in order to perform complex searches. Think of a UI that allows the user to specify multiple search criteria. The user might be able to choose multiple filters that should be applied to the search. Each filter in the UI can be associated with a single scope object. If the user enters a value for a particular filter then the corresponding scope object is added to the search conditions of the SQL Query object.

Creating Scope Objects

While Scope objects can be created via the tblscope_createObject API, it is simpler to create them in the table objects.yml file. You can see a list of the available scope object properties by referring to the tblscope_set documentation.

Here is some example YAML for creating scope objects:

scopes:
  todo_items:
    - name: of project
      related table joins: LEFT OUTER JOIN people
      conditions: todo_items.project_id is :1
    - name: of person
      related table joins: people
      conditions: people.id is :1
    - name: not completed
      conditions: todo_items.completed is 0
    - name: name contains
      conditions: todo_items.name contains ':1'

Tip: You don't need to define table objects before you define scope objects. When SQL Yoga parses the relationships it will create table objects as needed.

Using Scope objects when generating queries

Once you have defined Scope objects you can leverage them when working with SQL Query objects. The following code snippet creates a SQL Query object and then adds scope objects based on the search filters the user has selected using sqlquery_addScope. After adding the appropriate scope objects sqlquery_retrieveAsRecords is called and records matching the resulting WHERE clause are returned.

Notice how the name of each scope that is added corresponds to the name of a scope that appears in the table objects.yml entry shown above.

## Create Query object
put sqlquery_createObject("todo_items") into tQueryA

## Filter by project or person?
switch the uSelectedType of group "ProjectsPeople"
  case "project"
    sqlquery_addScope tQueryA, "of project", \
         the uSelectedProjectID of group "ProjectsPeople"
    sqlquery_set tQueryA, "order by", "todo_items.sequence"
    break

  case "person"
    sqlquery_addScope tQueryA, "of person", \
         the uSelectedPersonID of group "ProjectsPeople"
    break
end switch

## User supplied search string?
if the text of field "Search" is not empty then
  sqlquery_addScope tQueryA, "name contains", \
       sqlyoga_splitUserSearchString(the text of field "Search")
end if

## Filter out completed?
if the hilite of button "HideCompleted" then
  sqlquery_addScope tQueryA, "not completed"
end if

## Query database
sqlquery_retrieveAsRecords tQueryA, tRecordsA