The Standard Query Builder

The standard query builder is used for creating queries for all data tables in Leapfrog Geo except for imported points data tables. For information on building queries for points data tables, see The Points Query Builder.

To create a query filter for a data table, right-click on the table in the project tree and select New Query Filter. The New Query Filter window will appear and you can choose which way you prefer to build your query. Assign the filter a name and click OK to save it. It will appear in the project tree under the table to which it applies:

There are three windows that make up the standard query builder:

In the first window, the New Query Filter window, you can type in a query using the query syntax described in Leapfrog Geo Query Syntax. Press Ctrl-Enter for a new line in longer queries.

The second window, the Build Query window helps you build some common queries by selecting from the data columns in the table and defining different tests. Open this window by clicking the Build Query button in the New Query Filter window. See The Build Query Window for more information on building queries in this way.

The third window, the Advanced Query window, shows all the data columns and possible operations. See The Advanced Query Builder for more information.

Leapfrog Geo Query Syntax

The Leapfrog Geo query syntax is based on the WHERE clause of the Structured Query Language (SQL) with some restrictions:

• Unary operators are not allowed
• SQL functions cannot be used
• The SELECT statement should not be used (as in 'holeid in (SELECT holeid FROM ...)')
• Following statements are also prohibited: CASE, WHEN or MATCH

There is also one main SQL extension:

• IN and NOT IN will accept a partition group for the value list. E.g. 'zone IN layers.weathered' where "layers" is a partition of the "zone" column that has a group called 'weathered'.

These are some examples of valid Leapfrog Geo query statements:

• holeid in ('m-001', 'm-002')
• holeid not in ('m-001', 'm-002')
• holeid not like 'MAR%'

To enter a query that contains an apostrophe, enter two apostrophes. Adding the second apostrophe lets the query builder treat this as a mid-phrase apostrophe instead of an end-of-phrase marker, and results in a valid query.

The Query box allows the following shortcut queries:

• Typing a single word will match all hole-ids starting with that string. For example, typing "MAR" is a shortcut for "holeid like 'MAR%'"
• Typing a comma-separated list of words will match all hole-ids that exactly match the given words. For example typing "M001, M002" is a shortcut for "holeid in ('M001', 'M002')".

The Build Query Window

Many common queries can be built using the Build Query window:

This window aims to be easy to use rather than comprehensive in its support for the full Leapfrog Geo query syntax.

To add a criteria, click Column to view a list of columns available in the table. Select a Test and enter a Value. What can be entered for the Value depends on the type of column and test selected.

Quotes are not required around text values entered in the value column as they are in SQL. Leapfrog Geo will add quotes and brackets to the value "MX, PM" to make a valid SQL list of strings "('MX', 'PM')" when the query is saved.

Once Column and Test have been entered, if the Column is an enumerated list of values, a button is enabled in the Build column to assist in selecting the Value. When clicked, the Select column values window opens, allowing the desired values to be selected from the list of available options.

Click on the Apply button to apply the query to the context in which the dialog was opened.

Use the Delete button to delete the criteria in the selected row and the Add button to add a blank row.

The Advanced Query Builder

The Advanced Query window supports flexible query building using the Leapfrog Geo query language.

The Criteria to Match box on the left is where the query will be constructed. The query is displayed as a tree structure with AND and OR operators as the branch nodes and conditions as the leaves. Note that when you have only a single criterion to match, there will still be an AND or OR operator as a top level branch node, even if there is only a single leaf in the tree and there is nothing to AND or OR with, as in the example illustrated above.

Use the Add button to add a blank row and the Delete button to delete a row from the query.

The Data tree on the right contains all the columns that can be used in the query. Double-click on a column name to insert it into the query.

The collection of Symbols buttons contains the logical operators that can be inserted into the query.

Numeric values you enter into the query do not need to be enclosed in quotes, but text being used as a query token needs to be wrapped in single quotes. In this example, a test that the lith code column matches BX has been included, so ‘BX’ is used in the query. Because the top level branch node is an AND, the whole query is checking for a from depth exceeding 30.1 AND a lith code of BX:

If you want to add another logical layer to the tree, say for instance you want to check for both BX and BIF lith entries in the table, you can select a row and push it down a level, then click the AND or OR operator button. In the illustrated example, the Proj_Lith_Code = ‘BX’ line was selected, then OR clicked. A newly created leaf is selected in the criteria tree, ready for construction of the next test:

Here the test for the BIF code has been added:

The REGEXP button allows regular expression pattern-matching. To use regular expressions:

1. In an empty row, click a column name in the Data tree.
2. Click the REGEXP button.
3. Click at the end of the criterion row being edited to de-select the current content and to position the cursor at the end of the entry.
4. Enter a regular expression wrapped in single quotes.

Here the regular expression ^.+_00[0-9] has been used to look for any holeid that includes _00 within the string and ends in another number:

Standard regex syntax is used. You may find regex construction tools such as regex101.com of assistance in creating suitable regular expressions for your queries.

The Query box shows the raw form of the query assembled using the query builder.

The Check button can be used at any time to check if the current query statement is valid.

The arrow buttons are used for moving the currently selected criteria to a different position in the query.

The Date, List and Value buttons will open a builder dialog for the column selected in the current row. If there is no column found or the column is of the wrong type, an error message is displayed.