Dynamic Query is a query that contains in the where clause one or more values of certain issue fieldsfields in the WHERE clause.
...
Building a query using custom fields
...
...
...
- Jira and remember their
...
- IDs. They should
...
- have a customfield_yyyyy format, where yyyyy is a number.
To find out a custom field
...
- ID:
- Go to the Custom Fields section in
...
- Jira administration.
- Locate your custom field and click
...
- Configure
...
- from the actions menu
...
- on the right.
You can
- on the right.
...
- see your custom field
...
- ID as a parameter in the url:
/secure/admin/ConfigureCustomField!default.jspa?customFieldId=yyyyy.
- ID as a parameter in the url:
- After you go through the steps from
...
- the Configuration page and the data source configuration page, go to Administration
...
- > CPRIME PLUGINS CONFIGURATION > Database Custom Field
...
- .
- Fill in the
...
- Sql Query
...
- field for the main database custom field
...
- (customfield_xxxxx) with
...
- a dynamic clause of the
...
- following format:
"WHERE col1={customfield_yyyyy} AND col2={customfield_zzzzz} OR col2={customfield_ttttt}"). - Save the configuration.
...
Example
| Code Block |
|---|
SELECT col1, col2, col3, col4 FROM testTable WHERE col1 = {customfield_10300} |
...
| Note |
|---|
The dynamic query works on an issue when creating, editing the issue or for transition screens. If for the customfield_xxxxx database custom field customfield_xxxxx the dynamic clause is of has the form "WHERE col1={customfield_yyyyy} AND col2={customfield_zzzzz}" format for instance, then when changing you change the value for customfield_yyyyy or customfield_zzzzz, the value for customField_xxxxx is cleared, and after that you can select a value can be selected from the a new list of values determined by re-executing the query (and taking into account the new values for customfield_yyyyy and customfield_zzzzz). |
...
Building a query using system fields
...
...
Project field works on any
...
Jira screen, but note that
...
Jira creates the key in uppercase.
System fields can also be used in the dynamic query by using in the where clause one of the following parameters in the WHERE clause:
- project – key of the project
- assigneekey
- key – key of the current issue
- reporter
- priority
- versions
- fixVersions
- components
- labels
- created
- updated
- resolved
- duedate
- issuetype (the name of the issue type)
- status
- resolution
- currentuser
- currentuserkey
issueid
Note issueid – available starting with versions 3.0.8/
DBCF33.1.1
. The cast to numeric type is not ensured, so
the user hasyou need to cast it
by itself.
...
.
| Note |
|---|
For the fields: versions, fixVersions, and components fields, if there are more than one value, the string in the database should be like the one saved on the issue on JIRA. (in Jira. |
. |
Example
| Code Block |
|---|
SELECT col1, col2, col3, col4 FROM testTable WHERE col1 = {assignee} AND col2 = {reporter} |
Dynamic query using dates values
Normally
...
we can only have
...
columns of text type in combination with dynamic custom fields
...
in the WHERE clause. If a date column
...
or some other non text type
...
is used in the WHERE clause, the query does not work.
For instance if the
...
testdate column is numeric in the testTable table
...
, the following
...
query does not work, because the value of the {created} is a string.
| Code Block |
|---|
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
...
of the following
...
format:
...
2012-03-12 17:40:34.973
...
Here it is an example using system fields: .
| Code Block |
|---|
SELECT col1, col2, col3, col4 FROM testTable WHERE col1 = {assignee} AND col2 = {reporter} |
What types of custom field are supported?
JIRA .
Types of supported custom fields
Jira custom fields
- Text Field (< 255 characters)
- User Picker
- URL Field
- Free Text Field (unlimited text)
- Number
- Select List
- Radio Buttons (since v. 2.1.13)
Additional custom fieldsfield
- 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 .
...
Dynamic query using numeric values for dependents
Normally
...
we can only have
...
columns of text type in combination with dynamic custom fields
...
in the WHERE clause. If a numeric column
...
or some other
...
non text
...
is used in the WHERE clause, the query does not work.
For instance if the column id is numeric in the testTable table
...
, the following query
...
does not work, because the value of the customfield_10300 is a string.
| Code Block |
|---|
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)
...
.
| Warning | |||
|---|---|---|---|
Dynamic fields can not cannot be used between single or double quotes. For instance, a query with a dynamic field in the LIKE clause : does not work.
The solution is to use string concatenation provided by the database, so that {key} to be is outside of ' or ". For instance, for PostgreSQL, Oracle, HSQLDB: SELECT col1 FROM table WHERE col1 LIKE '%' || {key} || '%' For MySQL: SELECT col1 FROM table WHERE col1 LIKE concat('%', {key}, '%') For MS Sql Server: SELECT col1 FROM table WHERE col1 LIKE '%' + {key} + '%' For other databases, please see what is the operator should be used for string concatenation. |
...