Building Queries

The purpose of the Query panel is to provide a quick and easy way of building a query that can be executed.

To add/remove terms to/from the query use the right-click popup menu. Once an element has been added to the query tree its criteria can be specified.

Adding elements

To specify a query term for a particular field select the field from the list presented in the popup menu.

images/download/attachments/46802429/query-add-field.png

Individual query terms are added to a composite element that specified whether the child terms are combined using AND or OR logic. In addition all fields within a single AND or OR element must come from the same vertex of the data tree (e.g. be in the same database table). More complex queries combining fields from different database tables are defined using multiple AND or OR elements. The different elements of the query make up a tree structure.

Once an element has been added to the query tree its values can be specified.

Removing elements

To remove and element from the query tree select it and click on the 'Delete' key on the keyboard or use the right-click popup menu option.

images/download/attachments/46802429/query-delete.png

All child elements of an AND or OR element can be removed using the 'Delete sub-tree' option from the popup menu (note that the top-most AND or OR element cannot be deleted).

images/download/attachments/46802429/query-delete-sub-tree.png

AND and OR elements

AND elements can be changed into OR elements, and vice-versa using the popup menu.

images/download/attachments/46802429/query-and2or.png

Fields from different vertices (database tables) in the data tree must be contained in different AND or OR elements. When a field from a different vertex is added to the query an AND element is automatically added for you and the field is placed within this AND element.

images/download/attachments/46802429/query-add-detail-field.png

OR elements can be added to any AND or OR element. This allows more complex logic to be build up e.g. A AND B AND (C OR D).

Example

The following example illustrates a query that finds all structures containing the indole substructure that have an biological assay result (in the WOMBAT.ACT.LIST Entity) that is of type Ki and has a value of less than 10.

images/download/attachments/46802429/advanced-query.png

Restrictions on building queries

The query builder allows you to group a set of query terms into AND and OR elements. This allows you to build up complex queries. There are a few 'house rules' that apply to building queries. These are necessary to keep the user interface sufficiently simple and to ensure that the queries can actually be executed! The query builder does not let you build and execute every possible type of query, but should cover most needs. We will be increasing the range of queries that can be run in future versions of IJC.

The current 'house rules' are:

  1. All fields within any particular AND or OR element must be from the same Vertex of the data tree (effectively meaning they must be from the same database table).

  2. The root element of the query must be an AND or OR element for the root Entity of the Data Tree. You can add child AND or OR elements from other Entities in the Data Tree.

  3. Going down any branch of the query tree the Entity can only change once. e.g. These are OK:

    A and-> B
    A and-> B or-> B
    A and-> C

    but these are not:

    A and-> B and-> A
    A and-> B and-> C
  4. AND elements are not allowed as child elements of an AND element from the same Entity (they are unnecessary as the individual conditions can be added to the parent). e.g. This is not allowed:

    A and-> A

    but these are OK

    A and-> B
    A or-> A and-> A

Query by example

IJC provides a simple way to specify query terms by selecting appropriate values from a grid view or a form view. When you see a value in one of these views that you want to use as a query term you can select it:

  • In the grid view select the cell or cells in the table.

  • In the form view select the widget or widgets (including cells in a table). Once you have the value(s) selected set them as a query term by using one of the icons in the view toolbar:

    images/plugins/servlet/confluence/placeholder/unknown-attachment0.
    SearchByValue.png

    Specify a new query using this term(s).

    images/plugins/servlet/confluence/placeholder/unknown-attachment0.
    SearchByValueAdd.png

    Add this term(s) to the current query.

This will result in the query panel being updated with the new terms. The default operator will be set. The details of the query can then be further modified (e.g. choose a different operator) and then executed.

Adding terms to the query using this mechanism can result in meaningless queries being generated when multiple terms for the same field are added. Consider the meaning of the query before you execute it.