Data Editor - Filtering

Data Editor has a powerful feature - FILTERS, which allows you to select a subset of records of a table specifying simple search conditions.

To manage filter(s) you can use menu Record → Filter in the main menu or the button in the top left corner of the table grid – .

This menu contains commands to add a filter and remove it, to store and manage favorite filters and others. Let's consider in detail each command.

This command adds the first row of filter or one more OR-row to existed filter. You can see filter row(s) of the green color on the top of the table grid.

In filter row(s), you can define a search condition for each field. The rules of search condition are 100% the same as in the SQL query. Valentina Studio combines all these search conditions to produce a SQL query and execute it.

For an exact search on a numeric field, you can just enter some value into the cell:

5

Or you can specify explicit operator '=' at start:

=5
= 5         // space does not matter.

You can do a range search on a numeric field, starting with range operator > >= < ⇐

> 10

You can do even more complex range search using boolean conditions:

>= 10 and film_id <= 20

Notice that for the second expression you must specify the field name.

You can use $ instead of the field name:

>= 10 and $ <= 20
$ >= 10 and $ <= 20

Also yo can use BETWEEN of SQL:

BETWEEN 10 AND 20

You can type a few letters to get CONTAINS search, using WHERE fld LIKE '%abc%'

abc
ab'c     // it will be escaped by vstudio.

You can start with LIKE, ILIKE, RLIKE, REGEX, REGEXP, LIKE_REGEX (depending on your DB) to provide other forms of text search

LIKE '%abc'
LIKE 'abc%'
LIKE 'abc%d'
etc

For the search of strings with a single quote, you need to escape it, when use own LIKE:

LIKE '%ab''c'
LIKE '%ab\'c'

You can do also range search for strings:

$ > 'a' and $ < 'c' 

For a boolean field, you can use or 0/1 search or use TRUE / FALSE

TRUE
FALSE
1 
0 
= 1 
= 0

To search records with NULL or NOT NUL use syntax

IS NULL
IS NOT NULL

You can also use such kind of expressions in a filter cell:

= other_field_of_this_table
> other_field_of_this_table
$ <= other_field_of_this_table

It is easy to see that search conditions of the same green filter-row are combined by AND in the result SQL query:

SELECT * 
FROM T
WHERE ((filter_1_expr1) AND (filter_1_expr2) AND ...)

You can see the WHERE part of the result SQL query in the tooltip if you move the mouse cursor over the icon of the filter row.

This command is a kind of optimization. When you see in a cell some value, which you want to use for FILTER of records, you can just right-click that cell and choose this command. You will see a new filter row with this value assigned to that field.

Note that the filter is not yet applied. You will need to “Execute Filter” command yet.

This command is even bigger optimization than “Add Filter Row with Cell Value” because it does the same – adds a new filter row with the value of a cell, but also executes filter immediately.

Having the first filter-row, you can add more filter-row(s) pressing the same CMD+F keyboard shortcut. So you will see 2 or more green filter-rows.

Each next filter-row is added to the result WHERE as OR expression.

SELECT * 
FROM T
WHERE    ((filter_1_expr1) AND (filter_1_expr2) AND ...)
     OR  ((filter_2_expr1) AND (filter_2_expr2) AND ...)
     OR ...

This command adds a special filter row in which you can define by yourself the WHERE clause. This WHERE-filter can be used for more complex expressions, which cannot be done in another way.

Note, that you can use only one of the filters at the same time: a normal filter or a WHERE-filter.

This command removes the filter:

• green rows are removed from the table grid;
• all records of the table are displayed again.

If you have added too many rows to a filter, you can use this command to remove the last row(s).

This command executes SQL query, produced in the background using the filter conditions, and displays only records that satisfy them.

NOTE: you can execute filter by a mouse click on the filter icon () on the very left of a green row.

You can use the keyboard shortcut “CMD + E” on macOS or “CTRL + E” on Windows/Linux to execute the query.

You can use Record → Show All Records (CMD/CTRL + G) menu command, to show all records in the table exactly as before filter was applied. So you can maybe edit your filter future adding more conditions.

Note, that this command does not trash filter itself. You still can see green lines and edit them.

Also, you can discard filtering by click on the filter icon () on the very left of the green row.

This command allows you to get an SQL query, which is used by the current filter. You will be thrown into SQL Editor, which contains a corresponded query.

This feature allows gives you one more way to construct SQL query step by step for future use in your application code. Other ways are to use

• Query Editor;
• Generate SQL in Schema Editor;

Shows the list of recent filters for the current table. You can reuse them without typing the same filter condition.

Favorites allow you to save a filter under some name and reuse it in the future.

To add a new favorite filter you should, first of all, create it and set up the filter clauses.

Then you should select the 'Favorites' menu item and choose Append… item in the submenu. Then specify the name of the filter in the modal dialog.

In the future, you can use this filter via the same submenu.