Token: Subquery

By

OWS now allows the use of embedded sub queries to get details of information that are otherwise unavailable to your primary query.  An example may be if you are using OWS to design an entry form and you need a <select> list.  This is easily achievable with the Sub query action structure. All runtime actions like Sorting, Interactive Lists and Actions utilize the similar formatting structures contained within curly braces {...}. The Sub query syntax has a varying set of parameters, depending on the task that needs to be achieved. The Type of the action - SUBQUERY, followed by Name to name the sub query.  This way, a sub query may be re-referenced by name and the query will not be processed again, saving valuable database access time.  The Query must be defined only if it's the first time this named sub query is encountered.  There are several parameters dictating formatting and pre-selection of values.

Tip: Use the Name parameter to prevent re-querying the database unnecessarily.

Sub query Syntax

A Sub query is used to render the output of a query other than the primary query into the item holders for an HTML element.  Some examples include as options in a select element, line items in an order list, or any other HTML element which repeats.

Sub queries have several parameters, most of which are optional.

Sub query Syntax:
{SUBQUERY,Name,[Query],[Header],[Footer],[NoQueryFormat],[NoResultFormat],[Format],[AlternateFormat],[SelectedFormat],[SelectedField],[SelectedItems],[UseCache],[CustomConnection]}

Example

{SUBQUERY, Name="qryWidgets", Query="SELECT * FROM Widgets", 
Header=”<select name=cboWidgets>”, 
Footer=”</select>”, 
NoResultFormat=”<i>No Widgets can be bound at this time</i>”,
Format="<option value= '[ID]'>[Name]</option>", 
SelectedFormat="<option value='[ID]' SELECTED>[Name]</option>", 
SelectedField="ID",SelectedItems="6", UseCache=False}                 
         

NOTE: If your Sub query contains columns which are you referencing which match names of column in the Rendering query, or names of variables which exist in the System Information (like TabID, ModuleID, FirstName, LastName etc. You will need to escape the column values if you are attempting to display them from the Sub Query. For instance:

<select name="cboTabs">   

       {SUBQUERY, Name="qryTabs", Query="SELECT * FROM Tabs", Format="<option value= '\[TabID\]'>\[TabName\]</option>", SelectedFormat="<option value='\[TabID\]' SELECTED>\[TabName\]</option>", SelectedField="TabID",

SelectedItems="18",UseCache=False}      

</select>

Name
Name the sub queries on a page so that if the query needs to be used in more than one place, it can be referenced without the [Query] parameter and not have to re-query the database.

Query
Optional if this named sub query has been declared earlier in the page.  The query accepts all Query Options and will perform the replacement prior to execution.  If the query is designed to use a value from the main query for the current row (i.e., "SELECT * FROM Tabs WHERE ParentID = [TabID]", where [TabID] comes from the primary query), then the query will execute every time the [TabID] value changes.

Header

Content which should be displayed above the results based on your format, like Table tags and column structures, can be placed within the Header attribute. The Header will only display if results are obtained.


Footer

Content which should be displayed below  the results based on your format, like end Table tags, can be placed within the Footer attribute. The Footer will only display if results are obtained.

NoQueryFormat

When your subquery execution is missing a query, the No Query Format will be displayed in place of the results. This often occurs when your query is based on a variable and is missing at the time of execution.

NoResultFormat

Much like the standard Detail structure of OWS, when No Results are obtained by the query, the No Result Format will be displayed in its place.

Format
This is the HTML that will be repeated for each row, or every first row if [AlternateFormat] is implemented.  This works just like the OWS List Item Detail element.  Column and variable values will be replaced with actual values.

AlternateFormat
This is the HTML that will be repeated for every other row.  This works just like the OWS Alternate Item Detail element.  Column and variable values will be replaced with actual values. 

SelectedFormat
This is the HTML that will be repeated for each row matching the [SelectedField] to [SelectedItems].  This works just like the OWS List Item Detail element, except it is only rendered on rows in the [SelectedItems] list.  Column and variable values will be replaced with actual values.

SelectedField
The field name in the query for which the [SelectedItems] list applies. 

SelectedItems
A comma-delimited list of values for the [SelectedField] column.  Anytime the rendering engine encounters a row where [SelectedField] IN ([SelectedItems]) then the [SelectedFormat] item is what gets rendered.

UseCache
To save time and performance on servers, it is occasionally wise to use the cache for storing sub query information – for instances where the data will not change frequently, ie. A List of Countries. Since OWS is a completely dynamic engine, you maybe filtering your Sub Queries via means located within the site, and will, therefore, not be able to use the cache. To keep the sub query data current – set UseCache=False. The default is always True, so, if you are missing this tag and discover that your query results are not changing – you need to add this option. 

Connection
If your subquery needs to be pulled from a database connection other than the standard Dotnetnuke connection provided for your instance, you can specify the ODBC connection string to use for the connection by adding a connection string. For example:

Connection=” Provider=sqloledb;Data Source=MyOtherDatabase;Initial Catalog=pubs;Integrated Security=SSPI;” A complete breakdown of connection string is provided in the appendix titled: Connection Strings.

Average (1 Ratings):
 
Want to help out?
 
 

New York, NY • Baltimore, MD • Vienna, VA • St. Louis, MO • Seattle, WA • info@openwebstudio.com

Bookmark & Share Bookmark and Share