Table of Contents
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.
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.
Add Filter Row
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.
Numeric - Exact Search
For an exact search on a numeric field, you can just enter some value into the cell:
Or you can specify explicit operator '=' at start:
=5 = 5 // space does not matter.
Numeric - Range Search
You can do a range search on a numeric field, starting with range operator > >= < ⇐
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
Compare to Another Field
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
Multi-Field AND Search
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 ...)
Tooltip with SQL Query
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.
Add Filter Row with Cell Value
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.
Filter by Cell Value
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.
Add more OR Filter Rows
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 ...
Add 'Where' Row
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.
Remove Last Filter Row
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.
You can use the keyboard shortcut “CMD + E” on macOS or “CTRL + E” on Windows/Linux to execute the query.
Show All Records
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.
Filter to SQL
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.