Dynamic Query is a query that contains in the where clause one or more values of certain issue fields.
How to build a query using custom fields?
- Create some custom fields in JIRA and remember their ids. They should be of the form customfield_yyyyy, where yyyyy is a number.
- After you go through the steps from the configuration page and the data source configuration page, go to Administration -> Kepler General Parameters -> Database Custom Field and fill in the "Sql Query" field for the main database custom field: customfield_xxxxx with the dynamic clause of the form, for example, "WHERE col1={customfield_yyyyy} AND col2={customfield_zzzzz} OR col2={customfield_ttttt}").
- Save the configuration.
Here it is an example query:
SELECT col1, col2, col3, col4 FROM testTable WHERE col1 = {customfield_10300}
The dynamic query works on an issue when creating, editing the issue or for transition screens. If for the database custom field customfield_xxxxx the dynamic clause is of the form "WHERE col1={customfield_yyyyy} AND col2={customfield_zzzzz} ", when changing the value for customfield_yyyyy or customfield_zzzzz the value for customField_xxxxx is cleared and after that a value can be selected from the new list of values determined by re-executing the query (taking into account the new values for customfield_yyyyy and customfield_zzzzz).
How to build a query using system fields?
Project field works on any JIRA screen, but note that JIRA creates the key in uppercase.
Assignee, key, reporter are the system fields available in the previous versions(< 1.0.4) of the plugin.
System fields can also be used in the dynamic query by using in the where clause one of the following words:
- project,
- assignee,
- key,
- reporter,
- priority,
- versions,
- fixversions,
- components,
- created,
- updated,
- resolved,
- duedate,
- issuetype
- status
- resolution
- currentuser
For now, these are the only supported system fields, where "key" refers to the key of the current issue and "project" refers to the key of the project.
For the fields: versions, fixversions, components if there are more than one value, the string in the database should be like the one saved on the issue on JIRA. (example: "v1, v2" with comma and a space between the versions and so on ...).
Dynamic query using dates values
Normally in the WHERE clause we can have only columns of text type in combination with dynamic custom fields. If in the WHERE clause a date column (or some other type different from text) is used, the query does not work.
For instance if the column testdate is numeric in the table testTable, the following query:
select testdate, col1, col2 from worklog where testdate = {created};
does not work, because the value of the {created} is a string.
The solution is to use functions available for the database chosen, to transform from string to the type of the column in the SQL query.
For instance, for PostgreSQL: SELECT testdate, col1, col2 from worklog WHERE testdate = cast({created} as timestamp);
For the dates(created, updated) the date from the database should be like the following example:"2012-03-12 17:40:34.973".
Here it is an example using system fields: .
SELECT col1, col2, col3, col4 FROM testTable WHERE col1 = {assignee} AND col2 = {reporter}
What types of custom field are supported?
JIRA custom fields
- Text Field (< 255 characters)
- User Picker
- URL Field
- Free Text Field (unlimited text)
- Number
Additional custom fields
- Database Information and Database Child Information
- User Group Picker - this custom field is developed for User Group Picker plugin.
Risk - this is a custom field developed for Kontinuum plugin. For the example in the image, the value from the database must be of the form: HIGH|MODERATE|description|60|1|ACCEPTED|solution .
Note that the value of the custom field must first be saved on the issue if you use the last 5 types of fields.
Dynamic query using numeric values for dependents
Normally in the WHERE clause we can have only columns of text type in combination with dynamic custom fields. If in the WHERE clause a numeric column (or some other type different from text) is used, the query does not work.
For instance if the column id is numeric in the table testTable, the following query:
SELECT id, col2, col3, col4 FROM testTable WHERE id = {customfield_10300}
does not work, because the value of the customfield_10300 is a string.
The solution is to use functions available for the database chosen, to transform from string to the type of the column in the SQL query.
For instance, for PostgreSQL: SELECT id, col2, col3, col4 FROM testTable WHERE id = cast({customfield_10300} as numeric);
Dynamic fields can not be used between single or double quotes. For instance, a query with a dynamic field in the LIKE clause:
SELECT col1, col2, col3 FROM table WHERE col1 LIKE '%{key}%'
does not work.
The solution is to use string concatenation provided by the database, so that {key} to be outside of ' or ".
For instance for PostgreSQL, Oracle, HSQLDB: SELECT col1, col2, col3 FROM table WHERE col1 LIKE '%' || {key} || '%'
For MySQL: SELECT col1, col2, col3 FROM table WHERE col1 LIKE concat('%', {key}, '%')
For other databases, please see what is the operator for string concatenation.