Use the Query module to create customized database queries on your Chameleon data types.
You can create and manage your Queries in Flow. From the Home page, select Content Control > Query.
You can add new Queries to the All Queries list or to a Query Playlist. The All Queries list serves as the master list of queries whereas a Query Playlist simply holds a reference to the Query that exists in the master query list. If adding a new query to a Query Playlist, the new query is first added to the All Queries master list, and then a reference item to the query is added to the playlist.
Click on the Add New button.
With manual edit you enter the desired SQL statement directly in the edit window.
The wizard allows you to select tables and columns of data that you want.
Query Option | Details |
---|---|
Name | The name of the query item. This is a required field. The value of the name must also be unique. |
Description | A description of what the query does. |
Manual Entry / Query Wizard | Use Manual Entry to manually create or modify your query. Use the Query Wizard to help with building your query by choosing the tables and columns to use. |
In Flow version 15.7.3.4 and up, there is now support for parameters inside the queries.
If you include the @ symbol in your query, it'll be used to add a parameter to the query, allowing you to use a single query, in a variety of ways.
Example:
SELECT * FROM `league` l WHERE l.`code` = @LEAGUE |
When you try out the MySQL statement by pressing the 'Execute Query' button, the Query Parameters dialog will pop up to provide you with a means to enter a value (and the value type) for the @LEAGUE parameter.
There are 4 value type settings that can be chosen:
Note: In the Query Parameter dialog, you can choose what type the value of the @ parameter should be so that it matches the data in the database.
Clicking on the item's 'Edit Parameters' button will cause the 'Query Parameters' dialog to appear with the values currently assigned to the item. This button and the label that indicates how many parameters there are will only appear if there are @ parameters in the query item's MySQL statement.
Clicking on the 'Edit Parameters' button will bring up the 'Query Parameters' dialog.
When you want to have the query output links to an items media like the standard BLADE formatted data does there is an option to enable this as of version 16.2.2.2
You use the text LocalMediaUrl(mediaIdField)
where the mediaIdField is the table and column name of the field with the media ID value.
SELECT LocalMediaUrl(t.logoID) FROM `team` t where t.`code` = @teamCode; |
This will be replaced before it is run with text similar to:
SELECT CONCAT('https://tickit.company.com/tickit', '/blade/media/', IFNULL(t.logoID, 0)) FROM `team` t where t.`code` = @teamCode; |
Similarly the text 'LocalBaseUrl'
will be automatically be replaced by the URL of the Flow instance. (Example: https://tickit.company.com/tickit)
You can manage existing Queries as follows:
Action | Details |
---|---|
Remove a Query | Click on the remove icon to remove a query. |
Remove Selected Queries | Select the Queries you want to remove. You can do this by:
Then click on the Remove Selected button to remove the selected queries. |
Remove all Queries from a Playlist | This option is only available when working on a Query Playlist. Click on the Remove All button to clear the all the items from a playlist. |
Note: Removing a query in a playlist only removes the item from the playlist but does not remove the original source query (which exists in the All Queries master list). To delete the actual query, choose the All Queries list, select the query and remove it by clicking on it's remove icon |
Queries can be accessed through BLADE. See the topic BLADE - Query for details.