3.2.4. sql attributes¶
3.2.4.1. columns¶
The
columns
attribute represents the columns to be returned by the “SELECT” clause in the traditional sql query.It is a string of column names and relations separated by comma (you can use a list or a tuple, too).
It is a standard sql column clause and may contain sql functions and the “AS” operator. In addition to sql expressions, a column can be called through the following syntaxes:
$colname
: a column of the main table or a key of the relationDict@relname.colname
: a related columnsqlfunction($colname, @relname.colname)
:$
and@
syntax can be used inside sql functions too*
: all the columns of the main table (with or without the bagFields)*filter
: all columns of the main table filtered (check theexpandMultipleColumns()
method)*@relname.filter
: all columns of a related table filtered (check theexpandMultipleColumns()
method)To select all the columns use the char
*
The
columns
parameter also accepts special statements such as “COUNT”, “DISTINCT” and “SUM”
3.2.4.2. condition¶
The condition attribute gathers additional queries respect to the where in your field/component.
In the dbSelect the where attribute must not to be specified because it coincides with the user typing. In those case, you can use the condition attribute if you need to specify more conditions in the query
Syntax:
TODO condition ... Every kwargs params that starts withcondition_
are the variables involved in the ‘where’ clauseExample:
If you have the following dbSelect:
# ... other line codes pane = root.contentPane() fb = pane.formbuilder(cols=2, border_spacing='6px') fb.dbSelect()The query here is specified by what user digits; if he types “john”, then the dbSelect will show all the records that contains those letters.
If we need more conditions on the query, we can use the condition attribute; so, the dbSelect will be replaced with:
fb.dbSelect()For more information, check the dbSelect page
3.2.4.3. deferred¶
The sql “DEFERRED” clause.
Boolean,
True
to get... TODOExample:
TODO
3.2.4.4. distinct¶
The sql “DISTINCT” clause.
Boolean,
True
for getting a “SELECT DISTINCT”.Example:
TODO
3.2.4.5. group_by¶
The sql “GROUP BY” clause. Database columns can use one of the following syntaxes:
$colname
where
colname
is the name a table column
@relname.colname
where
relname
is a relation,colname
is the name of the column.Use
group_by='*'
when all columns are aggregate (TODO) functions in order to avoid the automatic insertion of the pkey field in the columns.Example:
TODO
3.2.4.6. having¶
The sql “HAVING” clause. Database columns can use one of the following syntaxes:
$colname
where
colname
is the name a table column
@relname.colname
where
relname
is a relation,colname
is the name of the column.
3.2.4.7. limit¶
TODO
3.2.4.8. order_by¶
The sql “ORDER BY” clause. A clause that returns the result set in a sorted order based on specified columns.
Database columns can use one of the following syntaxes:
$colname
where
colname
is the name a table column
@relname.colname
where
relname
is a relation,colname
is the name of the column.Example:
TODO
3.2.4.9. where¶
The
where
attribute represents the table columns to be returned by the “SELECT” clause in the traditional sql query.Database columns can use one of the following syntaxes:
$colname
where
colname
is the name a table column
@relname.colname
where
relname
is a relation,colname
is the name of the column.Query parameters have to start with colon (
:
), like:``@relname.colname=:param1``.where
param1
is the query parameter.Note
we suggest not to use hardcoded values into the where clause, but refer to variables passed to the selection method as kwargs.
Examples:
where="$date BETWEEN :mybirthday AND :christmas", mybirthday=mbd, christmas=xmas