Understanding Queries

All data displayed in the Flash movie (swf) using the fCMSPro components is stored in the database running on the web server. In order to retrieve specific data, based on a user's input, the Query object is used. You can access the Query class through ActionScript in order to create your own queries, in a similar way to the way you would execute an SQL query. When the data is retrieved, the fCMSPro will parse it transparently and trigger the appropriate events.

The fCMSPro Query object is used for building up the query that will be executed on the backend. It can be instantiated by using the getQuery function of the fMaster, fTemplate or fIndex components.

The Query class has a toString method that will output an easy to understand description of the query. By using trace(myfTemplate.query); we can display this in the output window. It should always be clear what queries the fIndex and fTemplate are sending to the backend. Here are some examples:

Tracing the default query of fIndex component( trace( myfIndex.query ); ) outputs:

Code:

/------------------------------------
| select title
| from 0 to 5
| sort on $CREATIONTIME(date_time) DESC
| where $LANG = deflang
| AND $DOCTYPE = news

The query above will return titles of the first 5 documents sorted by time of creation, written in the default language and of the type 'news'.

The name of the fields that will be returned can be changed via actionscript or by changing the 'fields' parameter in the component's panel. If we set it to the value '*' all fields of the record will be returned and the output will be:

Code:

/------------------------------------
| select *
| from 0 to 5
| sort on $CREATIONTIME(date_time) DESC
| where $LANG = deflang
| AND $DOCTYPE = news

Doing the same with actionscript:

Code:

myfIndex.query.fields = [ "*" ];

'from 0 to 5' is the limit or number of records that will be returned. In our example (default behavior) the first 5 records will be returned in the first request. Change that through actionscript (Query.limit and Query.page properties)
'sort on $CREATIONTIME(date_time) DESC' requests records to be sorted according to the tiime of creation in descending order. Let's change the sorting to be according to the database ID in ascending order. The actionscript needed is:

Code:

myfIndex.query.setSort( "$ID", "number", "ASC" );

and the query description:

Code:

/------------------------------------
| select *
| from 0 to 5
| sort on $ID(number) ASC
| where $LANG = deflang
| AND $DOCTYPE = news

At the end of the query description we see query filters (everything after the word 'where' in the description is about filters). First there is a language filter. By default there are no defined languages and the query will return all records where the language is not defined. If a multilingual site is required we can change the language by changing the fMaster.language property.

For example, we can have 2 buttons at the first screen of the website for language selection. Clicking on the 'english' button will change the fMaster.language property before the query is executed like this:

Code:

myfMaster.language = "english";

The default queries of fIndex and fTemplate components will now change and the query from our previous example will be:

Code:

/------------------------------------
| select *
| from 0 to 5
| sort on $ID(number) ASC
| where $LANG = english
| AND $DOCTYPE = news

Changing the language property of fMaster will change all default queries with the following actionscript:

Code:

myfMaster.language = "english";
trace( myfMaster.getQuery() );

will output:

Code:

/------------------------------------
| select *
| from 0 to 5
| sort on $CREATIONTIME(date_time) DESC
| where $LANG = english

Now let's say that we don't want to change the language for all queries but only for the myfIndex mentioned above. Here is the actionscript that will change the language for ONLY that query:

Code:

myfIndex.query.removeAllFilters();
myfIndex.query.addFilter( "$LANG", "=", "english");
myfIndex.query.addFilter( "$DOCTYPE", "=", "news");

and the output will be:

Code:

/------------------------------------
| select *
| from 0 to 5
| sort on $ID(number) ASC
| where $LANG = english
| AND $DOCTYPE = news

Other filters can be changed or added using the Query.addFilter method.