Completion is the way you can avoid writing too much if the system already has your data. It means you write some text that will be used as filter in a select statements of all possible values. Completion is active
This operation has the following main actors:
- the search field: the field where what you type is searched for. In the image, ‘f’ is the typed text that will be searched for in the field last_name that is the search_field. The letter ‘f’ will be used to filter the output. In this example the first_name (Roberto, Federico, Françoise) is not part of the search, but is used to better represent the possible matches.
- the object that should be returned:
- the string to be used (as in enum mode, or in completion in values used in the same column). This is only available for char/text fields No validation is done by default on this field, the completed text can be further edited.
- the foreign key (frequently an id) along with a representation of the referenced record (e.g.: the fullname of a director). A check is made: there’s no way to input a text that is not matched on the remote field (see foreign key description & search field below).
- a complete record as when we edit a relation and we use a filter on one field but we aim at setting a complete record. E.g.: we select an actor from a list and we may be filtering the name or the nation. See example 40d.
- the representation of the result: when using completion on a ForeignKey or on an m2m relation, the representation of the possible values needs to be taken. In the example of the image ‘Fellini’ the the representation of the referenced record that was referenced by a numeric id.
- the operator used when searching: normally regexp or like. In the image here you see that ‘Truffault’ is returned that does not start with ‘F’ but contains ‘f’.
The match of the completion has these modes:
start: partially written text is used as a filter and completion must match from the start of the field. It uses LIKE operator regexp: the match is done via regular expression if db supports it or via LIKE operator adding % on both sides enum: any value matches. All possible values are shown. This mimics an enum field. When there are only few values this may result more natural than the others
Since completion implies a search that may return many records, it’s only triggered on demand. Normal binding are:
shift Return: triggers ‘start’ mode completion control Return: triggers ‘regexp’ mode completion. This is also triggered pressing the down arrow of a foreign key shift control Return: triggers ‘enum’ mode
Pressing Alt along with the other keys will prevent any Filtering completion
When displaying data (and data shown by a completion are no exception), a foreign key is substituted by a more descriptive text. Let’s see how to customize it.
Make sure you understood the limitation on ForeignKey expressed in Basic assumptions and limitations
You can customize the way a record is represented by:
You would use module sqlkit.db.utils and create an object Table Description:
from sqlkit.db.utils import TableDescr utils.TableDescr('movie', format='%(title)s - %(year)s', metadata=db.metadata)
metadata is necessary so that TableDescr knows where to go and auto-load Table to introspect it if you don’t provide the search_field (see below)
It’s possible to write in the database the format string to be used in the table. A table called _sqlkit_table is searched for (firebird backend doesn’t allow leading ‘_’ in table names so ‘_’ is stripped for it).
|search_field:||The value of this field is used for the search. If no such field was defined, the first char field of the tale is used, if it exists.|
|format:||the value of this field is used to represent the record, e.g.: “%(title)s %(year)s”|
You can easily edit this table using Sqledit - the standalone program to browse and edit data
When SqlWidget creates a class on the fly it looks for the ‘format’ field to add a __str__ method to the class, so that this representation is used whenever suitable (e.g.: when a filter action is performed in a Mask)
You can force completion to start after n chars has been entered setting the completion object autostart value:
t = SqlMask('movie', dbproxy=db) t.completions.director_id.autostart = 2
Completion will be recalculated every time the written string is shorter that the last text that triggered the completion.
Take care not to use a little value for autostart on large tables.
There is another situation in which a completion is started automatically. When editing a foreign key (or an m2m): if a Return is hit, a select is issued to check if it’s a valid value and if not that value is used as base for completion. The difference from triggering a normal completion is that if a valid value is found, no further completion is done.
Since completion uses the already written chars to filter possible solution, if you further delete such chars you are not seeing all the real possible solutions but only the already retrieved ones. You can request a new completion...
There is also en easy possibility to add grouping of completion via a foreign_key attribute. It’s enough to set the group_by attribute of completion_group_by:
t.completions.director_id.group_by = 'nation'
You can programmatically decide to filter what a completion returns in a very easy way using django like syntax (the same used to set constraints):
t = SqlTable(Movie,...) t.completions.title.filter(title__icontains='love')
this line will instruct the completion to only show titles that contain the world “love”.
Filters on a field that is a foreign key will be relative to the related table:
# nation_cod is a field_name of the table t.completions.director_id.filter(nation_cod='ITA')
will build a constraint on the director table filtering only italian directors.
If you set relation on your Director class as in:
class Director(Base): ... nation_cod = Column(ForeignKey(Nation.cod)) nation = relation(Nation)
you can set filter on the completion based on this relation:
## nation__code (note the double underscore!!!) will trigger ## a filter on the cod field_name of the relation nation t.completions.director_id.filter(nation__cod='ITA')
It’s also possible to set a “dynamic filter” i.e. a filter depending on the value already of another field:
In this case the value of $nation will be set using t.get_value('nation') In case you have a related table you can go back to the main table:
Enum mode is the way you can mimic a standard enumeration field: you see all fields independently from what you have in your entry. This is more natural in some circumstances if you only have few values.
You can get this behavior all the times just hitting Control-Shift-Return or in ForeignKey fields double-clicking the down arrow. Since you probably want this depending on the values of the table you can programmatically choose to serve completion only via this way setting force_enum = True on the completion:
t.completions.director_id.force_enum = True
There is another way that mimics enum mode, i.e. setting directly the possible completion values via the method set_values:
t.completions.status.set_values(['open', 'closed', 'waiting for input'])
the signature of this function must be:
|Parameter:||value – the value that may have been written in the entry, used to filter values, as usual|
There are normally 2 possible completion according to the filter level. An example can better clarify: suppose you have an entry where you are supposed to enter a username. You set a filter on active users, now you need to fix an old record that really has a user that is no longer active. You need to loosen you filtering criterion momentarily. You can do that Pressing the Alt key along with normal Ctrl-Enter or Shift-Enter
These two filtering criteria are stored in two session.query objects and are stored in the completion with attributes:
|filtered_query:||the query with filters. You set filters on this query with filter() method. If a filtered_query already_exists, filters are added, otherwise it’s written from query|
|query:||the default query, used when no filter is desired (Alt is pressed). You set filters on this adding argument “main_query = True” to .filter(main_query=True).|
Of course each one can be customized with the normal sqlalchemy syntax also.
Note that if you have both filters (with and without main_query option) order makes difference as filtered_query is built based on query when .filter is called for the first time. If you change query after calling .filter() you end up with unrelated filter condition (that’s allowed as you may really want this).
Remember that Validation is a completely different mechanism than completion even if it’s not possible to add a field that doesn’t come from a completion.
The query attribute of the completion determines which fields will be present in the completions list, the format attribute decides how it will be represented. Default value is determined as described above, but you can customize it as you prefer, as far as you use fields present in the query. As an example:
t.completions.director_id.format = '%(first_name)s %(last_name)s -- %(nation)s'
When the completion is in a SqlTable that represents a not editable m2m relationship (as actors would be for movies), the completion does not simply add the single field but substitutes the whole record.
On such a relationship table’s completion you can set filter that will act on all fields. Rationale: if you have a movie/actor relationship and set a constraint on actors so that only female should be selected, you probably want to retain that filter independently from the fact that you select the nation, the first or the last name.
An object that hold all the information on how to retrieve possible completions in simple cases
|Parameter:||main_query – add the filter to the main query|
Add filters to the completion. Filters must be expressed in django-like syntax Value can be in the form $field_name (see below):
t = SqlMask(...) t.completions.field1.filter(cod='$field2')
- to retrieve the value of field2 via t.get_value(field2)
- to add .filter(field2 = value) to the query that retrieves the possible completions
Filter conditions are relative to the mapper of the completion: for a completion on a ForeignKey it’s the referenced table’s mapper. To state it again and referring to example 40 of the demo: if you edit movie table and complete on diector_id, the following code would select the nation of the director in table director:
In point 1. above t.get_value() is relative to the SqlWidget in which the completion is requested. This is relevant when related tables are present in the mask. Field value of the main mask can be referred to as $main.field_name
each token starting with $ as in $title is stripped from the $ and the remaining part is used as field_name and t.get_value(field_name) is used instead
if the field_name part starts with main. (as in $main.title) t.get_value(field_name) is not issued in the active SqlWidget but in the SqlWidget pointed to by relationship_leader
This in general is the main SqlMask holding possibly different m2m tables. This makes it possible for rows in an m2m table to complete only with values related to the referring header.
Set explicitely the possible completion values.
|Parameter:||values – a list of values or a callable that will return a list of possible values|
Can be a list or a callable that will be called to get the real list of values passing the (possible) text in the entry as parameter
A completion that completes on the whole line: each field can be used to choose the same record. It’s not possible in this mode to compose a new record field by field.
The lookup of foreign key values needs a different table than the one used to complete. This is called table_lookup/column_lookup.
Inherits from FkeyCompletion