Working with Select

Prerequisite

We highly recommend using autocomplete for big lists. It will increase the data retrieval performance

To work with select you need first to configure a datasource. If you did not, please refer to the How to configure datasource for SQL Custom Field

Configuring select Custom Field

Step 1 : Choose the datasource

To retrieve data, you will need first to connect to the database that stores your data. Choosing the right datasource will make the connection between the custom field and the database.

Step 2 : Construct the query

After choosing the datasource and the custom field type (we assume that you choose select as the custom field type). You will need now to fulfill the configuration with a working query. The query must first work and retrieve data on your DBMS.

To illustrate properly how it works let us implement a real use case :

Suppose that we have the table City in our database and, here is its structure :

City
City_id
City

A simple select query will retrieve cities from the table city :

Column Alias: When using an alias for the main column. If no alias is used then the field must be the same as the column name.

Column name: The name of the main column

Here is the result :

Step 3: Retrieve more data

Children fields will help you to retrieve more data on issues. To work with Children fields, no special custom field is required, you can use JIRA custom field as Text, Date, number, etc.

In the example below, the query has been modified to select an id and, depending on this id the plugin will retrieve the name of the city :

Query: The query must contain all the columns that you want to retrieve.

Column Alias: Explained by the use of an alias for the main column: city_id as 'id'

And here is the result :

Complex configuration

Now that the usage of the select type is more familiar to you, we can go one step further with the use of a more complex configuration.

Suppose now that our database contains two tables as below:

Country
Country_id
Country

City
City_id
City
Country_id

At this point, the need is to retrieve the country for each selected city. Here's the new configuration :

It's possible to join two or more tables. It's important to know that if the query works on your DBMS it will also work on the custom field configuration except when :

Using ORDER BY you must add the where clause: column name like '%' in our example the query becomes:

select city,country from city c inner join country co on c.country_id = co.country_id where city like '%' order by c.city_id

Here is the result of this configuration :