Form based query

Introduction

Form based query provides an alternative way to define and execute queries. Compared to the query builder is more limited in the types of queries that can be defined, but might be easier to use for simple queries.

Switching to query mode

The IJC form view and grid view both have a query mode. You enter this mode by clicking on the Query toggle button in the toolbar. The view will switch to query mode where you can specify the query criteria.

images/download/attachments/42173360/query-mode-toolbar.png

Defining the query criteria

In query mode you can enter query criteria directly into the form or table widgets. The standard query operators are available and these depend of the data type of the field (see the query panel for full range).

In most cases you define the query by typing the operator and value into the text box. Examples would include:

< 400
>= 0
= 5
between 100 and 200
contains asthma
like asth% is null

The database wildcard character is usually % for zero or more characters and _ for a single character

A fuller description of the different search operators is present in the query builder documentation .

These criteria are converted into simple 'field operator value' type queries for the corresponding field e.g. MolWeight < 400. Where criteria are specified for multiple fields they are combined with the AND operator e.g. MolWeight < 400 AND DonorCount = 5. Unlike the query panel you do need to know what operators are valid for your particular field, but these are usually obvious to learn.

The operator can also be set or changed using the right-click menu for the query widget. This allows you to choose from the list of valid operators, and making a selection switches the expression to that operator. This can be simpler for some of the less common operators such as 'Begins with' or 'Not in list'.

Since version 5.12, the query operator need not be specified if it is testing for equality. You can simply enter a number or text and run the query, and the equals operator will be filled automatically. This works for Text and Integer values. It is not possible for Decimal number fields because for technical reasons there is no equal operator for it. It will likely be implemented in a future version. So far we recommend using the between operator on Decimal number fields instead.

images/download/attachments/42173360/query-form-operators.png

IJC validates the query as you type. An invalid query is displayed in red and an error message is displayed in the Query as Text window and as a tooltip for the widget.

images/download/attachments/42173360/query-widget-invalid.png

Once the query is valid the text turns black and the query can be executed. Common causes of a query term being invalid are:

  • No operator was specified. Try using =, , etc.

  • Text was specified for a numeric field.

  • No term is present.

Some complex field types do not easily allow a query to be defined just by typing in a criteria. The only current example is structure queries. For these types of fields you double click on the widget to open an editor for the query. In the case of structures Marvin Sketch will open and let you draw the query structure. Closing the editor will set the query. The search type (Substructure etc. can be specified using the right click menu as described above. For more details on structure search operators and options see the Query Builder documentation.

Executing the query

Once you have defined your query criteria you can execute it using the 'Run query' button in the toolbar (or hitting the Return key). This executes the query in exactly the way that it would be executed though the query panel and takes you back to browse mode. You can also clear the query or show all records using the appropriate buttons in the toolbar. When you return back to query mode your last query should be displayed.

Query by example

Query by example can also be used in form based query. When in browse mode select the value you want to use as a query criterion for that particular field and click on one of the two query by example buttons in the toolbar.

images/download/attachments/42173360/SearchByValue.png

Specify a new query using this term(s).

images/download/attachments/42173360/SearchByValueAdd.png

Add this term(s) to the current query.

You will be taken to query mode and that criterion will be added or set as the query. A typical example would be to search for a structure that you are currently viewing.